GeeCON Prague 2014*
The moose likes JDBC and the fly likes Java and database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java and database" Watch "Java and database" New topic
Author

Java and database

Ariane Bogain
Greenhorn

Joined: Apr 25, 2003
Posts: 15
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
Ranch Hand

Joined: Oct 08, 2001
Posts: 2937
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:
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
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.


"I'm not back." - Bill Harding, Twister
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
And come to think of it, this really should be in the JDBC forum, so I'm moving it there.
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
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


Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
Ariane Bogain
Greenhorn

Joined: Apr 25, 2003
Posts: 15
Thank you very much for your help, it's now working!
Thank you again,
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Java and database