Some Help with SQLQuery Interface
lazarus / 16-Databases

----------------------------------------------------------------------
jack · 2012-02-17 04:03

I want to issue an SQL Statement to update a single table in the
database.
The TSQLQuery object does not seem to be designed to do this. 
Is there some other widget to just issue SQL?  I am using Firebird
DBMS.
Would I do better to write inline code to do this if so where might I
find an example to study?
The statement looks like this: (PR_EMP_ID and PR_DATE make up the
Primary Key)
========================================
UPDATE OR INSERT INTO PAY_RECORDS (
PR_EMP_ID,
PR_DATE,
PR_PGP_NAME,
PR_EMP_NAME,
PR_HR_JOB1_NAME,
PR_HR_JOB1_HOURS,
PR_HR_JOB1_RATE,
PR_HR_JOB1_EARNED,
PR_HR_JOB2_NAME,
PR_HR_JOB2_HOURS,
PR_HR_JOB2_RATE,
PR_HR_JOB2_EARNED,
PR_PW_JOB1_NAME,
PR_PW_JOB1_HOURS,
PR_PW_JOB1_RATE,
PR_PW_JOB1_PIECES,
PR_PW_JOB1_EARNED,
PR_PW_JOB2_NAME,
PR_PW_JOB2_HOURS,
PR_PW_JOB2_RATE,
PR_PW_JOB2_PIECES,
PR_PW_JOB2_EARNED,
PR_PW_JOB3_NAME,
PR_PW_JOB3_HOURS,
PR_PW_JOB3_RATE,
PR_PW_JOB3_PIECES,
PR_PW_JOB3_EARNED,
PR_PW_JOB4_NAME,
PR_PW_JOB4_HOURS,
PR_PW_JOB4_RATE,
PR_PW_JOB4_PIECES,
PR_PW_JOB4_EARNED,
PR_GROSS
) VALUES (
:prempid,
:prdate,
:prpgpname,
:prempname,
:prhrjob1name,
:prhrjob1hours,
:prhrjob1rate,
:prhrjob1earned,
:prhrjob2name,
:prhrjob2hours,
:prhrjob2rate,
:prhrjob2earned,
:prpwjob1name,
:prpwjob1hours,
:prpwjob1rate,
:prpwjob1pieces,
:prpwjob1earned,
:prpwjob2name,
:prpwjob2hours,
:prpwjob2rate,
:prpwjob2pieces,
:prpwjob2earned,
:prpwjob3name,
:prpwjob3hours,
:prpwjob3rate,
:prpwjob3pieces,
:prpwjob3earned,
:prpwjob4name,
:prpwjob4hours,
:prpwjob4rate,
:prpwjob4pieces,
:prpwjob4earned,
:prgross
)

Thanks for your help.

----------------------------------------------------------------------
Lacak2 · 2012-02-17 06:18

You can use TSQLQuery.
1. Set Q.SQL.Tex:=<your sql statement>
2. Set parameters Q.ParamByName('xxx').AsXXX:=<param value>
3. Execute by Q.ExecSQL;

----------------------------------------------------------------------
jack · 2012-02-17 11:22

Thanks, I'll give it a go.

----------------------------------------------------------------------
jack · 2012-02-17 13:58

I have created my Query but it seems to be a NOP.
Nothing happens, the database does not update and I get no error.

Two Questions:

Since I issued this query without a transaction, it seems that I
should not have to 'commit' is this correct?

Is there a way to view the fully qualified SQL statement (all params
values inserted) that is about to be executed?  Q.SQL.Text; simply
yields the params list as coded.

----------------------------------------------------------------------
Lacak2 · 2012-02-17 14:07

Quote from: jack on February 17, 2012, 02:58:29 pmI have created my
Query but it seems to be a NOP.
Nothing happens, the database does not update and I get no error.

Two Questions:

Since I issued this query without a transaction, it seems that I
should not have to 'commit' is this correct?
No, you must Commit always.
I suppose, that you have (on form or created in program)
TSQLTransaction object.

Quote from: jack on February 17, 2012, 02:58:29 pmIs there a way to
view the fully qualified SQL statement (all params values inserted)
that is about to be executed?  Q.SQL.Text; simply yields the params
list as coded.
No. During preparation of query, params in style ":param1" are
replaced by "?" and in this form is statement send to Firebird backend
for preparation. Parameter values are send separatelly in their own
structure in execution phase of statement.

----------------------------------------------------------------------
jack · 2012-02-17 14:22

Thanks, The commit did the trick.

For posterity,   If the Query is active, it will not work, It throws
an error stating that this is not a select statement.

----------------------------------------------------------------------
Zoran · 2012-02-17 16:07

Quote from: jack on February 17, 2012, 03:22:52 pmThanks, The commit
did the trick.

For posterity,   If the Query is active, it will not work, It throws
an error stating that this is not a select statement.

Posterity should also know that you don't have to use TSQLQuery, you
can use TSQLConnection.ExecuteDirect instead (in Firebird case that is
TIBConnection.ExecuteDirect). Still, you must commit the transaction.

There are quite a few nice SQLdb articles in our wiki that you should
read:
http://wiki.lazarus.freepascal.org/Firebird_in_action
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
http://wiki.lazarus.freepascal.org/SQLdb_Package
http://wiki.lazarus.freepascal.org/SqlDBHowto
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery