Cannot execute INSERT with PostgresSQL 8 and TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
Anonymous · 2006-04-21 12:00

Hello

I use PQConnection, SQLTransaction and SQLQuery.
I have no problem executing SELECT statement using Open, but I have
problems using insert.

I cannot user Open for INSERT so I try like this:
       SQLQuery1.Close;
       SQLQuery1.SQL.Clear;
       SQLQuery1.SQL.Add('INSERT into table...');
       SQLQuery1.ExecSQL;  

It gives no error, but does not insert new record into database
either.
What is the correct way to to this?


Cris

----------------------------------------------------------------------
g-r-g · 2006-04-28 11:06

Quote from: "Anonymous"
       SQLQuery1.Close;
       SQLQuery1.SQL.Clear;
       SQLQuery1.SQL.Add('INSERT into table...');
       SQLQuery1.ExecSQL;  

Code: [Select]SQLTransaction1.Commit; ?

----------------------------------------------------------------------
ematutu · 2009-01-19 21:59

Hi, try with this code:

SQLQuery1.Close;
SQLQuery1.InsertSQL.Clear;
SQLQuery1.InsertSQL.Add('insert into table...');
//SQLQuery1.InsertSQL.Add('values...'); //you can divide query
//SQLQuery1.InsertSQL.Add('where...;'); // into multiple lines
SQLQuery1.Open;
SQLQuery1.Insert; //Put Query in Insert Mode
SQLQuery1.Post;
SQLQuery1.ApplyUpdates; //Post & Apply Updates
SQLQuery1.Close;

----------------------------------------------------------------------
Loesje · 2009-01-20 12:44

That could work, but for most queries you don't have to provide an
InsertSQL since it's generated automatically.

And the original code should also work. Most probably he forgot to
commit the transaction, which you also forgot in your example, btw...
(for mysql that doesn't matter since transaction-support isn't
supported for mysql)