-2

I've got a question that I've not been able to answer myself. I recently had an issue that was similar to the user's at Why does PDO::lastInsertId return 0?. Like that user, my problem was solved by removing a semicolon from the end of a PDO prepared statement.

I'm wondering if someone can explain to me why prepared statements like in the post above should not have semicolons to terminate them. I've used many PDO prepared statements with semicolons and never had problems until a few days ago. I can't find anything in the PDO documentation about semicolon usage, so I'm confused about whether there's an underlying principle about whether or not to use them. Is it always a bad idea to use semicolons in prepared statements? If so, why?

Thank you!

Community
  • 1
  • 1
J L
  • 358
  • 1
  • 15
  • 1
    It normally requires that you put some research effort into this as well. That means, you should create some snappy example demo code that demonstrates your issue. And you should post that code here, ideally with creating the test-database. http://sscce.org/ – hakre Jan 03 '13 at 22:47

2 Answers2

2

I have semicolons all over in my code.

They work fine.

The accepted answer in your linked question is wrong.

Naftali aka Neal
  • 138,754
  • 36
  • 231
  • 295
  • 1
    perhaps pdo then assumes it's a multiquery and thus provides a different resultset? I wouldn't just say the answer to the linked question is wrong without having tested it? Is there some evidence on this? – Luceos Jan 03 '13 at 22:45
  • @Luceos is there evidence that it is right at all? – Naftali aka Neal Jan 03 '13 at 22:46
  • no obviously not. But if the issue was resolved by removing the semi-colon what other than to call it the "solution"..? – Luceos Jan 03 '13 at 22:49
  • 1
    For evidence there is the existence and acceptance of the other question. But better than evidence would actually be to show your own tests. Otherwise it's all a bit too wishy washy. – hakre Jan 03 '13 at 22:49
  • @hakre well the other solution is only a sulution if there was some space after the semicolon, then the PDO code assumes that another query was run and the lastInsertId gets removed. – Naftali aka Neal Jan 03 '13 at 22:50
2

I'm wondering if someone can explain to me why prepared statements like in the post above should not have semicolons to terminate them.

That would be hard, because actually what you state is just plain wrong. There is no problem with prepared statements that are terminated by a semicolon.

So while you wonder, don't wonder any longer. There is nothing to explain about the semicolon. It's just a delimiter character. For example I can run the following prepare just fine:

$stmt = $conn->prepare('
INSERT INTO config (`option`) VALUES (:option);;;;;
');
$result = $stmt->execute([':option' => "Insert Option " . uniqid() ]);

Make $result being bool(true), the row is inserted. You haven't shared your code, so it's hard to tell what you do and what fails with you, but semicolons can be stacked even for fun as you can see.

I've used many PDO prepared statements with semicolons and never had problems until a few days ago.

That is a clear sign that the semicolon is not the problem actually. Otherwise all queries must have failed already from the very beginning.

I can't find anything in the PDO documentation about semicolon usage, so I'm confused about whether there's an underlying principle about whether or not to use them. Is it always a bad idea to use semicolons in prepared statements? If so, why?

Because it's part of the SQL. Consult the SQL documentation of the database server you're using. It does outline all the glory details of the use of the semicolon. It's just that this is not part of PDO itself, so it's not documented with PDO but with your database server.

hakre
  • 178,314
  • 47
  • 389
  • 754
  • And you could also add lots of semicolons before the "INSERT" keyword, without making the query fail. – Jocelyn Jan 04 '13 at 01:47