So I thought I would share my results for anyone else that is where I was a month ago (searching here for answers).
Not all of us here are EXPERTS (me included ) .. so this may be of help to some other New-be here
most of my information came from this site : https://www.tutorialspoint.com/sqlite/index.htm
So here is a module that will handle a SQLite file
If anyone has suggestions or problems or bugs let me know.
If you think this should be in the "Farm" please put it there.
' ' Gambas module file
'' Requires gb.db.sqlite3
'' Module file to handle SQLite file
'' SQLite file handeling subroutines
'' writen by GrayGhost4 Marvin Clavey June 2022
Public RS As Result
Public sadata As String[]
Public $con As New Connection
Public Sub GetFileData(sFilename As String, scolumnName As String) As String[]
Dim test As Variant[]
Try $con.Close() ' Close the connection. The try allows it to fail without error
$con.Type = "sqlite3" ' Defines the type of connection
$con.Host = Application.path ' Host will be the path where the sqlite db file is
$con.Name = "data.SQL" ' database name is the name of the database file"
Try $con.Open() ' We activate and open the connection, the try is to allow an error
If Error Then Print "Cannot Open Database. Error = "; Error.Text
'' Retreve all the data from sFilename that is in scolumnName
RS = $con.Exec("Select * From " & Quote(sFilename))
Return RS.All(scolumnName) '' return all the data from one column
End
Public Sub GetRecordData(sFileName As String, sFileColumn As String, sFileIndex As String) As String[]
Dim saInfo As New String[] ' must have "NEW" or you will get a null error with .add
Dim obj As ResultField
RS = $con.Exec("Select * From" & Quote(sFileName) & "where" & Quote(sFileColumn) & "like" & Quote(sFileIndex))
For Each obj In RS.Fields
sainfo.Add(RS[obj.Name])
Next
Return saInfo
End
Public Sub UpdateFileData(sFileName As String, sFileIndex As String, sainfo As String[])
Dim saCommand As String
Dim obj As ResultField
Dim x As Integer = 0
For Each obj In RS.Fields ''assemble the update command "column" , "data"
saCommand &= Quote(obj.Name) & " = "
saCommand &= Quote(sainfo[x]) & ","
Inc x
Next
saCommand = Left(saCommand, -1) '' remove the last "'" from saCommand
$con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname like" & Quote(sFileIndex))
End
Public Sub InsertNewRecord(sFileName As String)
Dim sNewRecord As String
For Each obj As Object In RS.Fields ' This assenbles the File column names it to A string
sNewRecord &= Quote(obj.Name) & "," ' to insert into a blank record to be edited later
Next
sNewRecord = Left(sNewRecord, -1) ' remove the last "'" from command
sNewRecord = Right(sNewRecord, -(InStr(sNewRecord, ",") - 1)) ' Remove the first reccord Field (which is the index to the file)
sNewRecord = "(" & Quote("newwAcc-" & Str(Rand(20, 100))) & sNewRecord & ")" ' Add a new first field (random) for index
$con.Exec("INSERT INTO " & Quote(sFileName) & " VALUES " & sNewRecord)
End
Public Sub deleatOneRecord(sFileName As String, sFileField As String, sFileIndex As String)
'' Deleat one reccord from the table with an indexed coluem
$con.Exec("delete from" & Quote(sFileName) & "where" & Quote(sFileField) & "like " & Quote(sFileIndex))
End