SQLite File open. read . write . deleat

Post

Posted
Rating:
#1 (In Topic #857)
grayghost4
Avatar
Enthusiast
GrayGhost is in the usergroup ‘Enthusiast’
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 : SQLite Tutorial

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.


Code (gambas)

  1. ' ' Gambas module file
  2.    '' Requires    gb.db.sqlite3
  3.    '' Module file to handle SQLite file
  4.    '' SQLite  file handeling subroutines
  5.    '' writen by  GrayGhost4  Marvin Clavey  June 2022
  6.    
  8.   Public sadata As String[]
  10.  
  11.  
  12. Public Sub GetFileData(sFilename As String, scolumnName As String) As String[]
  13. Dim test As Variant[]
  14.      Try $con.Close()             ' Close the connection. The try allows it to fail without error
  15.      $con.Type = "sqlite3"        ' Defines the type of connection
  16.      $con.Host = Application.path ' Host will be the path where the sqlite db file is
  17.      $con.Name = "data.SQL"  '  database name is the name of the database file"
  18.      Try $con.Open()                  ' We activate and open the connection, the try is to allow an error
  19.      If Error Then Print "Cannot Open Database. Error = "; Error.Text
  20.      '' Retreve all the data from sFilename that is in scolumnName
  21.      RS = $con.Exec("Select * From " & Quote(sFilename))
  22.      Return RS.All(scolumnName)   '' return all the data from one column
  23.  End
  24.        
  25. Public Sub GetRecordData(sFileName As String, sFileColumn As String, sFileIndex As String) As String[]
  26.     Dim saInfo As New String[]    ' must have "NEW" or you will get a null error with .add
  27.     Dim obj As ResultField
  28.     RS = $con.Exec("Select * From" & Quote(sFileName) & "where" & Quote(sFileColumn) & "like" & Quote(sFileIndex))
  29.      For Each obj In RS.Fields
  30.      sainfo.Add(RS[obj.Name])
  31.      Next
  32.      Return saInfo
  33.       End
  34.  
  35. Public Sub UpdateFileData(sFileName As String, sFileIndex As String, sainfo As String[])
  36.     Dim saCommand As String  
  37.     Dim obj As ResultField
  38.     Dim x As Integer = 0
  39.     For Each obj In RS.Fields                ''assemble the update command "column" , "data"
  40.        saCommand &= Quote(obj.Name) & " = "
  41.        saCommand &= Quote(sainfo[x]) & ","
  42.        Inc x
  43.     Next
  44.     saCommand = Left(saCommand, -1)        '' remove the last "'"  from saCommand
  45.        $con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname like" & Quote(sFileIndex))
  47.  
  48. Public Sub InsertNewRecord(sFileName As String)
  49.   Dim sNewRecord As String
  50.   For Each obj As Object In RS.Fields    ' This assenbles the File column names it to A string
  51.     sNewRecord &= Quote(obj.Name) & "," ' to insert into a blank record to be edited later
  52.   Next
  53.    sNewRecord = Left(sNewRecord, -1)         ' remove the last "'"  from command
  54.    sNewRecord = Right(sNewRecord, -(InStr(sNewRecord, ",") - 1))       ' Remove the first reccord Field (which is the index to the file)
  55.    sNewRecord = "(" & Quote("newwAcc-" & Str(Rand(20, 100))) & sNewRecord & ")"  ' Add a new first field (random) for index
  56.     $con.Exec("INSERT INTO " & Quote(sFileName) & " VALUES " & sNewRecord)
  58.  
  59. Public Sub deleatOneRecord(sFileName As String, sFileField As String, sFileIndex As String)
  60.     '' Deleat one reccord from the table with an indexed coluem
  61.         $con.Exec("delete from" & Quote(sFileName) & "where" & Quote(sFileField) & "like " & Quote(sFileIndex))
  63.  
  64.  
Online now: No Back to the top
1 guest and 0 members have just viewed this.