Advise for a standalone database

[AndyGable]

Hi everyone,

Just wanted to ask for some advice. I am creating a small program that needs a database but I don't really any to have to install a full sized MySQL server.

I need to store about 10 tables that would hold about 100-5,000 rows at any given time

Has anyone use SQLite with Gambas and if so is there any example of how to save and recall data (or is that the same way as I'm using MySQL?

Hope someone can guide me to the correct point

Thanks

Andy

[thatbruce]

Note: sqlite is a single connection database, i.e. only one process can access the database in a read/write mode at a time.

[AndyGable]

Note: sqlite is a single connection database, i.e. only one process can access the database in a read/write mode at a time.

Yea that is what I am looking for it's a single application database (the application is used by customers to print dress labels)

So SQLite would be the one to pick?

[Quincunxian]

Hi Andy.
I use SQLite extensively for any project that needs data storage/retrieval.
Unless your data set has millions of records, then it should be more than sufficient for the task.

How are your SQL skills and do you have a rough design for your database structure?
Regardless of size and the underlying platform, it is important to get the design right and I've found that the more time spent on this task, the easier it will be to maintain.

If you could send me a list of the data elements for a table, I'll build a quick example to help you on your way. PM or email.

As a 'very' rough draft, I'm putting together a table mapping of a piece of software for writing a novel - See attachment in pdf form.
Even for simple designs, having one of these and keeping it up to date can be quite useful.

[grayghost4]

this is my SQLite read write Module, Probably very similar to your code :

' Gambas module file

   '' 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[]

   With $con
      Try .Close()             ' Close the connection. The try allows it to fail without error
     .Type = "sqlite3"        ' Defines the type of connection
     .Host = Application.path ' Host will be the path where the sqlite db file is
     .Name = "data.SQL"  '  database name is the name of the database file"
     Try .Open()                  ' We activate and open the connection, the try is to allow an error
     If Error Then Print "Cannot Open Database. Error = "; Error.Text
     RS = .Exec("Select * From " & sfilename) 
   End With 
  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
    saInfo = saInfo.Reverse()
    For Each obj In RS.Fields                ''assemble the update command "column" = "data"
       saCommand &= Quote(obj.Name) & " = " & Quote(saInfo.Pop()) & ","
    Next 
    saCommand = Left(saCommand, -1)        '' remove the last ","  from saCommand string
       $con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname = " & 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 the string 
  sNewRecord = Replace(sNewRecord, "accname", "newAcc-" & Str(Rand(20, 100)))  ' replace 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) & " = " & Quote(sFileIndex))
End