I am trying to invoke a PHP script from Postman. The script uses a
merge function to either update or add the record. I tested the syntax
using ACS and it will successfully execute either on an update or add
using static values. But if I change the statement to be a parameterized
statement, the execution fails. The error at the Postman end is: Failed
SQL Stmt:Resource id #4:: As I understand a "Resource" in PHP it is a
internal variable reference. If so, resource #4 is the SQL statement
itself. At the Apache end of things, using an Apache instance to host
the php script, I see two errors:
PHP Warning: db2_execute(): Value Not Bound in
/www/myapp/htdocs/DB_merge.php on line 44
PHP Warning: db2_execute(): Binding Error 3 in
/www/myapp/htdocs/DB_merge.php on line 44
Here is the code:
<?php
$conn_resource = db2_connect("*LOCAL", "", "");
/* Construct the SQL statement */
$sql = "MERGE INTO QIWS.QCUSTCDT A
USING (Values( CAST(? as int),Cast(? as varchar(8)),cast(? as
varchar(3)),cast(? as varchar(13)),cast(? as varchar(6)),
cast(? as varchar(2)),cast(? as int),cast(? as int),cast(? as
int),cast(? as int),cast(? as int)))
AS B
(CUSNUM,LSTNAM,INIT,STREET,CITY,STATE,ZIPCOD,CDTLMT,CHGCOD,BALDUE,CDTDUE)
ON B.CUSNUM = A.CUSNUM
WHEN MATCHED THEN UPDATE SET A.LSTNAM =
B.LSTNAM,A.INIT=B.INIT,A.STREET=B.STREET,A.CITY=B.CITY,A.STATE=B.STATE,
A.ZIPCOD=B.ZIPCOD,A.CDTLMT=B.CDTLMT,A.CHGCOD=B.CHGCOD,A.BALDUE=B.BALDUE,A.CDTDUE=B.CDTDUE
WHEN NOT MATCHED THEN INSERT
(CUSNUM,LSTNAM,INIT,STREET,CITY,STATE,ZIPCOD,CDTLMT,CHGCOD,BALDUE,CDTDUE)
values(B.CUSNUM,B.LSTNAM,B.INIT,B.STREET,B.CITY,B.STATE,b.ZIPCOD,B.CDTLMT,B.CHGCOD,B.BALDUE,B.CDTDUE)";
/* Prepare and execute the DB2 SQL statement */
$stmt= db2_prepare($conn_resource, $sql);
db2_bind_param($stmt, 1, $_REQUEST['cusnum']);
db2_bind_param($stmt, 2, $_REQUEST['lastname']);
db2_bind_param($stmt, 3, $_REQUEST['initials']);
db2_bind_param($stmt, 4, $_REQUEST['street']);
db2_bind_param($stmt, 5, $_REQUEST['city']);
db2_bind_param($stmt, 6, $_REQUEST['state']);
db2_bind_param($stmt, 7, $_REQUEST['zipcode']);
db2_bind_param($stmt, 8, $_REQUEST['creditlimit']);
db2_bind_param($stmt, 9, $_REQUEST['chargecode']);
db2_bind_param($stmt, 10, $_REQUEST['balancedue']);
db2_bind_param($stmt, 11, $_REQUEST['creditdue']);
$result = db2_execute($stmt) or die("Failed SQL
Stmt:".$stmt.":".db2_stmt_error().":".db2_stmt_errormsg());
?>
The result, returned to Postman is the Failed SQL Stmt:Resource id #4::
Note that the other two values are empty..I think there should be more
info included.
I am not a PHP expert by any means but is there a way to get MORE
information on what is failing? If I replace the parameters in the
VALUE statement with static values and comment out the db2_bind
statements, the statement executes correctly. But there is something it
doesn't like about the statement. Postman is correctly passing the
values AFAICT:
DB_merge.php?cusnum=1256&lastname=Tester&initials=IB&street=Main&city=Dallas&state=TX&zipcode=78453&creditlimit=1000&chargecode=9&balancedue=00&creditdue=10
Any ideas about what I am missing here? The CAST's were needed to take
care of the way the parameters are received (I think) I was getting
different errors until I included them. I found a *close* example here:
https://www.ibm.com/docs/en/i/7.5?topic=statements-merge
As an Amazon Associate we earn from qualifying purchases.