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

[Matthew T. Adams]

Fair enough.  However, the question remains:

How do I do stuff (inserts, creates, updates) all in
one round trip?

Does Connector/J support batching?

--matthew

--- Mark Matthews <mark@stripped> wrote:
> -----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-----
> 
> 
> -- 
> MySQL Java Mailing List
> For list archives: http://lists.mysql.com/java
> To unsubscribe:   
>
http://lists.mysql.com/java?unsub=1
> 


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