Advise for a standalone database

Post

Posted
Rating:
#1 (In Topic #1120)
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’
 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
Online now: No Back to the top

Post

Posted
Rating:
#2
thatbruce
Avatar
Regular
thatbruce is in the usergroup ‘Regular’
Note: sqlite is a single connection database, i.e. only one process can access the database in a read/write mode at a time.
Online now: No Back to the top

Post

Posted
Rating:
#3
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’

thatbruce said

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?
Online now: No Back to the top

Post

Posted
Rating:
#4
Quincunxian
Avatar
Expert
Quincunxian is in the usergroup ‘Expert’
 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.

Cheers - Quin.
I code therefore I am
Online now: No Back to the top

Post

Posted
Rating:
#5
grayghost4
Avatar
Enthusiast
GrayGhost is in the usergroup ‘Enthusiast’
this is my SQLite read write Module, Probably very similar to your code :

Code (gambas)

  1. ' Gambas module file
  2.  
  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.  
  14.    With $con
  15.       Try .Close()             ' Close the connection. The try allows it to fail without error
  16.      .Type = "sqlite3"        ' Defines the type of connection
  17.      .Host = Application.path ' Host will be the path where the sqlite db file is
  18.      .Name = "data.SQL"  '  database name is the name of the database file"
  19.      Try .Open()                  ' We activate and open the connection, the try is to allow an error
  20.      If Error Then Print "Cannot Open Database. Error = "; Error.Text
  21.      RS = .Exec("Select * From " & sfilename)
  22.    End With
  23.   Return RS.All(scolumnName)   '' return all the data from one column
  24.  End
  25.  
  26. Public Sub GetRecordData(sFileName As String, sFileColumn As String, sFileIndex As String) As String[]
  27.     Dim saInfo As New String[]    ' must have "NEW" or you will get a null error with .add
  28.     Dim obj As ResultField
  29.     RS = $con.Exec("Select * From" & Quote(sFileName) & "where" & Quote(sFileColumn) & "like" & Quote(sFileIndex))
  30.  
  31.      For Each obj In RS.Fields
  32.        sainfo.Add(RS[obj.Name])
  33.      Next
  34.      Return saInfo
  35.   End
  36.  
  37. Public Sub UpdateFileData(sFileName As String, sFileIndex As String, saInfo As String[])
  38.     Dim saCommand As String  
  39.     Dim obj As ResultField
  40.     saInfo = saInfo.Reverse()
  41.     For Each obj In RS.Fields                ''assemble the update command "column" = "data"
  42.        saCommand &= Quote(obj.Name) & " = " & Quote(saInfo.Pop()) & ","
  43.     Next
  44.     saCommand = Left(saCommand, -1)        '' remove the last ","  from saCommand string
  45.        $con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname = " & 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 the string
  54.   sNewRecord = Replace(sNewRecord, "accname", "newAcc-" & Str(Rand(20, 100)))  ' replace first field (random) for index
  55.     $con.Exec("INSERT INTO " & Quote(sFileName) & " VALUES (" & sNewRecord & ")")
  57.  
  58. Public Sub deleatOneRecord(sFileName As String, sFileField As String, sFileIndex As String)
  59.     '' Deleat one reccord from the table with an indexed coluem
  60.         $con.Exec("delete from" & Quote(sFileName) & "where" & Quote(sFileField) & " = " & Quote(sFileIndex))
  62.  
Online now: No Back to the top
1 guest and 0 members have just viewed this.