It's not a secret anymore!*
The moose likes JDBC and the fly likes MySQL driver problem? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL driver problem?" Watch "MySQL driver problem?" New topic
Author

MySQL driver problem?

Greg Belyea
Greenhorn

Joined: Jan 11, 2005
Posts: 19
Hi Guys

I am using the mysql-connector-java-3.1.7-bin.jar for my JDBC connection to my database and i am having a strange problem when executing a query. It does not seem to pay attention to my 'order by' clauses. I will do a print-out of the prepare statement before i execute it and i will get one result, then i'll paste it into the SQL window of MySQL and get another...

I can actually stick an invalid field name in after order by and it will still execute the query without throwing an exeption, but it will not do that in the SQL window, it will show up as a problem. Anyone seen this before, any suggestions i can try?

Thanks

Greg B
[ March 14, 2005: Message edited by: Bear Bibeault ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29220
    
135

Greg,
Very strange. Can you post the query here? Maybe there is an escape character in it?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Greg Belyea
Greenhorn

Joined: Jan 11, 2005
Posts: 19
Yeah here it is

select * from minwage where effective like ? and province like ? order by ? ASC

so after the prepare statement loads the parameters it will look exactly like i would want it to

select * from minwage where effevtive like '%77%' and province like '%N%' order by 'wage' ASC

problem is it will not execute the order by, so even if i submit a bad value for the order by, something that does not even exist, i will still get a resultset rather than the error that i should, it appears the order by is being ignored???

Greg B

Here is a system out just before parameter loading, and then before execution, i plyed with the method a bit so it's different than above.


com.mysql.jdbc.ServerPreparedStatement[1] - select * from minwage where province like null order by null desc

com.mysql.jdbc.ServerPreparedStatement[1] - select * from minwage where province like '%Fed%' order by 'wage' desc

the above line pasted into SQL window retrieves the desired results, however executing the pstmt, does not bring back the desired results? Very frustrating and not much info on similar issues???
[ March 15, 2005: Message edited by: Greg Belyea ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Greg Belyea:

select * from minwage where effevtive like '%77%' and province like '%N%' order by 'wage' ASC



I think order by 'wage' is your problem, because in sql order by clause is always followed by a column name not a String.

if you really want to set order by dynamically I would recommend to use column sequence in query because in prepared statement setInt for Order by would work but setString I am not sure

make your query somthing like

select a,b,c from mytable order by 1

above query is equivalent to

select a,b,c from mytable order by a

OR

select a,b,c from mytable order by 2

and this is equivalent to

select a,b,c from mytable order by b


hope this helps to solve your problem of oder by.


thanks
[ March 15, 2005: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Greg Belyea
Greenhorn

Joined: Jan 11, 2005
Posts: 19
Shailesh

Yes that does work, it does complicate what the knowledge the user must have on the client side but it may not be too much of an issue. Thanks for the help, i still find it a bit odd the in the SQL window, the results come back no prob with 'wage', but it won't for the application, but i am not gonna spend much time on it...

Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL driver problem?
 
Similar Threads
"select.." in jdbc gives bad performance but not in mysql directly
MySQL error
select vs desc
apostrophe ( ' ) issue in SQL of Hibernate.
problem in formatting date