Bug #14851 Updated: problems w/ db2 char() for bit data

ID: 14851 Updated by: kalowsky@php.net Reported By: jfreed@fbsdata.com -Status: Open +Status: Closed Bug Type: ODBC related Operating System: Redhat-6.2 PHP Version: 4.1.1 New Comment: Please read your error message carefully and look at the work you've done. > $rs = odbc_exec($con, "SELECT LOGIN_NAME FROM LOGON WHERE > TECH_ID = ".$tech_id); > It seems to work okay, but all of the keys in our database > are definedas TECH_ID CHAR(13) FOR BIT DATA NOT NULL. My first suggestion is to say that your SELECT is wrong. If you're using a char, you need to place that information within single quotes to be marked. Previous Comments: ------------------------------------------------------------------------ [2002-01-04 12:13:07] jfreed@fbsdata.com I configured php-4.1.1 as follows: ./configure --with-mysql=usr --with-apache=/usr/local/apache/src/apache_1.3.22 --with-ibm-db2=/home/flexadm/sqllib --enable-track-vars It seems to work okay, but all of the keys in our database are defined as TECH_ID CHAR(13) FOR BIT DATA NOT NULL. This is then automatically generated with the GENERATE_UNIQUE() function of DB2. PHP appears to have problems working with this type of data. As the following code has problems on my system: <?php $con = odbc_pconnect("database", "username", "password") or die("odbc_pconnect() failed.<br>\n"); $rs = odbc_exec($con, "SELECT TECH_ID FROM LOGON WHERE LOGIN_NAME = 'sbr'"); if(odbc_fetch_row($rs)) $tech_id = odbc_result($rs, "tech_id"); else die("Could not find tech_id<br>\n"); $rs = odbc_exec($con, "SELECT LOGIN_NAME FROM LOGON WHERE TECH_ID = ".$tech_id); if(odbc_fetch_row($rs)) $login = odbc_result($rs, "login_name"); else $login = "UNKNOWN"; echo "TECH_ID: ".$tech_id."<br>\n"; echo "LOGIN: ".$login."<br>\n"; ?> The above code yields the following results in the browser: Warning: SQL error: [IBM][CLI Driver][DB2/SUN] SQL0007N The character " " following "OGON WHERE TECH_ID =" is not valid. SQLSTATE=42601 , SQL state 42601 in SQLExecDirect in /home/jfreed/public_html/debug.php on line 13 Warning: Supplied argument is not a valid ODBC result resource in /home/jfreed/public_html/debug.php on line 14 TECH_ID:
'EbR LOGIN: UNKNOWN This lead me to try the odbc_binmode() function. So I added the following line to my code (right under the initial odbc_exec() call: odbc_binmode($rs, ODBC_BINMODE_CONVERT); Now the output is as follows: Warning: SQL error: [IBM][CLI Driver][DB2/SUN] SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818 , SQL state 42818 in SQLExecDirect in /home/jfreed/public_html/debug.php on line 15 Warning: Supplied argument is not a valid ODBC result resource in /home/jfreed/public_html/debug.php on line 16 TECH_ID: 20010504142745046252 LOGIN: UNKNOWN Now, when I'm using the DB2 CLI to do this kind of stuff, I get the tech_id as: x'2001050412745046252000000' This leads me to believe that we're on the right track here, just that perhaps PHP is not translating this binary data quite properly. Please let me know if you have any advice. I would be very grateful to find a way around this problem as my company would require this to work in order to move from CGI to PHP. Thanks for your time. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=14851&edit=1