• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Syntax check (losing hair now)...

 
John Brookes
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Been staring at this for a good couple of hours now, can't figure out what's wrong - any ideas?
stmt.executeUpdate("INSERT INTO orders (id_customer, model, trim, date) values ("+custID+", '"+modelIn+"', '"+trimIn+"', '"+dateIn+"')");
id_customer = autonumber, custID = int - all the rest are strings (java variables) or text in the column name cases. I've checked all my variable spelling, single-quotes around string variables but not around custID, etc...
Prog compiles otherwise but boots out when it gets to this line - terminal says syntax error in INSERT INTO statement.
Cheers
JB (SQL noob)
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
An initial inspection doesn't make anything jump out at me, but why spend all this angst and hair loss (btw, lost mine years ago -- it's not as bad as it seems) when you can make the driver do the work for you?
Investigate the use of PreparedStatement. It'll make your life worth living.
 
Lu Battist
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might try printing the sql.

Make sure none of those variables are null. If they are then that's the problem. Also check your database's list of reserve words, perhaps 'date' or 'trim' is not a valid column name. The other thing that might give you trouble is the autonumber on id_customer. You might try:
 
John Brookes
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm a first year uni student - the lecturer barely taught us enough Java to get through the year, he certainly hasn't taught us SQL properly. This is causing hair loss because it's for a big coursework and it's the only thing holding me back. The fact that I know it's going to be something small and simple makes it all the more irritating!
Oh well, that's the essensce of programming at the end of the day, even I know that
The code around that one line is:
int custID = 0;
int orderNum = 0;
stmt = conn.createStatement();
ResultSet custIDset = stmt.executeQuery("SELECT id_customer, surname FROM customer WHERE surname = '"+surname+"' and firstname = '"+firstname+"' and email = '"+email+"'");
if(custIDset.next())
{
custID = custIDset.getInt("id_customer");
}
stmt.executeUpdate("INSERT INTO orders (id_customer, model, trim, date) values ("+custID+", '"+modelIn+"', '"+trimIn+"', '"+dateIn+"')"); // PROBLEM!!
ResultSet orderNumSet = stmt.executeQuery("SELECT id_order FROM orders WHERE id_customer = "+custID);
if(orderNumSet.next())
{
orderNum = orderNumSet.getInt("id_order");
}
Iterator it = chosenOptions.iterator();
while(it.hasNext())
{
Integer selectionInt = (Integer)it.next();
int selection = selectionInt.intValue();
stmt.executeUpdate("INSERT INTO order_detail (id_order, id_selection) values ("+orderNum+", "+selection+")");
}
Maybe the problem's in here...
 
Padma Prasad
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
id_customer = autonumber, custID = int - all the rest are strings (java variables) or text in the column name cases. I've checked all my variable spelling, single-quotes around string variables but not around custID, etc...

What normally I would do with this kind of error is, try to print the insert statement. Print the insert statement as it is using println. may be you can see something going wrong somewhere. and if still nothing specific is found, try using the same query, whatever u printed, with SQL.
 
Stefan Wagner
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can't imagine an autonumber on cust_id in an orders-table.
... and while it looks syntactically correct (going for nulls and checking for reserved words might be a good idea - though the table shouldn't exist that way, if the names where reserved - you may send the insert-String to a file, and try to insert it with sql-plus or something like that.
And try to get more information from the jdbc-driver. There is often more than one message to be retrived, to investigate on an error.
btw: [ CODE] [/ CODE] - tags are welcomed.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic