Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Next issue: Can't execute multiple statements?

0 views
Skip to first unread message

TheMechE

unread,
Apr 30, 2003, 8:43:44 PM4/30/03
to
This is hard to believe....

Does mySQL really not accept a SQL string with multiple Statements separated
by semi-colons? Or is that a driver specific problem?
I'm having some serious concern here about these performance liabilities....

Any pointers?
See my earlier note about the Insert Into myTable SELECT blah blah from
myTable where index=k; -- fails, discussion.

-Andy


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=my...@freebsd.csie.nctu.edu.tw

Jennifer Goodie

unread,
Apr 30, 2003, 8:59:58 PM4/30/03
to
It works at the commandline. It must be a driver specific problem. Or
maybe it is a syntax issue and one of the queries is failing? Have you
tried it at the commandline to see what is happening? I'm too lazy to look
for your last thread to see what what platform you are running on and what
language you are using.

TheMechE

unread,
Apr 30, 2003, 9:08:55 PM4/30/03
to

Scenario 1.

I build String. I pass to execute() Here is the results...

All from my log writer
20030430.145301 Delete from Licenses where LicenseID = 269;Delete From
LicenseRules Where LicenseID = 269;Delete from Categories where categoryID
IN (-1) ;Delete from LicenseSlaves where MasterID = 269 OR SlaveID = 269;

20030430.145301 java.sql.SQLException: Syntax error or access violation: You
have an error in your SQL syntax. Check the manual that corresponds to your
MySQL server version for the right syntax to use near ';Delete From
LicenseRules Where LicenseID = 269;Delete from Cat

When I pass the same string to my StringTokenizer ( and split on ";") to do
the update and
perform each step one by one it works.

Mark Matthews

unread,
Apr 30, 2003, 10:39:28 PM4/30/03
to
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jennifer Goodie wrote:

> It works at the commandline. It must be a driver specific problem. Or
> maybe it is a syntax issue and one of the queries is failing? Have you
> tried it at the commandline to see what is happening? I'm too lazy to
look
> for your last thread to see what what platform you are running on and what
> language you are using.

';' has special significance for the parser inside mysql command-line
client, it means 'end of statement'...The command-line client takes care
of sending each query separated by ';' to the server, one-by-one.

>>-----Original Message-----
>>From: TheMechE [mailto:TheM...@microneil.com]
>>Sent: Wednesday, April 30, 2003 11:51 AM
>>To: my...@lists.mysql.com
>>Subject: Next issue: Can't execute multiple statements?
>>
>>
>>This is hard to believe....
>>
>>Does mySQL really not accept a SQL string with multiple
>>Statements separated
>>by semi-colons? Or is that a driver specific problem?
>>I'm having some serious concern here about these performance
>>liabilities....

MySQL-4.1 will support this, when a connection option is passed in.
However, you have to realize that to the un-careful programmer (which is
many, when under time/budget/experience constraints), the ';' is more of
a security liability than a performance liability due to the ability to
create SQL Injection attacks when a database supports multiple
statements via a delimiter in standard SQL. If you're going to use
multiple queries, you should also be _very_ aware of how to use them
_safely_. See http://www.owasp.org/ for more information about using
this feature safely.

-Mark
- --
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <ma...@mysql.com>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+sHTktvXNTca6JD8RAokZAJsGW3wMqv4O28ThfY5cNKaabuCPugCgn2pc
YezZS9TtJD+3MhPCuyECfmM=
=9gPH
-----END PGP SIGNATURE-----

Dan Nelson

unread,
May 1, 2003, 12:53:47 AM5/1/03
to
In the last episode (Apr 30), TheMechE said:
> Scenario 1.
>
> I build String. I pass to execute() Here is the results...
>
> All from my log writer
> 20030430.145301 Delete from Licenses where LicenseID = 269;Delete From
> LicenseRules Where LicenseID = 269;Delete from Categories where categoryID
> IN (-1) ;Delete from LicenseSlaves where MasterID = 269 OR SlaveID = 269;

Send four separate queries (or upgrade to mysql 4.1). Even with 4.1, I
recommend sending four queries, because that way you can error-check
each statement.

--
Dan Nelson
dne...@allantgroup.com

0 new messages