PHP 4.0 Bug #8346: DB2 CLI do not support SQL_CURSOR_DYNAMIC

From: Paul.Pryor@eCommSecurity.com Operating system: AIX 4 PHP version: 4.0.3pl1 PHP Bug Type: ODBC related Bug description: DB2 CLI do not support SQL_CURSOR_DYNAMIC When you execute a php script which uses odbc_exec for more than 946 times (milage may vary), the following error message is returned: Warning - SQL error: [IBM][CLI Driver][DB2/6000] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011 The following information is discovered in IBM DB2 CLI manual: SQL_ATTR_CURSOR_TYPE (DB2 CLI v2) A 32-bit integer value that specifies the cursor type. The supported values are: SQL_CURSOR_FORWARD_ONLY = The cursor only scrolls forward. SQL_CURSOR_STATIC = The data in the result set is static. This is the default. SQL_CURSOR_KEYSET_DRIVEN = DB2 CLI supports a pure keyset cursor. The SQL_KEYSET_SIZE statement attribute is ignored. To limit the size of the keyset the application must limit the size of the result set by setting the SQL_ATTR_MAX_ROWS attribute to a value other than 0. This option cannot be specified for an open cursor. If the specified cursor type is not supported by the data source, CLI substitutes a different cursor type and returns SQLSTATE 01S02 (Option value changed). For a mixed or dynamic cursor, CLI substitutes, in order, a keyset-driven or static cursor. Note: The following value has also been defined by ODBC, but is not supported by DB2 CLI: SQL_CURSOR_DYNAMIC If this values is used, DB2 CLI sets the statement attribute to SQL_CURSOR_STATIC SQL_CURSOR_FORWARD_ONLY and returns SQLSTATE 01S02 (Option value changed). In this case the application should call SQLGetStmtAttr() to query the actual value. The quick work around is to edit ext/odbc/php_odbc.c, and disable SQL_CURSOR_DYNAMIC, and to change all instances of SQL_CURSOR_DYNAMIC to SQL_CURSOR_FORWARD_ONLY. It appear that DB2/CLI exhibits memory leak when statement type is set to SQL_CURSOR_DYNAMIC. The diff follows: --- php_odbc.c.orig Thu Dec 21 00:44:49 2000 +++ php_odbc.c Thu Dec 21 01:00:30 2000 @@ -354,7 +354,9 @@ REGISTER_LONG_CONSTANT("SQL_CURSOR_TYPE", SQL_CURSOR_TYPE, CONST_PERSISTENT | CONST_CS); REGISTER_LONG_CONSTANT("SQL_CURSOR_FORWARD_ONLY", SQL_CURSOR_FORWARD_ONLY, CONST_PERSISTENT | CO NST_CS); REGISTER_LONG_CONSTANT("SQL_CURSOR_KEYSET_DRIVEN", SQL_CURSOR_KEYSET_DRIVEN, CONST_PERSISTENT | CONST_CS); +#ifndef HAVE_IBMDB2 REGISTER_LONG_CONSTANT("SQL_CURSOR_DYNAMIC", SQL_CURSOR_DYNAMIC, CONST_PERSISTENT | CONST_CS); +#endif REGISTER_LONG_CONSTANT("SQL_CURSOR_STATIC", SQL_CURSOR_STATIC, CONST_PERSISTENT | CONST_CS); REGISTER_LONG_CONSTANT("SQL_KEYSET_SIZE", SQL_KEYSET_SIZE, CONST_PERSISTENT | CONST_CS); @@ -758,7 +760,11 @@ /* Try to set CURSOR_TYPE to dynamic. Driver will replace this with other type if not possible. */ +#ifdef HAVE_IBMDB2 + if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) +#else if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC) +#endif == SQL_ERROR) { odbc_sql_error(conn->henv, conn->hdbc, result->stmt, " SQLSetStmtOption" ); SQLFreeStmt(result->stmt, SQL_DROP); @@ -1085,7 +1091,11 @@ /* Try to set CURSOR_TYPE to dynamic. Driver will replace this with other type if not possible. */ +#ifdef HAVE_IBMDB2 + if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) +#else if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC) +#endif == SQL_ERROR) { odbc_sql_error(conn->henv, conn->hdbc, result->stmt, " SQLSetStmtOption" ); SQLFreeStmt(result->stmt, SQL_DROP); -- Edit Bug report at: http://bugs.php.net/?id=8346&edit=1