Problem in Java executing a mysql statement

BrokenArrows

I am trying to issue the following command

stmt.execute("INSERT INTO current_guests VALUES (room_num, name, age, address)";);

The problem is that it is not recognising room_num, name, age, address as variables.

How can i get java to execute this command and properly insert the values that the variables represent.

lukin

Could be any one of a number of things.
What is the exact SQL error message you are getting back? You say that the values you want to insert into the table are not being recognised as variables.
I would need to see your code to get a clearer picture.
Maybe the variable type you are trying to insert into the table is not the same as the type of the column it is being inserted into?
(ie trying to put a String into a number field)
Just a suggestion.

cgarvey

Are you using prepared statements or anything fancy? If not should your execute string not be something like ...

stmt.execute("INSERT INTO current_guests VALUES (" + room_num" + ", " + name + ", " + age + ", " + address" + ')"

... having done the various checkson the room_num, etc. variables (especially important when Badass O'Connor is the name!)

.cg

sinkingfish

to the best of my knoladge youl have to breat it up a bit to include single quotes for the sql ( ' ) and to allow the variables to be seen.

eg

stmt.execute("INSERT INTO current_guests VALUES ('"room_num + " ', '" name + "', '" age + "','" address + "'" + ")"

probably not perfect but you get the idea, it is 2:30am after all and i am only a lowely student.

pH

If you want to mix java variables and SQL then you could have a look at SQLJ. This will involve adding a preprocessor/translator step to your compile but the end result will look very like your example.

The best way however is to use prepared statements. This is even more important when you use datatypes like dates. It will also handle the nasty cases when strings you are using contain the ' & " chars. Otherwise you need to handle all these cases manually and it can all change between databases.

Something like this:

    private void insert(int room, String name, int age, String address) {
        
        java.sql.Connection con = null;
        PreparedStatement ps = null;
        
        try {
            String sql = "INSERT INTO current_guests VALUES (? , ?, ?, ?)";
            ps = con.prepareStatement(sql);
            
            ps.setInt(1, room);
            ps.setString(2, name);
            ps.setInt(3,age);
            ps.setString(4, address);
            
            int count = ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            throw new RuntimeException("Error",e);
        }

    }