MySQL Bugs: #70121: Connector J fails to communicate with powerbuilder applicaiton

Bug #70121	Connector J fails to communicate with powerbuilder applicaiton
Submitted:	22 Aug 2013 13:11	Modified:	22 Aug 2013 14:00
Reporter:	Prashant Nirgun	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.26	OS:	Windows (Window 7 32 Bit)
Assigned to:		CPU Architecture:	Any
Tags:	JDBC PowerBuilder

Description:
I am using my Powerbuilder 11.5 application to connect to mysql using jdbc driver but it get connected successfully but whenever i used any function it fails. let me explain you

Error :
SQLSTATE : 42000
You have an error in your SQL Syntax, check the manual that corresponds to your MySQL server version for right syntax to use near '*) from my_table ' at line 1

//Connection parameters
SQLCA.DBMS = "JDBC"
SQLCA.LogPass = "tss"
SQLCA.LogId = "root"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Driver='com.mysql.jdbc.Driver',URL='jdbc:mysql://localhost:3306/transporter3',Option=3"
//SQLCA.DBParm = "Driver='com.mysql.jdbc.Driver',URL='jdbc:mysql://localhost:3306/mydb'"
//SQLCA.DBParm = "Driver='com.mysql.jdbc.Driver',URL='jdbc:mysql://localhost:3306/mydb',TrimSpaces='Yes'"

connect using sqlca ;
int i
IF SQLCA.SQLCode = -1 THEN
MessageBox("SQL error", SQLCA.SQLErrText)
Else
select count(*) into :i from tr_table ;
IF SQLCA.SQLCode = -1 THEN MessageBox("SQL error", SQLCA.SQLErrText)
END IF

Previously i was using ODBC driver to tackle this situation in ODBC driver we have seprate option Ignore space after function name if i set that option it works fine. But now we want DSN less connection & speed we been moving out to JDBC please help its

I am using PowerBuilder 11.5 Build 2506 and MySQL J Connector Version 5.1.26 on Wndows 7 32 Bit machine

How to repeat:
select count(*) into :i from tr_table ;

use any function which has prathesis it just fails

Suggested fix:
need to set any options to fix this error for odbc we set option=4096

Hello Prashant,

Thanks for your bug report.  We don't explicitly test Powerbuilder integration, but would like to help you out.  The SQL statement you provided doesn't appear to have syntax errors, so we'd like to see the actual SQL statement received by MySQL Server.  Can you please enable the MySQL general query log:

http://dev.mysql.com/doc/refman/5.6/en/query-log.html

If you are using a MySQL 5.6 Server, please also use the log-raw option to ensure syntax errors get logged to the general query log:

http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-raw

(even better, if you can enable the MySQL Enterprise Audit Log plugin, it's easier to extract the problematic statement)

Please provide the log entry for the problematic statement.

please delete the thread it resolve this problem the solution is

i resolve this issue mysql option -i or --ignore-spaces is going to work so i set that option in configuration files i.e. my.ini

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,IGNORE_SPACE"

stop MySQL service and then started it works smoothly