Re: [IBMiOSS] Passing parameters to a PHP script that contains SQL -- OPENSOURCE

NVM on the PHP thing. Looks like you included it 😊

Regards,
Richard Schoen
Web: http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx

-----Original Message-----
From: Richard Schoen
Sent: Monday, September 11, 2023 12:52 PM
To: opensource@xxxxxxxxxxxxxxxxxx
Subject: RE: Passing parameters to a PHP script that contains SQL

Not sure if this is relevant, but I generally use the ODBC driver with PHP whether on Windows, Linux or IBMi.

I'm not a PHP expert either, but just throwing that out.

Could it perhaps be your PHP jobs are running under a restricted user like QMHTTP/QTMHHTP1 ? That was always an issue with RPG stuff.

Are you saying it works if you call from ACS instead of Postman ?

What about just calling from the browser ? Does it work then ?

Maybe aren't getting the query parms correctly from the HTTP call ?

Hard to say without seeing any of the PHP.

Regards,
Richard Schoen
Web: http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx

-----Original Message-----
----------------------------------------------------------------------

message: 1
date: Sun, 10 Sep 2023 19:17:07 -0500
from: Pete Helgren <pete@xxxxxxxxxx>
subject: [IBMiOSS] Passing parameters to a PHP script that contains
SQL

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

--
Pete Helgren
www.petesworkshop.com
GIAC Secure Software Programmer-Java
GIAC Cloud Penetration Tester
AWS Certified Cloud Practitioner
Microsoft Certified: Azure Fundamentals