1

I wrote some code that built up a single query of multiple insert and update statements which was executed at the end of a page load. It used to work okay. I am writing similar, optimised code on my dev system (Ubuntu 14.04, PHP 5.5.3-Ubuntu), but I am no longer able to run multiple statements in one PDO query:

What I do

During a page render, I build up an SQL statement that would look a bit like:

insert into <table> (col1,col2,col3) VALUES (?,?,?);
update <table> set col1 = ?, col4 = ? where id = ?;
insert into <table> (col1,col2,col3) VALUES (?,?,?);
...

When the page has been rendered and I'm sure there are no problems, I execute the query using a wrapper for PDO. The important bits of the wrapper function are

$database = new PDO("mysql:host=<host>;dbname=<dbname>", <user>, <pwd>, 
               array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names 'utf8'"));
$database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $database->prepare($sql);
$stmt->execute($params);

For some reason, I am no longer able to execute this statement in one hit, instead, PDO only performs the first query, despite $stmt->queryString still holding the whole query. Can anyone help me with this problem.

Luke Madhanga
  • 6,871
  • 2
  • 43
  • 47
  • What does this give? `try { $stmt = $database->prepare($sql); $stmt->execute($params); } catch(PDOException $objException) { echo print_r($objException, true); }` Also see: http://stackoverflow.com/a/6461110/3000179 – ʰᵈˑ Oct 20 '14 at 14:08
  • @ʰᵈˑ no exception is thrown. It only runs the first statement, which in my example would be the `insert in ...` – Luke Madhanga Oct 20 '14 at 14:11
  • 2
    *"I build up an SQL statement that would look a bit like:"* - Why not show us what you're actually using. This is guesswork, at best. – Funk Forty Niner Oct 20 '14 at 14:12
  • possible duplicate of [PDO support for multiple queries (PDO\_MYSQL, PDO\_MYSQLND)](http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) – Bud Damyanov Oct 20 '14 at 14:15
  • @bodi0 I analysed that before writing, that one is less specific about what sql statements were given to PDO. – Luke Madhanga Oct 20 '14 at 14:17
  • So basically, you concatenated 3 queries and created a prepared statement out of it. Naturally, that's absolutely NOT how things work. You are supposed to create 3 prepared statements. One for each of the queries. Then you bind different parameters and reuse prepared statements. What comes as counter-intuitive is that it's actually faster in many cases. And not only is it faster to work like that, it's also easier. – N.B. Oct 20 '14 at 14:33
  • @N.B. Notice the ellipses at the end. The test I just ran had 50 queries. One by one in that instance would be longer, no? – Luke Madhanga Oct 20 '14 at 14:36
  • Thing is, what I'm assuming (because I don't have sufficient information) is that you're trying to optimize insert/update by concatenating stuff into 1 query. For some reason you're convinced it's faster (because 1 query = faster than 50 queries). However, that is not how prepared statements work and the optimization you're doing is not good. Can you include all of your code so we can give you a proper advice on how to approach the problem, rather than beating around the bush with what you think is the problem? – N.B. Oct 20 '14 at 14:40
  • @N.B. Yup, I'm going to break them up, check the answer – Luke Madhanga Oct 20 '14 at 14:49
  • ensure emulated prepared statements are set to true, otherwise multirowsets won't work –  May 22 '20 at 17:38

1 Answers1

-1

Found the problem: PDO fails silently if one of the queries throws an exception. In my case, the first query was okay, but the second was throwing an integrity constraint failure error, so it looked like only the first query was being run.

A wise man just told me: Don't shoot the messenger, break the queries up

Luke Madhanga
  • 6,871
  • 2
  • 43
  • 47