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

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Matthew T. Adams wrote:

> 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

MySQL-4.1 (which is still in ALPHA) will support this (this
functionality is currently not completed), but it won't be enabled in
the driver by default due to the security issues it causes. You will be
able to pass a flag to the driver in your URL to enable it.

Watch the docs for Connector/J 3.1 to see when this functionality is
available....

Prior to MySQL-4.1, this feature wasn't available.

However, if you want to do multiple INSERTs, you can already do this
(for quite some time, now) in one statement via the syntax:

INSERT INTO foo VALUES (...), (...), (...)

Which is very fast, because it also then does index updates in a bulk
fashion.
	-Mark

- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: [phone]
www.mysql.com

Are you MySQL Certified?
http://www.mysql.com/certification/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQE/e0U1tvXNTca6JD8RAnpQAJ9aeJs3k/JicA4dzu/TWI/gJoqhGQCffMfR
7BS39eVvgKcCiTyrNhiijAc=
=N9hL
-----END PGP SIGNATURE-----