PHP DB2 Get Full Query after db2_execute()

Question

We are using db2_prepare and db2_execute to prepare queries in a generic function. I am trying to have a debug method to get the full prepared query after the '?' values have been replaced by the db2_execute function.

Is there an efficient way of doing this besides manually replacing each '?' with the parameters I am passing in? i.e. is there a flag that can be set for db2_execute?

Example:

$params = array('xyz','123');
$query = "SELECT * FROM foo WHERE bar = ? AND baz = ?";
$sqlprepared = db2_prepare($CONNECTION, $query);
$sqlresults = db2_execute($sqlprepared,$params);

I would like the $sqlresults to contain the full prepared query:

"SELECT * FROM foo WHERE bar = 'xyz' AND baz = '123'";

I have looked through the docs and do not see any obvious way to accomplish this, but I imagine there must be a way.

Thank you!

mustaccio · Accepted Answer · 2015-05-28 14:15:05Z

1

db2_execute() does not replace parameter markers with values. Parameters are sent to the server and bound to the prepared statement there.

The CLI trace, which can be enabled on the client as explained here, will contain the actual parameter values. Keep in mind that the trace seriously affects application performance.

answered May 28, 2015 at 14:15

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

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

1 Comment

Bryan Zwicker Over a year ago

Understood. I did not know that they were sent to the DB and bound on that side, I was assuming PHP handled this in the background. We were only going to be using this as a debug option on the query and having it not run by default. I am going to add an answer of what I came up with to handle this through PHP but thank you for your input!

Bryan Zwicker · Accepted Answer · 2015-05-28 14:30:58Z

0

I ended up writing a loop to replace the '?' parameters with a simple preg_replace after and outputting the query in my 'debug' array key:

$debugquery = $query;
foreach($params as $param) {
  $debugquery = preg_replace('/\?/',"'".$param."'",$debugquery,1);
}
return $debugquery;

This handled what I needed to do (to print the finalized query for debugging purposes). This should not be run in Production due to performance impacts but is useful to look at the actual query the server is trying to perform (if you are getting unexpected results).

answered May 28, 2015 at 14:30

Bryan Zwicker

6521 gold badge7 silver badges25 bronze badges

Collectives™ on Stack Overflow

PHP DB2 Get Full Query after db2_execute()

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related