• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL driver problem?

 
Greenhorn
Posts: 27
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Greg,
Very strange. Can you post the query here? Maybe there is an escape character in it?
 
Greg Belyea
Greenhorn
Posts: 27
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 ]
 
Greg Belyea
Greenhorn
Posts: 27
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic