Recent

Author Topic: Using SQLite database in command line program on RPi4B?  (Read 2171 times)

BosseB

  • Jr. Member
  • **
  • Posts: 92
Re: Using SQLite database in command line program on RPi4B?
« Reply #30 on: November 22, 2019, 03:28:33 pm »
I can report back that on Raspbian this comnmand gave me a functioning SQLite database browser:
Code: [Select]
sudo apt install sqlitebrowser :D
--
Bo Berglund
Sweden

MarkMLl

  • Sr. Member
  • ****
  • Posts: 285
Re: Using SQLite database in command line program on RPi4B?
« Reply #31 on: November 22, 2019, 03:41:41 pm »
Well done that man and thanks for keeping people updated with a useful success story.
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

Zvoni

  • Sr. Member
  • ****
  • Posts: 299
Re: Using SQLite database in command line program on RPi4B?
« Reply #32 on: November 22, 2019, 05:16:09 pm »
*snipp*
And is there some example of non-GUI use of SQLite in an FPC command line program somewhere?
A very simple example for using SQLite with no GUI
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

BosseB

  • Jr. Member
  • **
  • Posts: 92
Re: Using SQLite database in command line program on RPi4B?
« Reply #33 on: November 22, 2019, 10:06:09 pm »
@Zvoni Thanks, helpful example!

Another question now that I am getting into coding SQLite:

I noted that there is a storage class BLOB and that it can be used to store "anything".
I have never touched on using this kind of data but its existence intrigues me since I am moving away from creating and handling files of data into using the SQLite database. I know it exists in other databases too but I want specifically to know how it works in SQLite...

So: Is there a freepascal sample of storing/retrieving binary data using SQLite BLOB?

Say I have  a number of files I want to store as BLOB objects in a database, how do the SQL statements look like for INSERT and SELECT of them?
The files may be text files or zip files in my case.

I have tried to search for example code but got nowhere really.

For text files I realize I can use a text column for the body and another for the file name, but what about zipfiles, they will need blob, right?

I am quite new to database work in Lazarus but I have done a fair deal of code in Delphi towards MSSQLServer databases.
Never ever used BLOB columns, though.
--
Bo Berglund
Sweden

dsiders

  • Full Member
  • ***
  • Posts: 238
Re: Using SQLite database in command line program on RPi4B?
« Reply #34 on: November 22, 2019, 10:27:51 pm »
@Zvoni Thanks, helpful example!

Another question now that I am getting into coding SQLite:

I noted that there is a storage class BLOB and that it can be used to store "anything".
I have never touched on using this kind of data but its existence intrigues me since I am moving away from creating and handling files of data into using the SQLite database. I know it exists in other databases too but I want specifically to know how it works in SQLite...

So: Is there a freepascal sample of storing/retrieving binary data using SQLite BLOB?

There is an example using Sqlite (Firebird embedded too) to store images. See: examples/database/image_mushrooms
Lazarus 2.0.4 / FPC 3.0.4 / Windows 8.1 64-bit

BosseB

  • Jr. Member
  • **
  • Posts: 92
Re: Using SQLite database in command line program on RPi4B?
« Reply #35 on: November 22, 2019, 11:26:53 pm »
There is an example using Sqlite (Firebird embedded too) to store images. See: examples/database/image_mushrooms
Looked at it and was no wiser...
Can't find any SQL code in any file that is dealing with reading or writing a BLOB field.
All I see is SQL for creating the database itself.
--
Bo Berglund
Sweden

winni

  • Hero Member
  • *****
  • Posts: 590
Re: Using SQLite database in command line program on RPi4B?
« Reply #36 on: November 23, 2019, 12:04:28 am »
Hi!

Example and discussion how to save images in blob field in sqlite:

https://forum.lazarus.freepascal.org/index.php?topic=39430.0

Winni

bee

  • Sr. Member
  • ****
  • Posts: 365
Re: Using SQLite database in command line program on RPi4B?
« Reply #37 on: November 23, 2019, 01:58:59 am »
I've been using fpc with sqlite for CLI and web app without any GUI component whatsoever. I even made an article about it. Well, it's written in Bahasa Indonesia, but Google Translate should be able to help read it in any languages you want. It's here: https://paklebah.github.io/fpc-sqldb-dan-sqlite.html

Hope it helps.
-Bee-

A long time pascal lover.

BosseB

  • Jr. Member
  • **
  • Posts: 92
Re: Using SQLite database in command line program on RPi4B?
« Reply #38 on: November 25, 2019, 05:21:13 pm »
I've been using fpc with sqlite for CLI and web app without any GUI component whatsoever.
I even made an article about it. Well, it's written in Bahasa Indonesia, but Google Translate should be able to help read it in any languages you want.
It's here:
https://paklebah.github.io/fpc-sqldb-dan-sqlite.html

Hope it helps.
Yes it does!
I really can manage fine with all the examples you have added into the article, quite clear what you are doing.
I can go on from there.

THANKS A LOT!
--
Bo Berglund
Sweden

BosseB

  • Jr. Member
  • **
  • Posts: 92
Re: Using SQLite database in command line program on RPi4B?
« Reply #39 on: November 25, 2019, 09:46:21 pm »
So now I have come a bit further in my creating a DbHandler class for my project.
I am encapsulating all of the database operations in a class where I have this code when I instantiate it:

Code: Pascal  [Select]
  1. constructor TMonitorDb.Create(dbfile: string);
  2. begin
  3.   FConn := TSQLite3Connection.Create(NIL);
  4.   FTrans := TSQLTransaction.Create(NIL);
  5.   FQuery := TSQLQuery.Create(NIL);
  6.   InitDb(dbfile);  //How to deal with failure?
  7. end;
  8.  
  9. function TMonitorDb.InitDb(DbFile: string): boolean;
  10. var
  11.   NewDatabase: boolean;
  12. begin
  13.   Result := false;
  14.   try
  15.     FConn.Transaction := FTrans;
  16.     FTrans.DataBase := FConn;
  17.     FQuery.Transaction := FTRans;
  18.     FQuery.DataBase := FConn;
  19.     FConn.DatabaseName := DbFile;
  20.     FConn.CharSet := 'UTF8';
  21.     NewDatabase := not FileExists(DbFile);
  22.     FConn.Open;
  23.     Result := FConn.Connected;
  24.     if Result and NewDatabase then //Database not existing, so create tables
  25.     begin
  26.       Log.LogStd('Populating new database');
  27.       FQuery.SQL.Clear;
  28.       //Use queries to build database
  29.       FQuery.SQL.Text := SQL_CREATE_TASKS;
  30.       FQuery.ExecSQL;
  31.       FQuery.SQL.Text := SQL_CREATE_MEASSETTINGS;
  32.       FQuery.ExecSQL;
  33.       FQuery.SQL.Text := SQL_CREATE_CMDFILES;
  34.       FQuery.ExecSQL;
  35.       FQuery.SQL.Text := SQL_CREATE_TASKLOG;
  36.       FQuery.ExecSQL;
  37.     end;
  38.     Result := true;
  39.   except
  40.     FLastError := 'Exception! Could not initialize database ' + DbFile;
  41.   end;
  42. end;

In the InitDb method if there is no database it creates it (handled by SqLite itself when it opens a non-existing database file. If that was done I want to set up the tables as shown above, but for some reason it won't work.
Only the two first of the tables are created...
If I run the query strings directly in the db browser they are all created, but I am running them one by one.

Questions:
Is there something more to do in between the SQL calls? Delays? (Not fun...)
In MSSQLServer I used to insert a GO command between multiple queries in a stored procedure, is there something like that also in SQLite?
Or is there some query property one can check to verify that the operation is ready?

I am using 4 query strings similar to the one below, one for each table.
At first I loaded them all into the FQuery.SQL stringlist but then only a single table was created.
Then I changed to the code above and now I get two tables....

Typical SQL string:
Code: Pascal  [Select]
  1.   SQL_CREATE_TASKLOG = 'CREATE TABLE `TASKLOG` ( '+
  2.           '`TaskLogID`  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, '+
  3.           '`TaskID`     INTEGER NOT NULL DEFAULT -1, '+
  4.           '`StartTime`  TEXT, '+
  5.           '`EndTime`    TEXT, '+
  6.           '`Readings`   INTEGER NOT NULL DEFAULT 0); ';

Can the TSQLTRansaction be used to verify that the ExecSql has actually finished before I run the next?
« Last Edit: November 25, 2019, 09:49:31 pm by BosseB »
--
Bo Berglund
Sweden

BosseB

  • Jr. Member
  • **
  • Posts: 92
Re: Using SQLite database in command line program on RPi4B?
« Reply #40 on: November 25, 2019, 11:10:26 pm »
RESOLVED!
Issue 1: Why could I not run multiple statements?
Well it turns out that it is a limitation of SQLite3. It can only process one query statement at a time, but otoh it does it very quickly so there is no real loss of efficiency by having multiple calls to ExecSQL.

Issue 2: Why did it stop after 2 tables were done?
In fact it was the string constants used to create the tables that had an error!
What happened was that I had copied the SQL text from "DB Browser for SQLite", which I used to create the sql and test that it worked. Inside that I had a -- comment in one table definition line, which was now probably killing the remainder of the statement since I have no line breaks included in the SQL string constants....

Once I fixed issue 1 by separating the tables into an ExecSql for each and corrected issue 2, the string constant, by removing the comment it worked as expected.

The whole process took 5 ms for creation of 4 tables with logging to a logfile in-between each.
Pretty OK to me.
--
Bo Berglund
Sweden

bee

  • Sr. Member
  • ****
  • Posts: 365
Re: Using SQLite database in command line program on RPi4B?
« Reply #41 on: November 26, 2019, 02:05:22 am »
Yes it does!
I really can manage fine with all the examples you have added into the article, quite clear what you are doing.
I can go on from there.

THANKS A LOT!

You're welcome. Glad to know it helps. :)
-Bee-

A long time pascal lover.

Zvoni

  • Sr. Member
  • ****
  • Posts: 299
Re: Using SQLite database in command line program on RPi4B?
« Reply #42 on: November 26, 2019, 08:14:36 am »
RESOLVED!
Issue 1: Why could I not run multiple statements?
Well it turns out that it is a limitation of SQLite3. It can only process one query statement at a time, but otoh it does it very quickly so there is no real loss of efficiency by having multiple calls to ExecSQL.

OK, resolved and all, but take a look at Transactions. SQLite supports them.
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

BosseB

  • Jr. Member
  • **
  • Posts: 92
Re: Using SQLite database in command line program on RPi4B?
« Reply #43 on: November 26, 2019, 07:46:44 pm »
FOLLOW-UP on SQLite usage
The functionalit to populate a new database with my table is now working as described above.

So now I have another question regarding handling of the interface between program and database.

There are a number of methods I need to program and I have trouble dealing with datetime and floating point data.
Integer and string are OK, but what to do about datetimes and floats?
The problem here is that the format of these are locale dependent, I would like to just send the TDateTime variable or the double variable into the database. But when I formulate the insert query I seem to need a string representation of these values and here the translation annoys me.
Do I really have to do something like this all the time (shortened SQL build construct)?

Code: Pascal  [Select]
  1. SQL := 'INSERT INTO TASKS ' +
  2.          '(TaskName, ..., TaskRepeat, .... StartTime, .... ScaleFactor) ' +
  3.          'VALUES ' +
  4.          '("'+
  5.          TS.Name + '", "' +
  6.          ....
  7.          IntToStr(TS.TaskRepeat) + ', ' +
  8.          ....
  9.          FormatTime(TS.StartTime) + ', ' +
  10.          ....
  11.          FormatDouble(TS.ScaleFactor) + ' ' +
  12.          ');';

When reading out data from the database result there is a simpler way:

Code: Pascal  [Select]
  1.   SQL := 'SELECT * FROM TASKS WHERE TaskId = ' + IntToStr(ID);
  2.   FQuery.SQL.Text := SQL;
  3.   FQuery.Open;
  4.   if not FQuery.Eof then
  5.   begin
  6.     TS.TaskID := FQuery.FieldByName('TaskID').AsInteger;
  7.     TS.Name := FQuery.FieldByName('TaskName').AsString;
  8.         ....
  9.     TS.TaskRepeat := FQuery.FieldByName('TaskRepeat').AsInteger;
  10.         ....
  11.     TS.StartTime := FQuery.FieldByName('StartTime').AsDateTime;
  12.         ....
  13.     TS.ScaleFactor := FQuery.FieldByName('ScaleFactor').AsFloat;
  14.  

So is there some way to just put the variables themselves into the operation to add an object's data into the database?

Please note that the data and time formats vary wildly across the globe, I prefer ISO (yyyy-mm-dd hh:mm:ss.sss) myself.
And the decimal point of floats is either period or comma depending on location. I want this system to not be depending on keeping track of such things...
And I don't know where the system will be deployed, can be anywhere in the world.

Any simpler way?
« Last Edit: November 26, 2019, 11:34:31 pm by BosseB »
--
Bo Berglund
Sweden

Zvoni

  • Sr. Member
  • ****
  • Posts: 299
Re: Using SQLite database in command line program on RPi4B?
« Reply #44 on: November 27, 2019, 08:41:22 am »
FOLLOW-UP on SQLite usage
Any simpler way?

Yes!
Use Parameters. I think i used Parameters in the example i've posted.
With Parameters you don't have to juggle around Formats, since the Parameters are taking care of that.
AIRCODE!
Code: [Select]
SQLQuery.SQL.text:='INSERT INTO MyTable (FloatField, DateTimeField) VALUES (:FloatParam, :DateParam);';
SQLQuery.ParamByName('FloatParam').AsFloat:=myFloat;
SQLQuery.ParamByName('DateParam').AsDateTime:=myDateTime;
SQLQuery.ExecSQL;
« Last Edit: November 27, 2019, 08:48:17 am by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts