473,569 Members | 2,721 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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


Oct 16 '05 #1
4 16743
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

Oct 16 '05 #2
Hi,

is this what you're looking for?

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

Cheers,
Markus
Oct 17 '05 #3
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.n et...
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

Oct 17 '05 #4
"DG" <dj*****@ukonli ne.co.uk> wrote in message
news:dj******** ***********@new s.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 "dbSqlPassThrou gh". 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
Oct 18 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2194
by: Shmuel | last post by:
Is it possible to query multiple statements at once? Like: $query = "set @p := 1; select @p + 1"; $results = mysql_query($query); I'm thinking of PHP4. There is in mysqli the prepare statement, but I can't use that. Shmuel.
2
3119
by: Annie D via AccessMonster.com | last post by:
Hi, Is it possible to use multiple statements in SQL?? (I’ve never used it before) : I have one query that i'm working with, The statements I want to use are as below, they all work individually, but not together, Access help is as much use as a chocolate fireguard in this case! any suggestions or explainations would be gratefully...
4
2635
by: | last post by:
Hello, I am attempting to convert multiple .wav files to the .flac format via a batch script. I know that this can be done with numerous software implementations automatically for me; however, i am interested to understand how to create a script to perform my purpose and since this function is useful, what the hey! I am rather new to batch files...
7
2718
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both of the queries work fine when I use them to query the MySQL server directly. My guess is that the MySQL extension only expects a single resource...
1
2489
by: arthy | last post by:
Hi, Is it possible to execute multiple statements on to the database using a single dbconnection object.what is the drawback in using .If not possible ,then how can the execution of multiple statements using a single object be done. thanks in advance, arthy
4
8858
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect the tables together with various queries using a unique id. My task is to generate reports from these tables dynamically. The user will select the...
1
3612
by: bharathi228 | last post by:
How to write two or more select statements in a single query here my requirement is SELECT dbo.SYS_PARAMS.PARAMETER_NAME, dbo.SYS_PARAMS.PARAMETER_UNITS, SYSTEM2.dbo.CALIBRATION.CAL_TYPE, SYSTEM2.dbo.CALIBRATION.CAL_TIMESTAMP, SYSTEM2.dbo.CALIBRATION.MEASURED_VAL, SYSTEM2.dbo.CALIBRATION.EXPECTED_VAL FROM SYSTEM2.dbo.CALIBRATION INNER...
0
2386
by: harsha318 | last post by:
Hi I need to have a single query and which can have multiple statements For eg: string str = string.Empty; str = "select * from Customers;Select * from Orders"; iDB2Connection iDB2con = new iDB2Connection(connStr); iDB2Command iDBCmd = iDB2con.CreateCommand();
0
8119
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7668
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7964
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5509
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3637
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2111
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1209
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
936
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.