Error doing db2 insert with PHP parameterized query

Question

I'm trying to run a db2 parameterized query in PHP and upon the execution of the insert, I get the error: Invalid parameter number., SQL state S1002 in SQLDescribeParameter

This is my script:

        $getItems = "
        SELECT 
            ID,
            EXPIRATION_TIMESTAMP
        FROM table1
    ";

    $stmt = odbc_exec($DB2connDEV, $getItems);

    $prepInsert = odbc_prepare($DB2connPROD, "INSERT INTO table2 (originalID, expiration_timestamp) VALUES(?,?)");

    while($gettingDevItems = odbc_fetch_array($stmt)){

        $rows[] = $gettingDevItems;
    }

    foreach($rows as $row){

        $originalID = $row['ID'];
        $expiration_timestamp = $row['EXPIRATION_TIMESTAMP'];


        $getIdentity = "SELECT IDENTITY_VAL_LOCAL() AS LASTID FROM SYSIBM.SYSDUMMY1";

        $insertTable = odbc_execute($prepInsert, array($originalID, $expiration_timestamp));//error at this line
        $insertTable = odbc_exec($DB2connPROD, $getIdentity);
        $row = odbc_fetch_array($stmt);
        $ret = $row['LASTID'];
    }

When I do a var_dump on the array of params, I get this:

array(2) {
  [0]=>string(1) "2"
  [1]=>string(26) "2019-10-03 00:00:00.000000"
}

What am I doing wrong here? Even if I take one value out to only insert one or the other I still get it, so It's not specific to one column.

You can give a try and put the prepare inside the foreach. Not sure if odbc driver or something in your code will mess things here. If this is your code you could change it with insert into table 2 select id, timestamp from table 1 and make it work in 1 shot. — Felippe Duarte
– Felippe Duarte, Commented Feb 17, 2020 at 20:10
@FelippeDuarte I'll move it inside the loop and see. I can't do the select/insert though becuase I'm going from one table on an IP/Server and into another — Geoff_S
– Geoff_S, Commented Feb 17, 2020 at 20:22
@FelippeDuarte wow that did fix it! Thanks so much, if you want to make that into an answer I'll accept it — Geoff_S
– Geoff_S, Commented Feb 17, 2020 at 20:25
No problem. Still, I don't know what's wrong, but at least it's working. — Felippe Duarte
– Felippe Duarte, Commented Feb 17, 2020 at 20:31

Felippe Duarte · Accepted Answer · 2020-02-17 20:30:20Z

2

Maybe odbc can't support reuse of prepared statement, or your driver, or other part of your code, or another thing. Anyway, move the prepared statement inside your foreach loop to make sure you will rebuild it:

foreach($rows as $row){
   $prepInsert = odbc_prepare($DB2connPROD, "INSERT INTO table2 (originalID, expiration_timestamp) VALUES(?,?)");
...

answered Feb 17, 2020 at 20:30

Felippe Duarte

15.2k2 gold badges27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error doing db2 insert with PHP parameterized query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related