aspose file tools*
The moose likes JDBC and the fly likes Can someone figure out whats wrong with this SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can someone figure out whats wrong with this SQL" Watch "Can someone figure out whats wrong with this SQL" New topic
Author

Can someone figure out whats wrong with this SQL

moiz eleven
Greenhorn

Joined: May 22, 2002
Posts: 9
I got this program out of a book and this code part works on Microsoft Access but on MySQL it give me an err.
First the CODE:
String updateStatement =
"UPDATE Books " +
"SET Price = Price + " + priceChange.getText() +
"WHERE Books.Publisher_Id = " +
"(SELECT Publisher_Id FROM Publishers WHERE Name = '" + publisher + "')";
The ERROR I get is:
Error java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near 'SELECT Publisher_Id FROM Publishers WHERE Name = 'Academic Press')' at line 1
Does anyone know whats wrong with the SQL statement? THanks.
Bosun Bello
Ranch Hand

Joined: Nov 06, 2000
Posts: 1510
I don't see anything wrong with the SQL. Since it works in MSAccess, and the error says syntax error or access violation, it may have to do with security.


Bosun (SCJP, SCWCD)
So much trouble in the world -- Bob Marley
Gustavo Adolpho Bonesso
Ranch Hand

Joined: May 11, 2002
Posts: 103
Hi,
MySQL has some limitations about nested selects (sub-selects), like you are trying to do... See the link
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
explane to you these limitations...
Best Regards !


Gustavo Adolpho Bonesso<br />SCJP1.4
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"moiz11", "gustavo_bonesso",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave
moiz eleven
Greenhorn

Joined: May 22, 2002
Posts: 9
Thanks I read the info on the mySQL link, please forgive my ignorance - but how can the same statement be written in another form in SQL - without doing the subquery so that it will work? THanks again.
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Hi there,
I can't think of a way of rewriting the SQL statement, but it seems that you are giving yourself the problem in the first place because you don't store the publisher_id
If you did, the performance would be alot better as well. Plus having your logic based on the publisher name means that you can update the name of the publisher while your program is live - a minor point I suppose.
Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Kat Lee
Greenhorn

Joined: May 23, 2002
Posts: 2
This is not consistent with the error message you're getting, but it appears that there is no space between the price value priceChange.getText() and the WHERE keyword.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by moiz11:
I got this program out of a book and this code part works on Microsoft Access but on MySQL it give me an err.
First the CODE:
String updateStatement =
"UPDATE Books " +
"SET Price = Price + " + priceChange.getText() +
"WHERE Books.Publisher_Id = " +
"(SELECT Publisher_Id FROM Publishers WHERE Name = '" + publisher + "')";
The ERROR I get is:
Error java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near 'SELECT Publisher_Id FROM Publishers WHERE Name = 'Academic Press')' at line 1
Does anyone know whats wrong with the SQL statement? THanks.

try using the keyword 'IN' instead of '=' ( as well as correct the spacing issue pointed out earlier );
String updateStatement =
" UPDATE Books " +
" SET Price = Price + " + priceChange.getText() +
" WHERE Books.Publisher_Id IN " +
" ( SELECT Publisher_Id FROM Publishers WHERE Name = '" + publisher + "')";
sometimes databases can be picky as your subquery may return more than one row, in which case you can not use =.
not sure if IN is ANSI compatible, but it works in Oracle, MSAccess, etc...
If that doesn't work, split your query into 2 SQL statements. Find the publisher ID first, then update the price using the publisher ID that you got in the first statement.
Jamie
[ May 24, 2002: Message edited by: Jamie Robertson ]
moiz eleven
Greenhorn

Joined: May 22, 2002
Posts: 9
ok guys thanks for all the suggestions, but i tried the spacing idea - and that did not work, i even tried it on the command line - same result. I tried the "IN" method - again same result. this leads me to believe that it may not be an SQL syntax problem at all (since the exact same program worked perfectly fine in MSAccess). i think as the err says it may just be - access violation. now how do i give a specific USER and his PASSWORD permissions to UPDATE a database?
thanx again.
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Well you can make sure pretty easily but substituting in the root user & password. If so, the MySQL docs are very good, at http://www.mysql.com/
Adam
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

Try being EXTRA specific, and reduce specificity as capable


The helped me with some problems in access with same names in multiple tables in a check constraint. the whole thing nullpointered me anyway, but at least the statement was right
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can someone figure out whats wrong with this SQL