### Database SQLite3

When working with SQLite3 Database, it is in cases handy to access a Database direct from a Terminal.
Various examples of SQL Commands using a Domoticz Database "domoticz.db" located on the Raspberry Pi Folder /home/pi/domoticz/.
Full Path is /home/pi/domoticz/domoticz.db.
Install SQLite3
To install run
sudo apt-get install sqlite3
Get Help
sqlite3
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> .help
...
Selecting Meter Data from Domoticz Table METER for DeviceID
SQL Command:
sqlite3 /home/pi/domoticz/domoticz.db "SELECT * FROM METER WHERE DeviceRowID = 45;"
Terminal Output:
45|17371730|0|2016-02-15 09:50:01
45|17371730|0|2016-02-15 09:55:00
...
Delete Meter Data from Domoticz Table METER for DeviceID
sqlite3 /home/pi/domoticz/domoticz.db "DELETE FROM METER WHERE DeviceRowID = 45 AND VALUE < 1000;"
Terminal Output:
None
B4J Non-UI App running SQLite Command
An example of a B4J Non-UI App executing SQLite Commands by using the jShell Library.
#Region Project Attributes
'For Tests
#CommandLineArgs: "/home/pi/domoticz/domoticz.db" "SELECT * FROM METER WHERE DeviceRowID = 45;"
#MergeLibraries: True
#End Region
`Sub Process_Globals
Private shl As Shell
End Sub`
`Sub AppStart (Args() As String)
'Define the argumentslist with the commandline parameter
Dim params As List
params.Initialize
params.AddAll(Args)
'Init the shell
shl.InitializeDoNotHandleQuotes("shl", "sqlite3", params)
Try
Log("Executing SQLite Command")
shl.Run(-1)
Catch
Log(LastException.Message)
End Try
'Do not forget
StartMessageLoop
End Sub`
`Sub shl_ProcessCompleted (Success As Boolean, ExitCode As Int, StdOut As String, StdErr As String)
Dim strResult As String
If Success And ExitCode = 0 Then
strResult = "Shell process completed successfully."& CRLF & "Result:" & CRLF & StdOut
'Here you can take any action with the StdOut data
Else
strResult = "Shell process NOT completed."& CRLF & "Error: " & CRLF & StdErr
End If
'Show the result
Log(strResult)
ExitApplication
End Sub`

Changed: 2018-03-26 13:06, Category: Raspberry Pi, File: T6C234M1711.htm, ID: 1711