Action queries in Lazarus/SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
SteveF · 2009-02-22 22:07

I'm coming into Lazarus from a VB & MS Access background.  I decided
to start with SQLite as a DBMS in order to "get my feet wet".  I may
move on to other systems in the future.

OK, the following is a perfectly legitimate line of VB code within the
MS Access environment to run an Update query on an internal table:

CurrentDb.Execute "UPDATE WorkTable SET Sequence = Format(Sequence,
"000000");"

This reformats all numbers in the Sequence column (defined as a Text
datatype, size 15) to six characters with leading zeroes.  "23"
becomes "000023", "100" becomes "000100", and so on.

How could I achieve the same result in Lazarus/SQLite?

Thanks,
SteveF

----------------------------------------------------------------------
LuizAmérico · 2009-02-23 21:07

CurrentDb.ExecSql('UPDATE WorkTable SET Sequence = Format(Sequence,
"000000");');

----------------------------------------------------------------------
SteveF · 2009-02-24 20:52

Luiz Americo,

The "CurrentDB" in this MS Access statement

CurrentDb.Execute "UPDATE WorkTable SET Sequence = Format(Sequence,
"000000");"

Is a reserved word in Access.  This particular setting points to the
current set of tables available within the *.mdb file where the VB
code is running.  The setting can be changed, if needed, to point to
other tables in other files.

In Lazarus & SQLite, would I need to use a Dataset connection?  Would
I need a separate connection that only specifies a SQLite file, with
no specific table?

Thank you for your assistance,
Steve Frye

----------------------------------------------------------------------
LuizAmérico · 2009-02-25 22:15

SqliteDataset uses it's own format so your current files will not
work.

Understand CurrentDB as the name of SqliteDataset instance, when you
drop a dataset in a form the first name it gets is Sqlite3Dataset1.
You can rename it as you like.

The line should be :

Sqlite3Dataset1.ExecSql('UPDATE WorkTable SET Sequence =
Format(Sequence, "000000");');