Multi line SQL

[Jonah86]

I'm using Delphi 7 connecting to a MySQL database via BDE (I know, stone age).

Well, I'm trying to populate the Tquery object with more than one SQL statement and it's giving me errors. I thought maybe I needed to include the semicolon, but no luck. It works fine with only one line.

The lines I'm adding are inserts, I'm using datamodule.query.sql.add() in a buttonclick routine and then running the whole query at the end. I'm sure it's possible to do with a loop, but I'm being lazy.

So is there any way to run multiple inserts in one Tquery object?

 

[svanels]

BDE stone age? Then I am also a Neanderthal man

What do you want to do? insert a bunch of records from one table into another?

From the help:

procedure ExecSQL; (Tquery)

Description

Call ExecSQL to execute the SQL statement currently assigned to the SQL property. Use ExecSQL to execute queries that do not return a cursor to data (such as INSERT, UPDATE, DELETE, and CREATE TABLE).

example of two sql commands:

insert into table_1 ( txcode, txroute, txsequence)
   select tcode, route, sequence from table_2;

update table_1
   set txinsp_date = :START_DATE;

The SQL sintaxe also need the ; between different commands

Steven

 

[towerbase]

I don't think that it is possible to have multiple SQL statements separated by semi colons within the BDE.

Various programs, such as mysql and the MySQL Control Center, will accept multiple statements but within these programs I think that they split the multiple statements up and send them one at a time to the MySQL server.

Instead of appending a semi colon to your SQL statement simply call ExecSQL, clear the SQL stringlist and repeat.

For example:-

Query.SQL.Clear;
Query.SQL.Add ( 'INSERT INTO table ( fld1, fld2 ) VALUES ( 1, 2 )';
Query.ExecSQL;

Andrew
Hampshire, UK

 

[svanels]

Andrew you are right, I confused it with a stored procedure.

insert into table_1 ( txcode, txroute, txsequence)
   select tcode, route, sequence from table_2

This can be executed because it is seen as one sql command.
whith the select/where combination you could filter out the data you want to transfer without a loop. This will work for data already contained in the database.

Just curious, if you are inserting data, the lazy approach tquery - dbNavigator - dbGrid doesn't work?
Don't forget to put request live = true

Regards

Steven