Becoming a FAQ: Executing multiple sql statements in a single network round trip

[Matthew T. Adams]

Hi all,

I've seen this question posed a couple of times now,
and I just wanted to get someone from MySQL to advise.

If I separate separate sql commands with semicolons
and submit to the server for execution, the driver
bombs with a syntax error message, suggesting that you
shouldn't be doing this.  Fair enough.

What is the suggested way to perform multiple sql
statements in only one network round trip to the
database?  Note that I am NOT asking how to reuse the
same Connection object across multiple statements --
that's easy, just reuse the connection object.  I want
to only make one round trip to the database to do my
stuff.

I saw someone from MySQL recommended a connection
option to allow multiple semicolon-separated
statements, but didn't say which option to set, or
what its value should be, and I couldn't find it after
RTFMing, either.

I'm half expecting someone to mention using batching,
which I haven't yet used.

Can someone from MySQL please authoritatively advise
on this and add to the FAQ?

Thanks a bunch in advance,
Matthew


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com