Sequential SQL command execution
lazarus / 16-Databases

----------------------------------------------------------------------
Ronan · 2011-08-08 06:34

hi all,

I wonder is it legal to make that following SQL command execution?
Inserting into loop may seem very weird, but actually in my code I
have to do this and first SQL command is not simple as that, much more
complicated.
Code: [Select]for i := 1 to 30
   ZQuery2.SQL.Add('select (i)*Column_1,(i)*Column_2  from CTE ' );
   ZQuery2.Close;
   ZQuery2.Open;

   ZQuery2.SQL.CLear;
   ZQuery2.SQL.Add('insert into New_Table select * from Old_Table');
   ZQuery2.ExecSQL;
end ;           

Basically my goal is to perform some querying on Old_table, and copy
the content to the new table , so to speak the old table content
should remain untact.

Actually the above code compiles and runs, but produced results are
wrong and DbGrid somehow loses connection to database, stays as if
unconnected to Datasource, ZQuery etc..

Any Comments will be appreciated.

----------------------------------------------------------------------
Lacak2 · 2011-08-08 07:30

I do not understand what do you want realy do.

In your example in first SQL you select some data, but nothing is done
with this data. (may be, you select data into some temporary table on
sql server)
In second SQL you perform sql insert ... seems, that does not depend
on first SQL. (but may be, that Old_table is populated in first
select?)

Optimization depends of capability of your SQL database.
(stored procedures, insert into ... select, etc.)

----------------------------------------------------------------------
Ronan · 2011-08-08 07:49

thanks for helping,

Quotefirst SQL command is not simple as that, much more complicated. 
I provide it for schematic simplification that the query is loop value
related. 1st command is more complicated. I don't used temporary
tables

QuoteIn second SQL you perform sql insert ... seems, that does not
depend on first SQL.
Absolutely no relation just to copy from one (old_table) to the other
(new_table).

This is what I want to achieve :QuoteBasically my goal is to perform
some querying on Old_table, and copy the content to the new table , so
to speak the old table content should remain untact.

Actually the above code compiles and runs, but produced results are
wrong and DbGrid somehow loses connection to database, stays as if
unconnected to Datasource, ZQuery etc..

----------------------------------------------------------------------
Arbee · 2011-08-08 08:32

If the intention is to use the data from the select to insert into
another table, why not do that in one single insert statement?

It is the INSERT version that works as:

INSERT INTO new_table SELECT fields FROM old_table WHERE condition;

You are already more or less doing that as I notice.  The two can't be
combined?

And what would be the problem in having two separate Query objects? 
One for the SELECT and one for the INSERT.  They can share the
connection.

----------------------------------------------------------------------
Ronan · 2011-08-08 10:23

In the code I've done exactly the same thing,
Quote   ZQuery2.SQL.CLear;
   ZQuery2.SQL.Add('insert into New_Table select * from Old_Table');
   ZQuery2.ExecSQL;
As far as I can gather, any query executions are not valid until you
really submit it to the database i.e. Post, commit, CommitRetainig
etc...
In my case I perform 1st query with succesfull resulting dataset but
before commiting it to database I run again antoher query which
overwrites the 1st one.
Is that cast correct?

----------------------------------------------------------------------
BigChimp · 2011-08-08 10:50

You're right, the SQL doesn't get executed until you do Commit or
Commitretaining. Might be different with a SELECT query, haven't
tested.
Post is similar: it commits/saves data, but is meant for datasets.
So it might help to insert a Commit and restart the transaction
afterward.

----------------------------------------------------------------------
Lacak2 · 2011-08-08 11:30

Quote from: Ronan on August 08, 2011, 12:23:55 pmIn my case I perform
1st query with succesfull resulting dataset but before commiting it to
database I run again antoher query which overwrites the 1st one.
Is that cast correct?
IMI there is no problem.
You must distinguish what happens localy and what on SQL server side.
When you call  ExecSQL then statement is performed on SQL server in
context of current transaction.
When you later reasign localy another SQL statement and execute it
again using ExecSQL then next statement is performed on SQL server in
same transaction as first.
Then you can commit it as one atomic block.