[Solved] Which component for update?
lazarus / 16-Databases

----------------------------------------------------------------------
jeanchristophe · 2012-05-11 11:19

Hello,

I would like to make the following update on my table:Code:
[Select]update objekt set object='Frimærke' where object= 'Frimµrke'; 
Right now I have:
TSQLite3connection, SQLQuery and SQLTransaction.
Obviously the SQLQuery require a "select".
I try to use SQLscript, but I an't make it works. Probably because I
can't use it!

Can you please tell me how to do so?

Best Regards
Jean-Christophe

----------------------------------------------------------------------
BigChimp · 2012-05-11 11:29

Quote from: jeanchristophe on May 11, 2012, 01:19:49 pmObviously the
SQLQuery require a "select".
That is what one would think looking at the "query" in the name....
but it isn't true.

See e.g. here:
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Insert_query
_example

Edit: note: you can also use the Connection's ExecuteDirect method...

As an aside, I think perhaps the wiki page I linked to could do with
some more updates based on the Dutch version, which is a bit more
comprehensive in places..

----------------------------------------------------------------------
ludob · 2012-05-11 11:45

QuoteObviously the SQLQuery require a "select".A query is any SQL
command.

SQLQuery serves a dual purpose:
- It can act as a Dataset when the query is returning a result set
such as SELECT, SHOW, etc. The query is executed with TSQLQuery.Open.
- It can just execute a query that doesn't return a result set such as
UPDATE, DELETE, etc. The query is executed with TSQLQuery.ExecSQL. The
number of affected rows is returned in SQLQuery2.RowsAffected.

----------------------------------------------------------------------
jeanchristophe · 2012-05-11 11:47

Thank you for your interest BigChimp

When I try to write my sql line, I got this:
Code: [Select]SQLQuery1: Cannot open a non-select statement
I don't know about the Connection's ExecuteDirect method, but I am
searching...

Best Regards
Jean-Christophe

----------------------------------------------------------------------
Arbee · 2012-05-11 11:50

My guess is you try to execute the query by doing an "open".

That is for SELECT queries only.   Use an "ExecSQL" instead, or -
indeed - an "ExecuteDirect".

----------------------------------------------------------------------
jeanchristophe · 2012-05-11 16:19

Hello Arbee,

Here are the few lines of code:Code: [Select]procedure
TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.SQL.Text:= 'UPDATE objekt SET Object="Frimærke" WHERE
Object="Frimµrke";';
  SQLQuery1.Active:= True;
  SQLQuery1.ExecSQL;
  SQLTransaction1.Commit;
Is it wrong?

Best Regards
Jean-Christophe

----------------------------------------------------------------------
ludob · 2012-05-11 18:26

SQLQuery1.Active:= True is the same as SQLQuery1.Open.  Don't do that
if you don't have a result set.

----------------------------------------------------------------------
jeanchristophe · 2012-05-11 21:32

OK thank you for your answer Ludob

I was a little bit curious because the word "object" was highlighted
where I write the SQL statement, so I went to SQLitte manager from
mozilla insted.
I try the samme command several times, without succes.
I exported the database and imported it into a new one (to prevent
damage)
Then I changed the name of the colon from "Object" to "Objet"
Then I try the exactly samme sentence again (since this morning, I
knew it   )
And it's works!

I don't really know, but obviously SQLite3 doesn't like the name
"Object"!

Thak you everybody for your help and see you soon here!