× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.