Hi ALl
Is there a need amongst the users of this group that we start a thread regarding SQLITE and Freebasic from beginner user to expert advice?
Powerbasic is never coming back, and is counterproductive to still try and learn using SQLITE that way.
Would it be helpful if we ask Paul to start a thread here that will take everyone from start to expert on the matter?
e.g This is how you include the required wrappers classes into FB, 2) This is how you do a basic query, How you do basic insert etc, etc? And then move it up gradually.
Or is everyone else here so advanced that I am the only one here in need of this?
-Regards, Peter
Hello Peter,
I like the idea. I stayed away from SQLite because I thought the learning curve was too steep. However, once I took the plunge, it turned out to be far simpler than I had imagined; I was up and running in no time.
Paul's clsSQLite3.bas, Jose's SQLite wrapper and Richard Kelly's SQLite app, have all been informative and helpful.
An SQLite tutorial for FB together with WinFBE...
I'm not an expert in SQLite. I only have a few examples that I wrote to test the CSQLite clases:
Basic steps
' Basic steps
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx
' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
DIM pSql AS CSQLite = "sqlite3_32.dll"
print pSql.m_hLib
' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "Test.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName
' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
AfxMsg "Unable to create the table"
END
END IF
' // Insert rows
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"
' // Prepare a query
DIM pStmt AS CSqliteStmt = pDbc.Prepare("SELECT * FROM t")
PRINT "Column count: ", pStmt.ColumnCount
' // Read the column names and values
DO
' // Fetch rows of the result set
IF pStmt.GetRow = SQLITE_DONE THEN EXIT DO
' // Read the columns and values
FOR i AS LONG = 0 TO pStmt.ColumnCount- 1
' // Get the value using the number of column...
PRINT pStmt.ColumnName(i)
PRINT pStmt.ColumnText(i)
' // ...or using the column name
PRINT pStmt.ColumnText("xyz")
NEXT
LOOP
PRINT
PRINT "Press any key..."
SLEEP
Memory database
' Binding
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx
' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
DIM pSql AS CSQLite = "sqlite3_32.dll"
print pSql.m_hLib
' // Create a new database in memory
' // I'm deleting and recreating the database for testing purposes
DIM pDbc AS CSQLiteDb = ":memory:"
' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
AfxMsg "Unable to create the table"
END
END IF
' // Prepare the statement
DIM sql AS CWSTR = "INSERT INTO t (xyz) VALUES (?)"
DIM pStmt AS CSqliteStmt = pDbc.Prepare(sql)
' // Bind the text
pStmt.BindText(1, "fruit")
' // Execute the prepared statement
pStmt.Step_
PRINT "Row id was", pDbc.LastInsertRowId
' // Prepare a query
pStmt.hStmt = pDbc.Prepare("SELECT * FROM t")
' // Read the value
pStmt.GetRow
PRINT pStmt.ColumnText("xyz")
PRINT
PRINT "Press any key..."
SLEEP
Bind Text
' Binding
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx
' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
DIM pSql AS CSQLite = "sqlite3_32.dll"
print pSql.m_hLib
' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "Test.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName
' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
AfxMsg "Unable to create the table"
END
END IF
' // Prepare the statement
DIM sql AS CWSTR = "INSERT INTO t (xyz) VALUES (?)"
DIM pStmt AS CSqliteStmt = pDbc.Prepare(sql)
' // Bind the text
pStmt.BindText(1, "fruit")
' // Execute the prepared statement
pStmt.Step_
PRINT "Row id was", pDbc.LastInsertRowId
' // Prepare a query
pStmt.hStmt = pDbc.Prepare("SELECT * FROM t")
' // Read the value
pStmt.GetRow
PRINT pStmt.ColumnText("xyz")
PRINT
PRINT "Press any key..."
SLEEP
Blob
' Blob
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx
' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
DIM pSql AS CSQLite = "sqlite3_32.dll"
print pSql.m_hLib
' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "TestBlob.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName
' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz blob)") <> SQLITE_DONE THEN
AfxMsg "Unable to create the table"
END
END IF
' // Prepare the statement
DIM sql AS CWSTR = "INSERT INTO t (xyz) VALUES (?)"
DIM pStmt AS CSqliteStmt = pDbc.Prepare(sql)
' // Bind the blob
DIM fakeBlob AS STRING
fakeBlob = STRING(500, "A")
pStmt.BindBlob(1, STRPTR(fakeBlob), 500, SQLITE_TRANSIENT)
' // Execute the prepared statement
pStmt.Step_
PRINT "Row id was", pDbc.LastInsertRowId
' // Open the blob
DIM pBlob AS CSQLiteBlob = pDbc.OpenBlob("main", "t", "xyz", 1)
DIM nBlobBytes AS LONG = pBlob.Bytes
PRINT "Blob bytes: ", nBlobBytes
' // Read the blob
DIM strBlob AS STRING
strBlob = STRING(nBlobBytes, CHR(0))
pBlob.Read(STRPTR(strBlob), nBlobBytes)
PRINT strBlob
PRINT
PRINT "Press any key..."
SLEEP
The purpose of my wrapper class was to ease somewhat the use of the SQLite API and, as Windows programmer, the use of Unicode, since Linuxers use UTF-8.
This is Awesome Josè !
I really need to get away from my old projects.
I have been learning the SQlite commands for a few days now, but I was still trying to make them work other than in the Database Browser I have.
I am going to start a Freebasic project and see how this goes with your examples.
If It does not go well, I will ask again.
Thanks a million.
Peter
For my needs I will probably never need an In Memory Database.
I just need to replace my flat files.
Therefore Queries, Insert into and Update of fields (which I must still try).
Your Class makes is WAY easier to understand.
I will update on the next challenge.
-Regards, Peter
Thanks, José, your examples will be a great help.
Josè, I have started to use the first example and it works perfectly.
Something i dont see is the "close" database command.
I assume it happens somewhere in your class, and I dont need to close it again upon exit?
-Peter
In Jose's code, when the class goes out of scope in your code then that class gets destroyed thereby calling the class's Destructor. In that Destructor, the CloseDB method is called thereby closing the database.
Why did I not learn this earlier?
That code makes it so simple!!
My first 4 Table Sqlite database in my life is running in the project and not a single flat file in sight.
Thanks gents.
-Peter
You should also notice that José's example uses a Destructor to finalize the prepared statement, which is fine, but you should be aware of it. Just in general:
1. Every database should be closed.
2. Every prepared statement should be finalized. Otherwise, it leaves breadcrumbs lying around that will get in your way the next time you open the database.
3. Every transaction should be ended. Otherwise, it will be rolled back as if it never happened.
4. By the way, get to know transactions. If you do a whole lot of updates to the database at one time, they go a lot faster if you wrap them in a transaction.
I learned all of these rules the hard way.
Phil
Thanks Phil
Yes, I have spend some time on the Sqlite site and read up on the commands. Also I use a DB tool that let you enter SQL code to test your transactions.
Once it works there, It will work in my program as well.
I dont make big software. Work tools that fills the gaps between what is supplied and what I really need. The previous version of the tool accumulated a LOT of customer details, especially Cellphone numbers and after 4 years started to become slow. (Although being a simple random access flat file never crashed or gave any issues)
There will also be a table for Item tracking numbers, their received dates, fees etc and that tends to grow greatly. Still need to figure out how to auto-delete anything older than six months in a database.
Baby steps, one at a time. I have made more progress the past week than the past 3 years. There will be blunders. I know.
Thanks for the help gents. Much appreciated.
-Peter
Quote from: philbar on August 06, 2022, 05:30:24 AM4. By the way, get to know transactions. If you do a whole lot of updates to the database at one time, they go a lot faster if you wrap them in a transaction.
Good point. I always wrap my updates/inserts in transactions (especially when adding bulk entries). The speed difference is considerable and the safety of using transactions gives you peace of mind that your data will fully committed to the database.
What do you consider 'large' volumes?
The tool I write will probably have 20 rows at the most on any query.
Would that need to be wrapped in a transaction?
It will auto-delete anything older than 6 months.
Using José's code in the example, how would you do that?
Updating a Listview from a large volume of data?
Sorry for all the questions, but I have started ROCK-BOTTOM again, and find this very interesting and with a new bit of zest for trying something new.
Regards, Peter
I don't think that Jose's class has specific Start/End transaction methods but it is super simple to simulate this by calling the sql using his Exec method.
pDbc.Exec("BEGIN IMMEDIATE TRANSACTION;")
'.... do your adding, updating, inserting, etc
pDbc.Exec("COMMIT TRANSACTION;")
It doesn't hurt to wrap all your calls in transactions. You will see speed improvements when adding bulk data to a table, say, in the hundreds or thousands. If you only have 20 records then you won't see much of a speed improvement. In your case, SQLite will implicitly create a transaction behind the scenes for EVERY ONE of your 20 additions. Therefore, the database is being locked and unlocked 20 times. If you wrap the whole code in BEGIN/COMMIT TRANSACTION then the database only has to do it once. It is the implicit transaction locking and unlocking that causes slow down on bulk data operations.... that is why I always wrap everything regardless.
Read through this page: https://www.sqlite.org/lang_transaction.html
That makes a lot of sense.
Will immediately start to do it that way.
-Regards, Peter
Now I understand what you meant with get to know transactions.
I have created a few infinite loops, crashes and spoke some undesirable language until I got the AHA! Moment from your example.
I also learned that Freebasic is not as forgiving as Powerbasic.
But serious progress had been made. Should have done this 15 years ago.
-Peter
A great deal of progress were made with your advice.
Tabs with multiple controls gets updated from a single Database call.
Something odd I noticed is your code on top of a new form.
'Application.Run(Frm Settings) Remove the following Application.Run code if it used elsewhere in your application.
Today, the application complained about this line as already included elsewhere. I could not find anywhere that it was mentioned before.
Also the dot syntax acts odd. When I type e.g. Settings.Listview2.Columns.AddThere would be a combobox pop-up with all the option I can choose. [Very Handy]
Just the next line it simply doesnt happen even if i repeat the previous line word for word.
After a 'save project' it works again.
I am enjoying myself again as things are starting to make sense again.
-Peter
'Application.Run(Frm Settings) Remove the following Application.Run code if it used elsewhere in your application.
There can be only one of these lines in the application because that is the code that starts the application. It is automatically added to every new form that you create because WinFBE does not know what form you will use as the startup form. You can even move that code a .bas file that you might be using as your startup code file. Make sure to check ALL of your form code files (even the tab control child forms) to see if that code exists elsewhere.
"combobox popup" codetips.
This is a known problem. It will be fixed when I do the work on the visual designer. The dot "." syntax is inconsistent in triggering the codetips.