ID: 32298
Updated by: [EMAIL PROTECTED]
Reported By: cryo28 at rbcmail dot ru
-Status: Open
+Status: Wont fix
Bug Type: InterBase related
Operating System: Win2000
PHP Version: 5.0.3
New Comment:
As you point out, this would involve rewriting the SQL based on the
value of parameters. In the current situation, PHP doesn't touch the
SQL, and relies on the client library to check for correctness. Adding
the capability you describe to PHP would inevitably require some SQL
parsing at the PHP level, which is something we could do without.
Furthermore, the whole point of prepare()/execute() is that the SQL is
processed only once, and executed multiple times with different
parameter values. Changing the SQL between executions kind of defeats
this purpose, as you would have to re-prepare the modified statement.
Previous Comments:
------------------------------------------------------------------------
[2005-03-16 09:34:23] cryo28 at rbcmail dot ru
I've tried it on latest snapshot downloaded from link above (win32
version). PHP 5.1.0-dev. Got same result. Here is a little changed
script and results of execution (table creationg and filling is the
same).
<?php
$db = ibase_connect('localhost:d:\inet\bases\wwwcat.gdb', 'SYSDBA',
'masterkey', 'win1251', null, 3);
$sql = 'SELECT * FROM TESTTABLE WHERE NULLFIELD = ?';
$st = ibase_prepare($db, $sql);
echo $sql.'. Param = NULL'."\r\n";
$q1 = ibase_execute($st, NULL);
var_dump($q1);
while ($r1 = ibase_fetch_assoc($q1)) var_dump($r1);
echo "\r\n ----------------------------- \r\n";
echo $sql.'. Param = 1'."\r\n";
$q2 = ibase_execute($st, 1);
var_dump($q2);
while ($r2 = ibase_fetch_assoc($q2)) var_dump($r2);
?>
SELECT * FROM TESTTABLE WHERE NULLFIELD = ?. Param = NULL
resource(4) of type (interbase result)
-----------------------------
SELECT * FROM TESTTABLE WHERE NULLFIELD = ?. Param = 1
resource(5) of type (interbase result)
array(2) {
["ID"]=>
int(3)
["NULLFIELD"]=>
int(1)
}
array(2) {
["ID"]=>
int(4)
["NULLFIELD"]=>
int(1)
}
------------------------------------------------------------------------
[2005-03-14 22:21:44] [EMAIL PROTECTED]
Please try using this CVS snapshot:
http://snaps.php.net/php5-latest.tar.gz
For Windows:
http://snaps.php.net/win32/php5-win32-latest.zip
------------------------------------------------------------------------
[2005-03-14 10:35:14] cryo28 at rbcmail dot ru
Description:
------------
Requesting feature is ability to pass null parameters with
ibase_prepare/execute. At this moment, a didn't found any way for
selecting rows by sql statement containing param in where_clause which
values can be null.
A similar work is done, for example, in FibPlus components for delphi
by autoconverting "WHERE FIELD=:param" to "WHERE FIELD IS NULL" if
:param=NULL.
Reproduce code:
---------------
CREATE TABLE TESTTABLE (
ID INTEGER NOT NULL,
NULLFIELD INTEGER
);
INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (1, NULL);
INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (2, NULL);
INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (3, 1);
INSERT INTO TESTTABLE (ID, NULLFIELD) VALUES (4, 1);
COMMIT WORK;
<?php
$db = ibase_connect('localhost:d:\inet\bases\wwwcat.gdb', 'SYSDBA',
'masterkey', 'win1251', null, 3);
$q_sql_1 = 'SELECT ID FROM TESTTABLE WHERE NULLFIELD IS NULL';
$q_sql_2 = 'SELECT ID FROM TESTTABLE WHERE NULLFIELD = 1';
$st_sql_1 ='SELECT ID FROM TESTTABLE WHERE NULLFIELD = ?';
$st_sql_2 ='SELECT ID FROM TESTTABLE WHERE NULLFIELD IS ?';
//PHPDocument1 string 10 - ibase_prepare() [<a
href='function.ibase-prepare'>function.ibase-prepare</a>]: Dynamic SQL
Error SQL error code = -104 Token unknown - line 1, char 45 ?
$st1 = ibase_prepare($db, $st_sql_1);
$st2 = ibase_prepare($db, $st_sql_2);
echo $q_sql_1."\r\n";
$q1 = ibase_query($q_sql_1);
while ($r1 = ibase_fetch_object($q1)) var_dump($r1);
echo "\r\n".'-----------------------'."\r\n";
echo $q_sql_2."\r\n";
$q2 = ibase_query($q_sql_2);
while ($r2 = ibase_fetch_object($q2)) var_dump($r2);
echo "\r\n".'-----------------------'."\r\n";
echo $st_sql_1.' param = 1'."\n\r";
$q4 = ibase_execute($st1, 1);
while ($r4 = ibase_fetch_object($q4)) var_dump($r4);
echo "\r\n".'-----------------------'."\r\n";
echo $st_sql_1.' param = NULL'."\n\r";
$q3 = ibase_execute($st1, NULL);
while ($r3 = ibase_fetch_object($q3)) var_dump($r3);
echo "\r\n".'-----------------------'."\r\n";
echo $st_sql_2.' param = NULL'."\n\r";
$q5 = ibase_execute($st2, NULL);
while ($r5 = ibase_fetch_object($q5)) var_dump($r5);
echo "\r\n".'-----------------------'."\r\n";
?>
Expected result:
----------------
Expected result is ability to pass NULL params to ibase_execute and get
right value as if query was WHERE NULLFIELD IS NULL
Actual result:
--------------
Content-type: text/html
X-Powered-By: PHP/5.0.3
SELECT ID FROM TESTTABLE WHERE NULLFIELD IS NULL
object(stdClass)#1 (1) {
["ID"]=>
int(1)
}
object(stdClass)#2 (1) {
["ID"]=>
int(2)
}
-----------------------
SELECT ID FROM TESTTABLE WHERE NULLFIELD = 1
object(stdClass)#2 (1) {
["ID"]=>
int(3)
}
object(stdClass)#1 (1) {
["ID"]=>
int(4)
}
-----------------------
SELECT ID FROM TESTTABLE WHERE NULLFIELD = ? param = 1
object(stdClass)#1 (1) {
["ID"]=>
int(3)
}
object(stdClass)#2 (1) {
["ID"]=>
int(4)
}
-----------------------
SELECT ID FROM TESTTABLE WHERE NULLFIELD = ? param = NULL
-----------------------
SELECT ID FROM TESTTABLE WHERE NULLFIELD IS ? param = NULL
-----------------------
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=32298&edit=1
