Обсуждение: SQLDescribeParam / SUPPORT_DESCRIBE_PARAM
Hello, I noticed that unixODBC logs IM001 SQL_ERRORs for SQLDescribeParam in its tracefile. Actually, I first noticed the problem when I tried to use a prepared statement in PHP 5.3.8 with a PostgreSQL 9.1 database and I got error messages at odbc_execute() due to the SQLDescribeParam error. First I thought that the problem is in PHPs ODBC extension because it worked fine back in PHP-5.3.3. But digging into the sources of PHP I found out, that the return value of SQLDescribeParam simply wasn't validated before 5.3.5 (and there as no 5.3.4 release), but it returned an IM001 SQL_ERROR ever since. So following the chain I looked at the source of psqlodbc and noticed, that the library is prepared for a database connection that supports SQLDescribeParam and this is verified by SUPPORT_DESCRIBE_PARAM(). But what does it depend on, whether the connection supports it? psqlodbc is for PostgreSQL only. I built the latest PostgreSQL library, compiled the latest psqlodbc against it and SQLDescribeParam still threw the IM001 SQL_ERROR. As a quick fix just created a patch for PHP for my internal use, which removes checking of the return values of SQLDescribeParam. But that seems very odd. I'd really like to understand what's behind this issue. Kind regards Marten Lehmann
Hi Marten, (2011/11/09 10:42), Marten Lehmann wrote: > Hello, > > I noticed that unixODBC logs IM001 SQL_ERRORs for SQLDescribeParam in > its tracefile. Actually, I first noticed the problem when I tried to use > a prepared statement in PHP 5.3.8 with a PostgreSQL 9.1 database and I > got error messages at odbc_execute() due to the SQLDescribeParam error. > > First I thought that the problem is in PHPs ODBC extension because it > worked fine back in PHP-5.3.3. But digging into the sources of PHP I > found out, that the return value of SQLDescribeParam simply wasn't > validated before 5.3.5 (and there as no 5.3.4 release), but it returned > an IM001 SQL_ERROR ever since. > > So following the chain I looked at the source of psqlodbc and noticed, > that the library is prepared for a database connection that supports > SQLDescribeParam and this is verified by SUPPORT_DESCRIBE_PARAM(). > > But what does it depend on, whether the connection supports it? psqlodbc > is for PostgreSQL only. I built the latest PostgreSQL library, compiled > the latest psqlodbc against it and SQLDescribeParam still threw the > IM001 SQL_ERROR. > > As a quick fix just created a patch for PHP for my internal use, which > removes checking of the return values of SQLDescribeParam. But that > seems very odd. I'd really like to understand what's behind this issue. Please check the *Server side prepare* option of your datasource or add UseServerSidePrepare=1 to your connection string. regards, Inoue, Hiroshi > Kind regards > Marten Lehmann
Hello,
> Please check the *Server side prepare* option of your datasource or
> add UseServerSidePrepare=1 to your connection string.
I tried, but it doesn't work completely. Actually, when I just use
UseServerSidePrepare = 1 in odbc.ini, I get some error like this:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to
allocate 3086129905 bytes) in testbase.php on line 64
Then when I add Parse = 1 it works fine on the command line, ie. when I
call the php-cgi instance with the testscript.
But is soon as I start the script through Apache und mod_php with the
same ldd-dependencies, I again get exhausted memory errors.
I traced the query - it's as simple as "select count(*) from sessions
where cust_id = ?" - and I found these lines:
[ODBC][3429][1320890058.574677][SQLNumResultCols.c][248]
Exit:[SQL_SUCCESS]
Count = 0xb7f2a80c -> 1
[ODBC][3429][1320890058.574872][SQLColAttributes.c][280]
Entry:
Statement = 0x9c3cff0
Column Number = 1
Field Identifier = SQL_COLUMN_NAME
Character Attr = 0xb7f2a82c
Buffer Length = 32
String Length = 0xbff54e3a
Numeric Attribute = (nil)
[ODBC][3429][1320890058.576863][SQLColAttributes.c][597]
Exit:[SQL_SUCCESS]
So SQLNumResultCols = 1 seems to be correct. But String Length =
0xbff54e3a seems to be very absurd! That's 3220524602 bytes which means
around 3 GB! But the correct result is an integer of 5. Any idea how
this problem arises? Does psqlodbc through mod_php not set the
Parse-option? What is the actual problem? I didn't have such problems
with the DBD::Pg driver.
Kind regards
Marten
Hi, (2011/11/10 11:07), lehmann@cnm.de wrote: > Hello, > >> Please check the *Server side prepare* option of your datasource or >> add UseServerSidePrepare=1 to your connection string. > > I tried, but it doesn't work completely. Actually, when I just use > UseServerSidePrepare = 1 in odbc.ini, I get some error like this: > > Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to > allocate 3086129905 bytes) The above message comes from the PostgreSQL server. Could you examine which query causes the error using the server log? > in testbase.php on line 64 > > Then when I add Parse = 1 it works fine on the command line, ie. when I > call the php-cgi instance with the testscript. > > But is soon as I start the script through Apache und mod_php with the > same ldd-dependencies, I again get exhausted memory errors. > > I traced the query - it's as simple as "select count(*) from sessions > where cust_id = ?" - and I found these lines: > > [ODBC][3429][1320890058.574677][SQLNumResultCols.c][248] > Exit:[SQL_SUCCESS] > Count = 0xb7f2a80c -> 1 > [ODBC][3429][1320890058.574872][SQLColAttributes.c][280] > Entry: > Statement = 0x9c3cff0 > Column Number = 1 > Field Identifier = SQL_COLUMN_NAME > Character Attr = 0xb7f2a82c > Buffer Length = 32 > String Length = 0xbff54e3a > Numeric Attribute = (nil) > [ODBC][3429][1320890058.576863][SQLColAttributes.c][597] > Exit:[SQL_SUCCESS] > > So SQLNumResultCols = 1 seems to be correct. But String Length = > 0xbff54e3a seems to be very absurd! That's 3220524602 bytes which means > around 3 GB! But the correct result is an integer of 5. Any idea how > this problem arises? Does psqlodbc through mod_php not set the > Parse-option? What is the actual problem? I didn't have such problems > with the DBD::Pg driver. > > Kind regards > Marten