This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Java and database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Java and database" Watch "Java and database" New topic

Java and database

Ariane Bogain

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:
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();
The error occurs with the line String query.
Could anyone help me?
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

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

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:
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.
PreparedStatement stmnt = conn.prepareStatement( "INSERT into TABLE1(COL1, COL2, COL3) VALUES (?,?,?)");
stmnt.setString(1, value1);
stmnt.setString(2, value2);
stmnt.setString(3, value2);
Which I think is much more readable
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

Joined: Apr 25, 2003
Posts: 15
Thank you very much for your help, it's now working!
Thank you again,
I agree. Here's the link:
subject: Java and database
It's not a secret anymore!