Multiple statements in single query batch

[DG]

Hi,

Can anyone advise how to execute multiple statements in a single query
batch. For example-

update customers set customer_name = 'Smith' where customer_name =
'Smyth';
select * from customers;

I can execute each statement individually but get the 'you have an error in
you SQL syntax' when trying to execute them together.
Ultimately I'm looking to build multi-statement queries (some may contain
transactions) that do lots of work and then return a result for the client
to use :-

INSERT customers
(customer_name)
VALUES
(ucase('test3'));

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE c.customer_name = ucase('test3');

Can anyone tell me what I'm missing?

Thanks,

David

[Thomas Bartkus]

On Sun, 16 Oct 2005 13:36:58 +0100, DG wrote:

Hi,

Can anyone advise how to execute multiple statements in a single query
batch. For example-

update customers set customer_name = 'Smith' where customer_name =
'Smyth';
select * from customers;

I can execute each statement individually but get the 'you have an error
in you SQL syntax' when trying to execute them together. Ultimately I'm
looking to build multi-statement queries (some may contain transactions)
that do lots of work and then return a result for the client to use :-

INSERT customers
(customer_name)
VALUES
(ucase('test3'));

SELECT c.customer_id, c.customer_name FROM customers c
WHERE c.customer_name = ucase('test3');

Can anyone tell me what I'm missing?

How are you delivering these query statements to MySQL?

The standard "mysql" application that comes packaged with the database can
easily manage multi-statment queries. Just so long as they are legal
statements properly terminated with a semicolon.

So - what is it that you are using to deliver your multi-statement queries?
Thomas Bartkus

[Markus Popp]

Hi,

is this what you're looking for?

http://www.php.net/manual/en/functio...ulti-query.php

Cheers,
Markus

[DG]

I'm using .Net front end, essentially delivering a string of text then
'filling' the results into the clients memory. For the examples below I'm
using the Query Browser provided with MySQL to test queries first. The
problems occur from both clients.

I'm trying to disconnect the GUI from the db so I can change either in
future without major re-development costs. e.g. At work I use Sybase
extensively and build strings of SQL that are platform independent. This
means we have MS Access, .Net and Java GUIs connected to the same db with no
development overheads and allowing different teams to use the same db in
their own way. Note a lot of the work we do is in stored procs which
simplifies this further.

Any advice?

Thanks
"Thomas Bartkus" <th***********@comcast.net> wrote in message
news:pa****************************@comcast.net...

On Sun, 16 Oct 2005 13:36:58 +0100, DG wrote:

Hi,

Can anyone advise how to execute multiple statements in a single query
batch. For example-

update customers set customer_name = 'Smith' where customer_name =
'Smyth';
select * from customers;

I can execute each statement individually but get the 'you have an error
in you SQL syntax' when trying to execute them together. Ultimately I'm
looking to build multi-statement queries (some may contain transactions)
that do lots of work and then return a result for the client to use :-

INSERT customers
(customer_name)
VALUES
(ucase('test3'));

SELECT c.customer_id, c.customer_name FROM customers c
WHERE c.customer_name = ucase('test3');

Can anyone tell me what I'm missing?

How are you delivering these query statements to MySQL?

The standard "mysql" application that comes packaged with the database can
easily manage multi-statment queries. Just so long as they are legal
statements properly terminated with a semicolon.

So - what is it that you are using to deliver your multi-statement
queries?
Thomas Bartkus

[Thomas Bartkus]

"DG" <dj*****@ukonline.co.uk> wrote in message
news:dj*******************@news.demon.co.uk...

I'm using .Net front end, essentially delivering a string of text then
'filling' the results into the clients memory. For the examples below I'm
using the Query Browser provided with MySQL to test queries first. The
problems occur from both clients. Query Browser provided with MySQL to test queries first. The
problems occur from both clients.
What query browser provided by MySQL?

The one I am familiar with is MySQLCC (MySQL Control Center) which has no
problem with multiple statements delivered via the query Window. And I know
it to work on both Linux and Windows versions.

The .Net front end means you are using Microsofts ADODB library - Yes? No?
Or have the come up with a working ADO.NET?
Here you may be having a problem since AFAIK, the .sql property of a
connection object doesn't work with multiple statements. If this is still
the case, it is a problem with the ADODB library, not MySQL.
This means we have MS Access, .Net and Java GUIs connected to the same db
with no development overheads
What about the Microsoft imposed ADODB overhead?

All the MySQL server needs is a stream of legitimate sql statements
terminated by semi-colons. I may not be familiar with the latest & greatest
ADODB incarnation - But in the past both DAO and ADODB were notorious for
the inability to manage multiple statement queries - even as passthroughs to
MySQL. Just try writing an Access query with multiple statements! If I
opened an ADODB connection object to a MySQL server, I would have to
cn.execute one sql statement at a time.

For example, using Access, you always had to create separate queries
"Query1, Query2. .... QueryN" and execute them sequentially even if they
were "dbSqlPassThrough". This was (and perhaps still is!) a limitation in
ADODB. Access as well as the underlying libraries DAO and ADO have no notion
of multiple statement queries and don't know what to do with them.

If you write directly to the MySQL ODBC driver, bypassing the MS libraries,
you would find no such limitation! The ODBC driver itself will execute each
statement in turn and throw query results back at you in the order in which
they are produced.
I'm trying to disconnect the GUI from the db so I can change either in
future without major re-development costs. e.g. At work I use Sybase
extensively and build strings of SQL that are platform independent. This
means we have MS Access, .Net and Java GUIs connected to the same db with no development overheads and allowing different teams to use the same db in
their own way. Note a lot of the work we do is in stored procs which
simplifies this further.

That generic SQL ("SQL that are platform independant") sounds a bit of a
tall order. Good luck with that!

My approach to bringing many disparate db platforms to the Microsoft world
is to use MS Access and linked tables. This is a forte of MS Access.
Whatever Oracle, Sybase, MySQL, or whatever might look like, they all look
like an Access table and can coexeist together given suitable ODBC drivers.
Doing it this way, everything looks like an Access database to Microsoft
developement tools. You can then use Microsoft's peculiar flavor of SQL to
talk to everything. When in Rome ....!

Not sure if this helps but it is the little I know!
Thomas Bartkus