php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #58440 reserved word DEFAULT not recognized
Submitted: 2008-12-01 06:37 UTC Modified: 2009-03-03 03:53 UTC
From: shlomo dot v at zend dot com Assigned:
Status: Closed Package: ibm_db2 (PECL)
PHP Version: 5.2.5 OS: i5/OS
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
33 - 3 = ?
Subscribe to this entry?

 
 [2008-12-01 06:37 UTC] shlomo dot v at zend dot com
Description:
------------
The DEFAULT keyword not recognized Using the ZENDCORE PHP for i5/OS!

The reserved keyword DEFAULT should be acknowledged and the default value specified for the field with the CREATE TABLE option should be used and placed when you INSERTing a record/row. 



Reproduce code:
---------------
<?php
                                 
// Connect to AS400
echo "<br>Connect to Iseries";
$db = db2_connect("DBname","user", "password");
if(!$db) {
    echo "<br>Connection Failed: " . db2_conn_errormsg();
} else echo "<br>Connectction Established";
// Prepare Statement
$sql = 'INSERT INTO UserLib.TESTfile (FO1, FO2, FO3) VALUES(?, ?, ?)';
echo "<br>Prepare statement SQL:".$sql;
$stmt = db2_prepare($db, $sql);
if(!$stmt) {
    echo "<br>Prepare Failed: " . db2_stmt_errormsg();
} else echo "<br>Prepare OK";


//define("DEFAULT", "DEFAULT");


// Fieldd Value
echo "<br>Field Value";
$fo1 = value1;
$fo2 = "DEFAULT"; // Want to use the default SQL keyword 
//$fo2 = constant("DEFAULT");
$fo3 = 'default';
echo "<br>FPO1:".$fo1;
echo "<br>FO2:".$fo2;
echo "<br>FO3:".$fo3;

// Execute preparede statement
$fields = array($fo1, $fo2, $fo3);
echo "<br>Execute statement SQL".print_r($fields);
$result = db2_execute($stmt, $fields);
if(!$result) {
        echo "<br>Execution Failed: " . db2_stmt_errormsg();
} else echo "<br>Execute OK";
?> 

Expected result:
----------------
The reserved keyword DEFAULT should be acknowledged and the default value specified for the field with the CREATE TABLE option should be used and placed when you INSERTing a record/row. 


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-12-15 06:52 UTC] abhargav at in dot ibm dot com
Today, this code will not work on any DB2 product LUW or i5/OS (probably no databases at all) ... 
1) LUW simply does it wrong by converting the parameter marker "DEFAULT" string to a long 0
2) while i5/OS offers an error because parameter marker "DEFAULT" is not a INTEGER
... there are multiple workarounds for the problem using a "second" db2_prepare (see BTW example bottom page).

=============================
In the spirit of "what if we fixed it with invention" ...
=============================
In i5/OS V6R1 we have new connection attribute SQL_ATTR_EXTENDED_INDICATORS  = SQL_TRUE ... so it would be possible to "invent" some sort of PHP syntax that would be honored at the PECL ibm_db2 level in SQLBindParameter ...
-- maybe this "string" escape sequence $fo2="DEFAULT::int";
-- and while we are inventing no-standard syntax perhaps  ...  $fo2="NULL::int";
...whew ...but ... do we really want to do this sort of thing (reminder DEFAULT::int would not work below V6R1)?

=======================
code will not work
=======================
CODE will not work:
$sql = "DROP table testDefault";
$sql = "CREATE table testDefault (id INTEGER NOT NULL, me INTEGER NOT NULL default 42)";
$stmt = db2_exec($conn, $sql);
db2_error($stmt,$sql);

$fields = array($fo1=1, $fo2="DEFAULT");
$sql = "INSERT INTO testDefault (id,me) values (?,?)";
$stmt = db2_prepare($conn, $sql);
db2_error($stmt,"prepare(".$sql.")");
$r=db2_execute($stmt,$fields);
db2_error($r,"execute(".$sql.")");

LUW DB2 "bad" output (linux):
Valid: DROP table testDefault
Valid: CREATE table testDefault (id INTEGER NOT NULL, me INTEGER NOT NULL default 42)
Valid: prepare(INSERT INTO testDefault (id,me) values (?,?))
Valid: execute(INSERT INTO testDefault (id,me) values (?,?))
Valid: SELECT * FROM testDefault
Valid: fetch id=1 me=0 

i5/OS DB2 "bad" output:
Valid: DROP table testDefault
Valid: CREATE table testDefault (id INTEGER NOT NULL, me INTEGER NOT NULL default 42)
Valid: prepare(INSERT INTO testDefault (id,me) values (?,?))
Error: execute(INSERT INTO testDefault (id,me) values (?,?)) SQLSTATE value: 22018SQL Message: Character in CAST argument not valid. SQLCODE=-420
Valid: SELECT * FROM testDefault 


=======================
BTW -- this is a workaround
=======================
CODE will work:
$sql = "INSERT INTO testDefault (id,me) values (1,DEFAULT)";
$stmt = db2_prepare($conn, $sql);
db2_error($stmt,"prepare(".$sql.")");
$r=db2_execute($stmt);
db2_error($r,"execute(".$sql.")");

$sql = "SELECT * FROM testDefault";
$stmt = db2_exec($conn, $sql);
db2_error($stmt,$sql);
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Mon Sep 23 15:01:27 2019 UTC