aspose file tools*
The moose likes JSP and the fly likes mySQL query with JSP issue 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 » Java » JSP
Bookmark "mySQL query with JSP issue" Watch "mySQL query with JSP issue" New topic
Author

mySQL query with JSP issue

Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Not sure where to place this question, but since the problem is occuring in a JSP application I am developing, I thought I would start here.
Anyways, I have a mySQL query that mySQL refuses to run. I made sure it is exactly like the documentation, but it won't run. Also, when I try and query the data via a select, it returns just fine. Here is the query:

Now, if I run this as a SELECT statement, it returns fine. Here is the select query:

If anyone has any ideas as to what I am doing wrong, I would appreciate it!
Thanks -
Chris
[ August 14, 2003: Message edited by: Chris Cingrani ]
[ August 14, 2003: Message edited by: Chris Cingrani ]
Winston Smith
Ranch Hand

Joined: Jun 06, 2003
Posts: 136
Are you using executeQuery() or executeUpdate()?
Realize, if you are performing an update, you should use executeUpdate().
WS


for (int i = today; i < endOfTime; i++) { code(); }
Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Winston -
Actually, I was trying to first get the query to work in mySQL and then I was going to move it to the JSP code. At this point, the query is still the problem (running through phpMyAdmin).
Thanks -
Chris
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
If you don't use PHP or JSP, and just enter that query directly into the mysql command-line tool, do you still get the error?


Ron Newman - SCJP 1.2 (100%, 7 August 2002)
Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Ron -
That is correct. The error is occuring when entering the query in mySQL, so this isn't a JSP problem at the moment.
Anyone have any ideas to the root of the mySQL syntax problem?
Thanks -
Chris
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Earlier you said that you were getting this error when you used a PHP application. Are you also getting it when you enter the command directly into the "mysql" command-line program?
When I enter it, I don't get a syntax error at all. Since I don't have your table structure installed, I get an error about the "draft" table not existing, but that's to be expected.
Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Thanks for getting back to me, Ron.
To answer your question, I only tried the query through the php GUI tool. I was under the impression that if my query didn't work there, it wouldn't work in the JSP code either.
Anyways, I don't have access to the mysql command line, so I just moved to the JSP code.
Here is my statement, plus the error being generated:

Thanks for the help -
Chris
[ August 14, 2003: Message edited by: Chris Cingrani ]
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Well, I see one problem right away, and it may even be the same thing that caused it to fail with PHP:
You are using double-quotes (" ") to surround the Java string, but also using them to surround a string that you are passing to the CONCAT() function. This causes a syntax error, which is what the compiler is complaining about. Try changing the inner quotes to single-quotes (' ').
You also appear to have an extraneous second semicolon at the end of the statement.
Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Ron -
I tried your suggestions, but got another error. The reference to an access violation seems bogus, becuase I ran another update query from my JSP just fine.
Here is the code and the error:

Thanks again!
Chris
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
What version of MySQL are you using?
From http://www.mysql.com/doc/en/UPDATE.html :

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

If you're using a version older than 4.0.4, you really should upgrade. The latest fully supported release is 4.0.14 .
Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Ron -
Just talked to my web host. Looks like they are on mySQL version 3.23.56, which explains the problem.
With that said - do you have any ideas on how to update the information? I can't think of anything offhand without getting data from the other table.
Thanks -
Chris
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
That's really ancient. Ask them to upgrade.
Otherwise, you'll need to lock one table for read and the other for write, query both tables, update the second one, then unlock both tables.
[ August 14, 2003: Message edited by: Ron Newman ]
[ August 14, 2003: Message edited by: Ron Newman ]
Faisal Khan
Ranch Hand

Joined: Jun 29, 2003
Posts: 285
Chris,
If the database on the server can not be upgraded what you can do is, export the two tables into CSV files. Install MySQL 4 on your machine, setup the two tables, make the update and then export the CSV file on local mahcine and finally import back onto the server.
Its probably the quickest thing you can do (though it doesn't spound like it), if the upgrade is not taking place.


The secret to creativity is knowing how to hide your sources.
Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Thanks for the feedback Ron and Faisal. It looks like I might be out of luck on this one, because I need to have it working by Sunday for a real time fantasy football draft. At this point, I am thinking that I will just manually delete records or update the database after each player is chosen. It isn't pretty, but hopefully they will have the database upgraded by next year. If not, I will be looking for a new provider.
Thanks again for all the help - I owe you one!
Chris
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
I did give you a solution that should work even with your old MySQL ...
"Otherwise, you'll need to lock one table for read and the other for write, query both tables, update the second one, then unlock both tables."
[ August 14, 2003: Message edited by: Ron Newman ]
Chris Cingrani
Ranch Hand

Joined: Nov 21, 2000
Posts: 69
Ron -
I forgot to mention that in my last message. I did try to get your solution to work (also referenced the doc) without any luck. Here is the last query I tried without luck (can't find avail_draft in update). However, when I add SELECT avail_draft.position FROM avail_draft to the second part of the set in the update, it doesn't work either:
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
You can't refer to more than one table in the UPDATE statement. You should do a join in the SELECT statement, get the primary keys of the row(s) you want to update, then update each row.
(this may be easier if you can be sure that only one row needs to be updated. what are the primary keys of these tables?)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: mySQL query with JSP issue