Recent

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

BosseB

  • Sr. Member
  • ****
  • Posts: 468
Re: Using SQLite database in command line program on RPi4B?
« Reply #45 on: November 27, 2019, 09:52:21 am »
OK, I did not really see the colons in your example...
Anyway I also read up on SQLite and the sqldb in Lazarus so I have done this:

Code: Pascal  [Select][+][-]
  1.   SQL := 'INSERT INTO TASKS ' +
  2.          '(TaskName, CmdFileName, ResultFileName, TaskRepeat, RepeatInterval, MaxRunTime, StartTime, ' +
  3.          'Enabled, NextCmd, ContactRes, ErrorCnt, ScaleFactor, MeasSettingID) ' +
  4.          'VALUES (:TaskName, :CmdFileName, :, :TaskRepeat, :RepeatInterval, :MaxRunTime, :StartTime, ' +
  5.          ':Enabled, :NextCmd, :ContactRes, :ErrorCnt, :ScaleFactor, :MeasSettingID);';
  6.  
  7.   FQuery.SQL.Text := SQL;
  8.   FQuery.Prepare;
  9.   FQuery.Params.ParamByName('TaskName').AsString := TS.Name;
  10.   FQuery.Params.ParamByName('CmdFileName').AsString := TS.CmdFileName;
  11.   FQuery.Params.ParamByName('ResultFileName').AsString := TS.ResultFileName;
  12.   FQuery.Params.ParamByName('TaskRepeat').AsInteger := TS.TaskRepeat;
  13.   FQuery.Params.ParamByName('RepeatInterval').AsInteger := TS.TaskRepeatInterval;
  14.   FQuery.Params.ParamByName('MaxRunTime').AsInteger := TS.MaxRunTime;
  15.   FQuery.Params.ParamByName('StartTime').AsDateTime := TS.StartTime;
  16.   FQuery.Params.ParamByName('Enabled').AsInteger := BoolToInt(TS.Enabled);
  17.   FQuery.Params.ParamByName('NextCmd').AsInteger := TS.NextCmd;
  18.   FQuery.Params.ParamByName('ContactRes').AsInteger := BoolToInt(TS.ContactResTest);
  19.   FQuery.Params.ParamByName('ErrorCnt').AsInteger := TS.ErrorCount;
  20.   FQuery.Params.ParamByName('MeasSettingID').AsInteger := TS.MeasSettingsID;
  21.   FQuery.Params.ParamByName('ScaleFactor').AsFloat := TS.ScaleFactor;
  22.   FQuery.ExecSQL;

Notice the Prepare call after setting SQL of the FQuery component.
Apparently this is required in order to use parameters but it is missing from your example.
Is this because it is really not needed?

I have not yet been able to test the call because of other parts of the program not yet done.
But it compiles without errors at least.
--
Bo Berglund
Sweden

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Using SQLite database in command line program on RPi4B?
« Reply #46 on: November 27, 2019, 12:13:09 pm »
I don't think it's necessary to use prepare (at least i never used it).

but something else: You do know, that you have to call the Commit-Method of the Transaction-Object to actually write the changes permanently to the DB?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

BosseB

  • Sr. Member
  • ****
  • Posts: 468
Re: Using SQLite database in command line program on RPi4B?
« Reply #47 on: November 27, 2019, 06:52:53 pm »
I don't think it's necessary to use prepare (at least i never used it).

but something else: You do know, that you have to call the Commit-Method of the Transaction-Object to actually write the changes permanently to the DB?
No, that is not something I have thought about, otoh I have actually put it into the destructor of my db object, so it kind of worked for what I was doing initially.
Now I have added a second Commit in the InitDb function.
In my application I initialize and destroy the db stuff like this:

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.   FConn.Transaction := FTrans;
  7.   FTrans.DataBase := FConn;
  8.   FQuery.Transaction := FTRans;
  9.   FQuery.DataBase := FConn;
  10.   FConn.DatabaseName := DbFile;
  11.   FConn.CharSet := 'UTF8';
  12.   InitDb(dbfile);  //How to deal with failure?
  13. end;
  14.  
  15. function TMonitorDb.InitDb(DbFile: string): boolean;
  16. var
  17.   NewDatabase: boolean;
  18. begin
  19.   Result := false;
  20.   try
  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 ' + DbFile);
  27.       FQuery.SQL.Clear;
  28.       //Use queries to build database
  29.       FQuery.SQL.Text := SQL_CREATE_TASKS;
  30.       Log.LogDeb('TASKS');
  31.       FQuery.ExecSQL;
  32.       FQuery.SQL.Text := SQL_CREATE_MEASSETTINGS;
  33.       Log.LogDeb('MEASSETTINGS');
  34.       FQuery.ExecSQL;
  35.       FQuery.SQL.Text := SQL_CREATE_CMDFILES;
  36.       Log.LogDeb('CMDFILES');
  37.       FQuery.ExecSQL;
  38.       FQuery.SQL.Text := SQL_CREATE_TASKLOG;
  39.       Log.LogDeb('TASKLOG');
  40.       FQuery.ExecSQL;
  41.       FTrans.Commit;
  42.     end;
  43.     Result := true;
  44.   except
  45.     FLastError := 'Exception! Could not initialize database ' + DbFile;
  46.     Log.LogErr('Exception! Could not create tables!');
  47.   end;
  48. end;
  49.  
  50. destructor TMonitorDb.Destroy;
  51. begin
  52.   if FConn.Connected then
  53.   begin
  54.     FTrans.Commit;
  55.     FQuery.Close;
  56.     FConn.Close;
  57.   end;
  58.   FQuery.Free;
  59.   FTrans.Free;
  60.   FConn.Free;
  61.   inherited Destroy;
  62. end;
  63.  

I guess your advice is to add a FTrans.Commit at the end of all of the specific functions I am writing in order to add modify and delete records in the database?
Or should they be after each execution of an FQuery object method?
Like ExecSQL and Open etc.
« Last Edit: November 27, 2019, 08:59:00 pm by BosseB »
--
Bo Berglund
Sweden

BosseB

  • Sr. Member
  • ****
  • Posts: 468
Re: Using SQLite database in command line program on RPi4B?
« Reply #48 on: November 27, 2019, 09:55:14 pm »
Another item I just ran across when coding:
The task object contains a property that is actually a packed record type containing a mix of data totalling 32 bytes.
I would like to store that in the database as a single binary value, is that possible?
For example if I set it as a BLOB data type how exactly can I read and write it into the database?

The record consists of:
8  one-byte size parameters
1 single precision float value
3 2-byte (smallint) parameters
1 14-byte array of byte containing various data.

This packet is going to be transferred as-is to the equipment controlled by the application.

So it is important that the 32 bytes are intact from input into the database and back out again.

How can this be accomplished with SQLite?
A simple example will do, all I can find on the net is related to storing images...
And this attempt failed:
(Here TS.MeasSettings is a record type (packed as described above)..

Code: Pascal  [Select][+][-]
  1.     TS.MeasSettings := FQuery.FieldByName('MeasSettings').AsBLOB;
Error message:
Code: [Select]
dbhandler.pas(232,59) Error: identifier idents no member "AsBLOB"
So I tried this instead:
Code: Pascal  [Select][+][-]
  1. var
  2.    ARR: TBytes;
  3. begin
  4.     ....
  5.     SetLength(ARR, SizeOf(TSSMeasSettings));
  6.     ARR:= FQuery.FieldByName('MeasSettings').AsBytes;
  7.     Move(ARR[0], TS.MeasSettings, SizeOf(TSSMeasSettings));

This does not generate any compilation errors, but is it really what one should do?
I am not comfortable using Move(), but when nothing else seems to work that is what I have done in the past as well...
--
Bo Berglund
Sweden

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Using SQLite database in command line program on RPi4B?
« Reply #49 on: November 28, 2019, 08:05:39 am »
I guess your advice is to add a FTrans.Commit at the end of all of the specific functions I am writing in order to add modify and delete records in the database?
Or should they be after each execution of an FQuery object method?
Like ExecSQL and Open etc.
IMO, that's a question that shouldn't even be asked.
Think about it: Your App runs with your code for, say, a week and collects data, but never "commits" it.
And suddenly the construction worker outside decides to cut the power supply......
A rule of thumb (at least for me): If you're sure about your data --> commit immediately. This applies especially for INSERTS.
For UPDATE's and DELETE's: --> the "classic" scenario --> Do the UPDATE/DELETE (don't commit!) --> Ask User "Are you sure?" --> Yes=Commit / No=Rollback
If there is no User-Feedback required --> commit immediately
Bottom Line: Commit after each "ExecSQL"
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Using SQLite database in command line program on RPi4B?
« Reply #50 on: November 28, 2019, 08:24:06 am »
Another item I just ran across when coding:
The task object contains a property that is actually a packed record type containing a mix of data totalling 32 bytes.
I would like to store that in the database as a single binary value, is that possible?
For example if I set it as a BLOB data type how exactly can I read and write it into the database?

The record consists of:
8  one-byte size parameters
1 single precision float value
3 2-byte (smallint) parameters
1 14-byte array of byte containing various data.

This packet is going to be transferred as-is to the equipment controlled by the application.

So it is important that the 32 bytes are intact from input into the database and back out again.

How can this be accomplished with SQLite?
A simple example will do, all I can find on the net is related to storing images...
And this attempt failed:
(Here TS.MeasSettings is a record type (packed as described above)..

Code: Pascal  [Select][+][-]
  1.     TS.MeasSettings := FQuery.FieldByName('MeasSettings').AsBLOB;
Error message:
Code: [Select]
dbhandler.pas(232,59) Error: identifier idents no member "AsBLOB"
So I tried this instead:
Code: Pascal  [Select][+][-]
  1. var
  2.    ARR: TBytes;
  3. begin
  4.     ....
  5.     SetLength(ARR, SizeOf(TSSMeasSettings));
  6.     ARR:= FQuery.FieldByName('MeasSettings').AsBytes;
  7.     Move(ARR[0], TS.MeasSettings, SizeOf(TSSMeasSettings));

This does not generate any compilation errors, but is it really what one should do?
I am not comfortable using Move(), but when nothing else seems to work that is what I have done in the past as well...
I think you could create a TMemoryStream, and use its Write-Method
https://www.freepascal.org/docs-html/current/rtl/objects/tmemorystream.write.html

In the End it really depends what alignement your packed record has.

After that it should be a simple
(note: Aircode! Not tested!)
Code: [Select]
MyStream:=TMemoryStream.Create(nil);
MyStream.Write(MyPackedRecord, 32);
SQLQuery.ParamsByName('MyBlobData').LoadFromStream(MyStream, ftBlob);
SQLQuery.ExecSQL;
And reading it from the db is the other way round (Read_method of MemoryStream)
« Last Edit: November 28, 2019, 08:25:54 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

BosseB

  • Sr. Member
  • ****
  • Posts: 468
Re: Using SQLite database in command line program on RPi4B?
« Reply #51 on: November 28, 2019, 11:53:47 am »
I think you could create a TMemoryStream, and use its Write-Method
https://www.freepascal.org/docs-html/current/rtl/objects/tmemorystream.write.html

In the End it really depends what alignement your packed record has.

After that it should be a simple
(note: Aircode! Not tested!)
Several errors pop up:
1) TMemoryStream.Create takes no arguments.
2) SQLQuery.ParamsByName does not exist, it shall be FieldByName
3) LoadFromStream() does not exist for FQuery
4) (MyStream, ftBlob), ftBlob is not recognized 
Quote
Code: [Select]
MyStream:=TMemoryStream.Create(nil);
MyStream.Write(MyPackedRecord, 32);
SQLQuery.ParamsByName('MyBlobData').LoadFromStream(MyStream, ftBlob);
SQLQuery.ExecSQL;
And reading it from the db is the other way round (Read_method of MemoryStream)
I have not used streams in my programming earlier so I have to consult web pages...
The example given does not work...

And this example is the wrong way, my problem here is to read from the database, not to write to it.
That will be the task for the next method I will write.

I understand how to read bytes from and write to a memory stream, but not how to actually write the stream with data from a Query..
In the previous query calls in my code it is  a simple assignment from the query to a variable specifying the AsSomething.
But In this case, how can it be done???
I tried to google it but find only non-working Delphi examples...
What do I do with the below code to READ data from the query into the TS.MeasSettings record?

Code: Pascal  [Select][+][-]
  1.     //Alternate solution using memory stream:
  2.     MStr:=TMemoryStream.Create;
  3.     //How to get the data from FQuery into the memory stream?
  4.     MStr.Write(FQuery.FieldByName('MeasSettings'), SizeOf(TSSMeasSettings)); //Does this really work?
  5.     MStr.Write(FQuery.FieldByName('MeasSettings').AsBytes, SizeOf(TSSMeasSettings)); //Or is this how it must be done?
  6.     //Move data from stream to record:
  7.     MStr.Read(TS.MeasSettings, SizeOf(TSSMeasSettings));
  8.     MStr.Free;

It does not throw compile errors, but since I do not have a write to database method yet I cannot check if the correct data is stuffed into the database and read back...
And it seems like there is a problem to use memory stream for writing to the database blob field..


« Last Edit: November 28, 2019, 11:55:41 am by BosseB »
--
Bo Berglund
Sweden

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Using SQLite database in command line program on RPi4B?
« Reply #52 on: November 28, 2019, 12:07:39 pm »
Look here at the last answer: https://stackoverflow.com/questions/14709685/how-can-i-do-insert-into-blob-in-sqlite-using-delphi-xe3
Yes, he uses a Picture-file for his example, but the source of the MemoryStream doesn't matter
btw: i had a typo in my example.
it must ParamByName, not ParamsByName
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Using SQLite database in command line program on RPi4B?
« Reply #53 on: November 28, 2019, 02:41:26 pm »
OK,
here is a working sample:
Loading and Saving BLOB's to sqlite

No idea if there is a more elegant way
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

BosseB

  • Sr. Member
  • ****
  • Posts: 468
Re: Using SQLite database in command line program on RPi4B?
« Reply #54 on: November 28, 2019, 09:23:45 pm »
Quote
here is a working sample:
Loading and Saving BLOB's to sqlite
Thanks, I will have a look!

Later:
Yes it does compile and it seems like a very good way to handle blob fields AFAICT at the moment.
I still do not have a database containing the data to read back so I will have to come back later.

This code compiles successfully using your suggested way (and after adding db to uses):
Code: Pascal  [Select][+][-]
  1. //Reading data from database:
  2. var
  3.   MStr: TStream;
  4.   ...
  5. begin
  6.   ...
  7.     MStr := FQuery.CreateBlobStream(FQuery.FieldByName('MeasSettings'), bmRead);
  8.     MStr.Read(TS.MeasSettings, SizeOf(TSSMeasSettings));
  9.     MStr.Free;
  10.    ....
  11. end;
  12.  
  13. //Writing data to database:
  14. var
  15.   MStr: TMemoryStream;
  16.   ...
  17. begin
  18.   ...
  19.   //Alternate solution using stream
  20.   MStr:=TMemoryStream.Create;
  21.   MStr.Write(TS.MeasSettings, SizeOf(TSSMeasSettings));
  22.   FQuery.ParamByName('MeasSettings').LoadFromStream(MStr, ftBlob);
  23.   MStr.Free;
  24.  


<DELETED stupid question, a simple Quick Compile showed it is illegal to do typecast like that...>
« Last Edit: November 28, 2019, 10:02:28 pm by BosseB »
--
Bo Berglund
Sweden

 

TinyPortal © 2005-2018