SQLite File open. read . write . deleat

Post your Gambas programming questions here.
Post Reply
User avatar
grayghost4
Posts: 174
Joined: Wednesday 5th December 2018 5:00am
Location: Marengo, Illinois usa

SQLite File open. read . write . deleat

Post by grayghost4 »

I posted earlier about SQL file handling, after some research and trial and error I have got my program working.
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

Last edited by grayghost4 on Wednesday 15th June 2022 9:43pm, edited 1 time in total.
Post Reply