MySQL multiple prepared statements with same connection

Question

What exactly are the rules for setting up multiple prepared statements simultaneously (with a single DB connection)? I often have loops that include multiple queries; it would be nice to set up 3 prepared statements, cycle through them, then close them all.

What I've found is that sometimes they work together, sometimes they don't. For instance I've been able to set up 3 simultaneous SELECT statements. But I think that having a prepared SELECT and an INSERT on the same table, running in a loop, disconnects me from the database (or at least it doesn't work as it should). However, when I change the prepared statements to regular queries, I can run the same queries in the same order, just fine.

I'm pretty much a novice and would appreciate some clarification on how this works!

Answer 1

Sounds like you have a good candidate for a stored procedure. When you need to insert data into one table based on the results from another select query, you can usually do that inside a stored procedure slightly quicker than with programming language code.

As for why your current system doesn't work; I think you are probably seeing the 'Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order'

This happens with prepared statements because they actually return two result sets. So when you retrieve a row from your select query and then try to do an insert there is actually another resultset waiting in the pipe. You need to retrieve this second result before doing your insert.

So to sum it up, you are probably bettter off using a regular query or using a stored procedure.