File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Java and database

 
Ariane Bogain
Greenhorn
Posts: 15
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2937
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18671
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Wanderer
Sheriff
Posts: 18671
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And come to think of it, this really should be in the JDBC forum, so I'm moving it there.
 
Andy Bowes
Ranch Hand
Posts: 171
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much for your help, it's now working!
Thank you again,
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic