MySQL Bugs: #69671: 5.2.5 fails to execute batch statements with parameter values

Bug #69671	5.2.5 fails to execute batch statements with parameter values
Submitted:	4 Jul 2013 15:55	Modified:	8 Jul 2013 10:51
Reporter:	Sam Varshavchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.2.5	OS:	Linux
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
The following example appears to fail with 5.2.5, but works with 5.1.11.

Using literal values in the SQL, instead of binding parameters, seems to work too.

The sample table's schema is:

CREATE TABLE `temptbl` (
  `intkey` int(11) NOT NULL,
  `strval` varchar(255) NOT NULL,
  PRIMARY KEY (`intkey`)
)

How to repeat:
#include <odbcinst.h>
#include <stdio.h>
#include <sqlext.h>

int main()
{
	SQLHENV e;
	SQLHDBC c;
	SQLHSTMT s;

	if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &e)))
		return 0;

	if (!SQL_SUCCEEDED(SQLSetEnvAttr(e, SQL_ATTR_ODBC_VERSION,
                                         (void *) SQL_OV_ODBC3, 0)))
		return 0;

	if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, e, &c)))
		return 0;

	if (!SQL_SUCCEEDED(SQLDriverConnect(c, (SQLHWND)0,
					    (SQLCHAR *)
					    /* "DSN=postgres",*/
					    "DSN=mysql;UID=***;PWD=***;OPTION=67108864",
					    SQL_NTS,
					    0,
					    0,
					    0,
					    SQL_DRIVER_NOPROMPT)))
		return 0;

	if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, c, &s)))
		return 0;

	if (!SQL_SUCCEEDED(SQLPrepare(s, (SQLCHAR *)
				      "delete from temptbl where intkey=?; insert into temptbl(intkey, strval) values(?, ?)", SQL_NTS)))
	{
		printf("SQLPrepare failed\n");
		return 0;
	}

	if (!SQL_SUCCEEDED(SQLBindParameter(s, 1, SQL_PARAM_INPUT,
					    SQL_CHAR,
					    SQL_C_CHAR,
					    1,
					    0,
					    "2",
					    1,
					    0)))
		return 0;
	if (!SQL_SUCCEEDED(SQLBindParameter(s, 2, SQL_PARAM_INPUT,
					    SQL_CHAR,
					    SQL_C_CHAR,
					    1,
					    0,
					    "2",
					    1,
					    0)))
		return 0;
	if (!SQL_SUCCEEDED(SQLBindParameter(s, 3, SQL_PARAM_INPUT,
					    SQL_CHAR,
					    SQL_C_CHAR,
					    1,
					    0,
					    "2",
					    1,
					    0)))
		return 0;

	if (!SQL_SUCCEEDED(SQLExecute(s)))
	{
		printf("SQLExecute failed\n");
		return 0;
	}

	printf("Connected\n");
	return 0;
}

Hello Sam,

Thank you for reporting a bug in MySQL Connector/ODBC and for providing the C test case. 

You should keep in mind that by default all statements are prepared and then executed on the server. The test case fails with the syntax error because you cannot prepare a batch statement on the MySQL Serve side. It is the expected behavior of MySQL Server and ODBC driver.

The workaround for this problem is to use a special option to prepare the statements on the client side.
This option makes the driver to put parameter values into the query BEFORE anything is sent to the server. Hence, the server receives the query with all parameter values already embedded in the query text.

You can either add NO_SSPS=1 to your connection string or select the checkbox in the GUI dialog for your Data Source (Details -> Misc -> Prepare Statements on the client).

Please let me know whether the above workaround helped to resolve the issue (the test case worked for me after I added NO_SSPS=1 to the connection string).
Thanks.

Thanks, that seems to work.