Executing multiple sql statements
lazarus / 16-Databases

----------------------------------------------------------------------
T90 · 2011-11-07 20:29

I have done some search, but I did not find answer to my question. I
have several sql statements and as far as I found a TSQLQuery can
execute just one at a time. How can I execute all the statements at
once ?

----------------------------------------------------------------------
Arbee · 2011-11-08 07:11

Don't know about SQLdb, but in ZEOS there's the TZUpdateSQL object,
which allows multi statements.  Obviously such a construct only makes
sense for queries that update the DB, not for SELECTs.

I never used it myself, but as far as I can see you need to link such
a TZUpdateSQL object with the standard TZQuery (via the "updateObject"
property) and call the latter's execSQL.

In SQLdb (I don't have in-depth experience with that one) I see the
TSQLScript class.  No idea if that even comes close, but you may want
to investigate that.

----------------------------------------------------------------------
ludob · 2011-11-08 08:45

Zeos has also a TZSQLProcessor that is usefull for running SQL
scripts. Set TZSQLProcessor.Script or do a TZSQLProcessor.LoadFromFile
and then TZSQLProcessor.Execute. Also useful for complex multi-table
updates since Params are supported.

sqldb has TSQLScript with less features. Set TSQLScript.Script and
call TSQLScript.Execute;

----------------------------------------------------------------------
T90 · 2011-11-08 08:55

I thought I found a solution, but I was wrong. I want to do the
following:

I have several TEdits that holds some information. However I want to
update only the TEdits, whose information has been changed. This is
why I use Query.SQL.Add and I add just the info from the boxes with
changed info. Usually the Query.SQL.Text consists of 2-3 queries. I
made a TStringList var names sqlstatements and set its delimiter to
';' since all queries end with ';'. When I use the sqlstatements[0]
and so on the queries are displayed correct with parameters. I forgot
to add that I use params. In phpmyadmin all is executed correctly, but
unfortunately Lazarus does not execute it. Is there a bug or simply
Lazarus can not execute queries in this way ?

----------------------------------------------------------------------
T90 · 2011-11-08 09:02

I will take a look at the ZOESLib as you, guys mentioned.

And I've got another question: Is it enough to remove the ';' and '
from a TEdit in order to avoid SQL Injection ?

----------------------------------------------------------------------
ludob · 2011-11-08 09:20

QuoteIs it enough to remove the ';' and ' from a TEdit in order to
avoid SQL InjectionIf you are using parameters you don't have to do
anything. ftString fields are or bound (sqlite,... ) or quoted and
escaped (mysql,...).

----------------------------------------------------------------------
garlar27 · 2011-11-08 13:20

I never had problems executing many SQL statements as long as I end
each statement with  a ";". For instance:
Code: [Select]INSERT INTO "EMPLOYEE" ("ID", "NAME") VALUES (1, 'A');
INSERT INTO "EMPLOYEE" ("ID", "NAME") VALUES (2, 'B');
INSERT INTO "EMPLOYEE" ("ID", "NAME") VALUES (3, 'C');