SQLite is an embedded (non-server) single-user databaseDoes this disqualify SQLite as database if I also want it to be reachable from Apache?
Maybe a stupid question, but...I might not have directly mentioned it, but my project is such:
whoever said, that the DB-server has to run on the Pi?
[SERVERMACHINE] --> Running/providing MySQL (or whatever RDBMS)
[MACHINEWITHWEBINTERFACE] --> connect to MySQL on [SERVERMACHINE] --> Login to MySQL --> Do jobInstructions
[RPi4b] --> Your App connecting to MySQL on [SERVERMACHINE]
Or is my brain malfunctioning?
If you have to have a web interface, one possibility would be to embed the server into the program actually doing the measurement so that they can use the same instance of SQLite. You can always have a server listen on a non-standard port number to make sure that it doesn't clash with e.g. a pre-existing copy of Apache, /but/ you would have to take into account that a malicious user would probably find holes in a lightweight server that probably wouldn't exist in Apache etc.I want to avoid having specialized client software to maintain. Done it before and it was no fun.
MySQL has a bit of an odd reputation of needing drivers tailored to the exact version of server. For various reasons it's not my favourite choice.
MarkMLl
I want to avoid having specialized client software to maintain. Done it before and it was no fun.
Instead using a web browser and writing a website for config (using PHP unfortunately) is easier on my nerves...
And having a server side software communication over http is no fun either, I have done a similar thing before using both a server and client in Delphi. By I don't want a repeat of that...
(Are you the M Mo..an L..d I have seen on the mail list?)
Instead using a web browser and writing a website for config (using PHP unfortunately) is easier on my nerves...
I really do not want to create a Lazarus GUI application running on the RPi just for setup and configuration.
It shall upload results to a server somewhere when measurements are done.
As for SQLite-Concurrent Access:Interesting! Seems like SQLite can be used after all.
https://stackoverflow.com/questions/4060772/sqlite-concurrent-access
zvoni@ZvoniLinux:~$ locate libsqlite3.so
/snap/core/7917/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/snap/core/7917/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/snap/core18/1223/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/snap/core18/1223/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/mysql-workbench/libsqlite3.so
/usr/lib/x86_64-linux-gnu/libsqlite3.so
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
zvoni@ZvoniLinux:~$
the "locate sqlite"-command?What I get is:
$ locate libsqlite3.so
-bash: locate: command not found
Same if I try sqlite3 on command line:$ sqlite3
-bash: sqlite3: command not found
So I did: $ sudo apt install sqlite3
$ which sqlite3
/usr/bin/sqlite3
sudo find / -name libsqlite3.so
find: ‘/tmp/.vnc-1000/run/gvfs’: Permission denied
$ sudo apt install mlocate
-- stuff happens ---
$ locate libsqlite3.so
locate: can not stat () `/var/lib/mlocate/mlocate.db': No such file or directory
After installing mlocate, issue this command at the prompt: updatedb
$ sudo apt install mlocate
$ locate libsqlite3.so
locate: can not stat () `/var/lib/mlocate/mlocate.db': No such file or director
$ sudo updatedb
$ locate libsqlite3.so
/usr/lib/arm-linux-gnueabihf/libsqlite3.so.0
/usr/lib/arm-linux-gnueabihf/libsqlite3.so.0.8.6
sudo apt install sqlite3 libsqlite3-dev
I did not know of the last part before...I like to be able to inspect the database directly...
sudo apt install sqlitebrowser
:D
*snipp*A very simple example for using SQLite with no GUI
And is there some example of non-GUI use of SQLite in an FPC command line program somewhere?
@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_mushroomsLooked at it and was no wiser...
I've been using fpc with sqlite for CLI and web app without any GUI component whatsoever.Yes it does!
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 (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!
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.
FOLLOW-UP on SQLite usage
Any simpler way?
SQLQuery.SQL.text:='INSERT INTO MyTable (FloatField, DateTimeField) VALUES (:FloatParam, :DateParam);';
SQLQuery.ParamByName('FloatParam').AsFloat:=myFloat;
SQLQuery.ParamByName('DateParam').AsDateTime:=myDateTime;
SQLQuery.ExecSQL;
I don't think it's necessary to use prepare (at least i never used it).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.
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?
dbhandler.pas(232,59) Error: identifier idents no member "AsBLOB"
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?IMO, that's a question that shouldn't even be asked.
Or should they be after each execution of an FQuery object method?
Like ExecSQL and Open etc.
Another item I just ran across when coding:I think you could create a TMemoryStream, and use its Write-Method
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)..Error message:
TS.MeasSettings := FQuery.FieldByName('MeasSettings').AsBLOB;Code: [Select]dbhandler.pas(232,59) Error: identifier idents no member "AsBLOB"
So I tried this instead:
var ARR: TBytes; begin .... SetLength(ARR, SizeOf(TSSMeasSettings)); ARR:= FQuery.FieldByName('MeasSettings').AsBytes; 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...
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 think you could create a TMemoryStream, and use its Write-MethodSeveral errors pop up:
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!)
I have not used streams in my programming earlier so I have to consult web pages...Code: [Select]MyStream:=TMemoryStream.Create(nil);
And reading it from the db is the other way round (Read_method of MemoryStream)
MyStream.Write(MyPackedRecord, 32);
SQLQuery.ParamsByName('MyBlobData').LoadFromStream(MyStream, ftBlob);
SQLQuery.ExecSQL;
here is a working sample:Thanks, I will have a look!
Loading and Saving BLOB's to sqlite