Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mySQL query with JSP issue

 
Chris Cingrani
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you using executeQuery() or executeUpdate()?
Realize, if you are performing an update, you should use executeUpdate().
WS
 
Chris Cingrani
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Chris Cingrani
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Chris Cingrani
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1056
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic