Todd McNeill wrote:
[color=blue]
> Correction- The SQL should be:
>
> //Build SQL String
> $SQLSTRING="select snaptime, count(*) from $schema.stats group by
> snaptime having count(*) > 5 order by snaptime desc with ur";
>
> Thanks,
> Todd
>
>
toddles666@yahoo.com (Todd McNeill) wrote in message news:<9ccdfaea.0404151326.55bd7b6@posting.google.c om>...
>[color=green]
>>Hi-
>>
>>I'm getting some rather strange behavior with PHP4 on Debian Linux
>>accessing a DB2 V8.1 box on AIX. I've recompiled PHP "--with-ibm-db2"
>>support. I'm able to connect to the database just fine, but when I
>>attempt to select data from a table "WITH UR", I get the following
>>error:
>>
>>Warning: odbc_exec(): SQL error: [IBM][CLI Driver][DB2/AIX64] SQL0104N
>>An unexpected token "FOR UPDATE" was found following "naptime desc
>>with ur". Expected tokens may include: "<space>". SQLSTATE=42601 , SQL
>>state 42601 in SQLExecDirect in /var/www/recent.php on line 62
>>
>>Here's the PHP code snippet I'm running:
>>
>>--- Start Code ---
>>//Connect to database
>>$conn = odbc_connect($dsn,$user,$passwd,SQL_CUR_USE_DRIVER );
>>
>>//Build SQL String
>>$SQLSTRING="select snaptime, count(*) from $schema.stats where group
>>by snaptime having count(*) > 5 order by snaptime desc with ur";
>>
>>//Execute SQL
>>$resultset = odbc_Exec( $conn, $SQLSTRING); <-- Error occurs here.
>>--- End Code ---
>>
>>
>>It seems that for some reason, "FOR UPDATE" is being automagically
>>appended to my SQL String. I want to run with Uncommitted Read to
>>prevent lock waits on the stats table, which is being constantly
>>updated.
>>
>>If anybody has any insights, I'd greatly appreciate it.
>>
>>Thanks,
>>Todd[/color][/color]
My guess is that PHP is automatically set to a conservative isolation
level, which is why it tries to append "FOR UPDATE" to SELECT
statements. (I've seen this behaviour too.)
A user-contributed note to the odbc_exec function
(
http://ca.php.net/manual/en/function.odbc-exec.php) suggests tacking on
"FOR READ ONLY" to the SELECT statement to prevent PHP from adding "FOR
UPDATE". So if you try:
$SQLSTRING="select snaptime, count(*) from $schema.stats where group
by snaptime having count(*) > 5 order by snaptime desc FOR READ ONLY
with ur";
-- this should work; I recreated your problem with a local database and
resolved it by adding the FOR READ ONLY clause in the appropriate spot.
Note that use of the FOR READ ONLY clause is generally recommended for
improving performance of queries that won't be used to perform updates
(
http://publib.boulder.ibm.com/infoce.../r0000879.htm).
I suspect that if you specifiy FOR READ ONLY, you probably won't need to
specify WITH UR -- but that's just a suspicion.
Dan
