Lazarus fails to autogenerate proper InsertSQL, how to remedy?
lazarus / 16-Databases

----------------------------------------------------------------------
SeriousSam · 2016-06-16 12:03

I'm trying to insert payment records into a SQLite database, but
apparently InsertSQL isn't autogenerating the correct INSERT
statement, and I have discovered that I can't simply assign parameters
to .InsertSQL by using .ParamByName like I can to the .SQL property.

My database has tables of Customers and Payments. The Payments table
is as follows:

Pay_Key: Primary key, Integer, Not NULL. Identifies a single payment
row in the table.
Pay_Customer: Integer, Not NULL. Foreign-key linked to an integer-type
Not NULL primary key in my Customers table.
Pay_Sum: Integer. (Yes, I'm storing the payment sum as integer, but
this isn't really important here.)

I'm using SELECT * FROM Payments WHERE Pay_Customer=:CustomerKey in my
SQLQuery.SQL, and assigning :CustomerKey each time programmatically
via SQLQuery.ParamByName('CustomerKey').Text. This lets me navigate
existing records nicely in a DBGrid, but when I try to insert a new
payment, the operation fails on the "Pay_Customer Not NULL" condition.
Apparently Lazarus doesn't know what value to use for Pay_Customer,
since I passed it programmatically.

How can I remedy this? I can write my own InsertSQL if need be, I just
don't understand how I can pass this customer parameter to it. I would
very much like to use InsertSQL/UpdateSQL/DeleteSQL, since they would
make it easy to use stock DBGrid/DBNavigator components and logic for
what I'm doing.

Thanks for any suggestions, and sorry if this is a n00b question.

 -Sam

ps. I'm using Lazarus 1.6 (FPC 3.0.0). I have enabled foreign keys in
SQLite in SQLite3Connection.Params (foreign_keys=on).

----------------------------------------------------------------------
ezlage · 2016-06-16 14:55

InsertSQL is a property, not a procedure/function. It receives some
StringList with your SQL code (SQLQuery1.InsertSQL:=AStringList or
SQLQuery1.InsertSQL.Add('SQL CODE').

So, no SQL code is automatically generated, you must provide it.

----------------------------------------------------------------------
LacaK · 2016-06-16 17:49

Once you open dataset (TSQLQuery) just Append record, supply values
and perform Post.
Then ApplyUpdates will write these localy cahced updates to database.
And Comit ar CommitRetaining will commit changes so they will be
permanent.

In case of simle SQL.Test like "SELECT * FROM table WHERE condition",
INSERTSQL should be generated properly ...