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
Advise for a standalone database
Re: Advise for a standalone database
Note: sqlite is a single connection database, i.e. only one process can access the database in a read/write mode at a time.
Have you ever noticed that software is never advertised using the adjective "spreadable".
-
- Posts: 363
- Joined: Wednesday 2nd December 2020 12:11am
- Location: Northampton, England
- Contact:
Re: Advise for a standalone database
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
- Posts: 175
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: Advise for a standalone database
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.
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.
- Attachments
-
- StoryLathe.pdf
- (854.17 KiB) Downloaded 200 times
Cheers - Quin.
I code therefore I am
I code therefore I am
- grayghost4
- Posts: 187
- Joined: Wednesday 5th December 2018 5:00am
- Location: Marengo, Illinois usa
Re: Advise for a standalone database
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