Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / fpc-pascal / 2007-09

sqldb query error on group by with where clause

6 messages · 5 people · –

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Bee

This query...

select k_province, k_city, k_school, count(file_id), sum(amt_student) from upload_log where (date(upload_time)='2007-09-14') group by k_province, k_city having k_province='205' and k_city='205031';

raises exception...

An unhandled exception occurred at $0808E144 :
EDatabaseError : Error executing query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by k_province, k_city having k_province='205' and k_city='205031';)' at line 1

tested on sqldb using tmysql50connection class on FPC 2.0.2, Ubuntu Feisty, mySQL 5.0.

The weird thing is... the same exact query is run fine when executed through mysql's console or mysql query browser or phpmyadmin or any other mysql client programs. Only sqldb that refused to run the query. If I remove the where clause (no date check), the query would run fine. That's why it confused me since I don't know what's wrong with it.

Any hints? Or is it sqldb's bug? TIA.

-Bee-

has Bee.ography at:
http://beeography.wordpress.com

Reply by email

Michael Van Canneyt
to Bee
Bee wrote 13 lines
This query... select k_province, k_city, k_school, count(file_id), sum(amt_student) from upload_log where (date(upload_time)='2007-09-14') group by k_province, k_city having k_province='205' and k_city='205031'; raises exception... An unhandled exception occurred at $0808E144 : EDatabaseError : Error executing query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by k_province, k_city having k_province='205' and k_city='205031';)' at line 1

Nevertheless, it's MySQL that reports the error.
Please set 'ParseSQL' to false and see if that helps ?

Michael.

Reply by email

Stephano
select k_province, k_city, k_school, count(file_id), >sum(amt_student) from upload_log where (date(upload_time)>='2007-09-14') group by k_province, k_city having k_province='205' >and k_city='205031'; raises exception...Add k_school to the group by clause.Stephano

Reply by email

Marc Santhoff
to Bee: Yes. But what did sqlDB do to the query so mysql didn't accept it? Yes, it helps. Thank…
Bee wrote 9 lines
> Nevertheless, it's MySQL that reports the error. Yes. But what did sqlDB do to the query so mysql didn't accept it? > Please set 'ParseSQL' to false and see if that helps ? Yes, it helps. Thank you. What does ParseSQL do anyway? What are the benefit of it?

This question can be answered by activating logging on the mysql server.

The log will show much detail, so beware of filling up your disk drive.

HTH,
Marc

Reply by email