Java and database

[Ariane Bogain]

I'm trying to send a query to an Oracle database but I'm not sure of the syntax and my compiler keeps insisting that there is an error, that an ";" is expected but I don't understand why as I already have an ";". I'm sure the problem is with the query itself so here is the snippet of my code:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url, user, password;
url ="jdbc dbc:BOOKING";
user = "Mlab2";
password = "bagpuss";
Connection conn =DriverManager.getConnection(url, user, password);
String code=jbcode.getText();
String sid=staff.getText();
String job=jobs.getText();
String start=Startdate.getText();
String end=Enddate.getText();
String query="INSERT INTO job VALUES('"+ sid +"','"+ code+"','" + job +"','" + start"','" + end +"','NULL','NULL','NULL')";
Statement stmt=conn.createStatement();
stmt.executeQuery(query);
conn.commit();
The error occurs with the line String query.
Could anyone help me?
Thanks

[John Smith]

String query="INSERT INTO job VALUES('"+ sid +"','"+ code+"','" + job +"','" + start"','" + end +"','NULL','NULL','NULL')";
You have too much stuff here and you seem to be afraid of spaces. Try it like this, it will work:

        String values = "'" + sid + "','" + code + "','" + job + "','" + start + "','" + end + "','NULL','NULL','NULL'";
        String query = "INSERT INTO job VALUES(" + values + ")";

[Jim Yingst]

One other possible problem is that if one of the strings contains a ', this will screw yo the syntax. To help debug this (or many other SQL problems), insert a System.out.println(query); to see what sort of query you've really created. Ideally this should be sent to some sort of logger rather than System.out.
To fix this problem (if it is a problem in your data) I recommend using a PreparedStatement instead. Alternately you could write code to replace any ' with '' before putting it in a query - but PreparedStatement takes care of this sort of stuff much more easily, IMO.

[Jim Yingst]

And come to think of it, this really should be in the JDBC forum, so I'm moving it there.

[Andy Bowes]

You need a '+' after the start variable on the query line.
As a side issue the SQL Insert statement will fail if anyone adds a new column to the table. Try using the following syntax that expilcity names the columns instead:
INSERT into TABLE1 (COL1, COL2, COL3) VALUES (VALUE1, VALUE2, VALUE3)
I tend to use PreparedStatements rather than all of the String concatenation that you are doing as ther can better handle bizarre text entered by the user i.e. commas, quotes etc.
e.g
PreparedStatement stmnt = conn.prepareStatement( "INSERT into TABLE1(COL1, COL2, COL3) VALUES (?,?,?)");
stmnt.setString(1, value1);
stmnt.setString(2, value2);
stmnt.setString(3, value2);
stmnt.executeUpdate();
Which I think is much more readable
HTH
Andy Bowes

[Ariane Bogain]

Thank you very much for your help, it's now working!
Thank you again,