Freebasic & Sqlite. Is there a need for a seperate thread?

[Petrus Vorster]

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

[SeaVipe]

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...

[José Roca]

I'm not an expert in SQLite. I only have a few examples that I wrote to test the CSQLite clases:

Basic steps

Code Select Expand

' 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


[José Roca]

Memory database

Code Select Expand

' 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


[José Roca]

Bind Text

Code Select Expand

' 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


[José Roca]

Blob

Code Select Expand

' 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


[José Roca]

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.

[Petrus Vorster]

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

[Petrus Vorster]

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

[SeaVipe]

Thanks, José, your examples will be a great help.

[Petrus Vorster]

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

[Paul Squires]

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.

[Petrus Vorster]

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

[philbar]

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

[Petrus Vorster]

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