from powerbuilder script I'm using the following sql statement:
select max(user_id) into :ls_user from users;
(where ls_user is a string variable)
and I'm getting the following error
SQLSTATE = 37000
[MySQL][ODBC 3.51 Driver][mysqld-4.1.9-nt]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 '( user_id ) from users' at line 1
I'll get this error if I use also count.
In the ODBC I checked the options:
- Change BIGINT to INT
- Enable Dynamic cursor
Stick the same query you are using in your PowerScript into a DataWindow, then trap the SQL Prepare event to see what raw SQL Syntax is being submitted to MySQL. My guess would be that something is going awry with the conversion of the syntax in the script to what is actually submitted to MySQL (possibly in the bind variable handling)...
Without understanding what the PowerBuilder Runtime is doing "behind the curtains" to cause a SQL error, you are fighting blind!