I do not have much need to handle BLOB data so I have not fully completed those functions. I will update this code should I have a need for BLOBs in the future.
clsSQLite3.bas (this is the actual class)
Code: Select all
''
''
'' SQLite3 class (Public Domain code - enjoy).
'' Paul Squires of PlanetSquires Software (August 2015)
'' A simple class interface to the SQLite3 database DLL.
''
'' Compiler: FreeBASIC 1.03 (32-bit)
''
#Include Once "windows.bi"
#Include Once "sqlite3.bi"
Type clsSqlite
Private:
m_dbHandle As sqlite3 Ptr ' Database connection handle
m_LastError As Integer ' Current error code
Public:
Declare Destructor
Declare Function Version() As String
Declare Function SourceID() As String
Declare Function VersionNumber() As Integer
Declare Property dbHandle() As sqlite3 Ptr
Declare Property dbHandle( nValue As sqlite3 Ptr )
Declare Property LastError() As Integer
Declare Property LastError( nValue As Integer )
Declare Function OpenDatabase( DBName As String ) As Integer
Declare Function CloseDatabase() As Integer
Declare Function SqlExec( ByVal sql As String ) As Integer
Declare Function SafeSql( ByVal sql As String ) As String
Declare Sub StartTransaction()
Declare Sub EndTransaction()
End Type
Type clsSqliteQuery
Private:
m_hStmt As sqlite3_stmt Ptr
m_LastError As Integer
Public:
Declare Destructor
Declare Function GetRow() As BOOLEAN
Declare Function PrepareQuery( ByRef db As clsSqlite, ByRef sSQL As String ) As Integer
Declare Property hStmt() As sqlite3_stmt Ptr
Declare Property hStmt( nValue As sqlite3_stmt Ptr )
Declare Property LastError() As Integer
Declare Property LastError( nValue As Integer )
Declare Function ColumnCount() As Integer
Declare Function ColumnName( ByVal nCol As Integer ) As String
Declare Function ColumnType( ByVal nCol As Integer ) As Integer
Declare Function ColumnDeclType( ByVal nCol As Integer ) As String
Declare Function GetText Overload ( ByVal nCol As Integer ) As String
Declare Function GetText Overload ( ByVal sColName As String ) As String
Declare Function GetColIndex( ByVal sColName As String ) As Integer
Declare Function FinalizeQuery() As Integer
Declare Function ResetQuery() As Integer
Declare Function BindQuery Overload ( ByRef nPos As Integer, ByRef nInteger As Integer) As Integer
Declare Function BindQuery Overload ( ByRef nPos As Integer, ByRef nInt64 As sqlite3_int64) As Integer
Declare Function BindQuery Overload ( ByRef nPos As Integer, ByRef nDouble As Double) As Integer
Declare Function BindQuery Overload ( ByRef nPos As Integer ) As Integer ' to bind a NULL
Declare Function BindQuery Overload ( ByRef nPos As Integer, ByVal sText As Const ZString Ptr) As Integer
Declare Function BindQueryBlob( ByRef nPos As Integer, ByVal pBinary As Any Ptr, ByVal nLength As Integer) As Integer
End Type
'' ---------------------------------------------------------------------
'' clsSQLITE
'' ---------------------------------------------------------------------
''
'' Close any open database when object is destroyed
''
Destructor clsSqlite
this.CloseDatabase
End Destructor
''
'' Returns the SQLite3 version string.
''
Function clsSqlite.Version() As String
Dim psz As Const ZString Ptr
psz = sqlite3_libversion
If psz Then Function = *psz
End Function
''
'' Returns the SQLite3 version number.
''
Function clsSqlite.VersionNumber() As Integer
Function = sqlite3_libversion_number()
End Function
''
'' Returns the SQLite3 source ID string.
''
Function clsSqlite.SourceID() As String
Dim psz As Const ZString Ptr
psz = sqlite3_sourceid
If psz Then Function = *psz
End Function
''
'' dbHandle (Property - handle to the open database)
''
Property clsSqlite.dbHandle() As sqlite3 Ptr
Property = this.m_dbHandle
End Property
Property clsSqlite.dbHandle( nValue As sqlite3 Ptr )
this.m_dbHandle = nValue
End Property
''
'' LastError (Property - error code from most recent sqlite function call)
''
Property clsSqlite.LastError() As Integer
Property = this.m_LastError
End Property
Property clsSqlite.LastError( nValue As Integer )
this.m_LastError = nValue
End Property
''
'' Opens database and returns error code (if any)
''
Function clsSqlite.OpenDatabase( ByRef DBName As String ) As Integer
Dim rc As Integer = sqlite3_open( DBName, @m_dbHandle)
this.LastError = rc
If (rc <> SQLITE_OK) Then
this.CloseDatabase
End If
Function = rc
End Function
''
'' Close active database
''
Function clsSqlite.CloseDatabase() As Integer
Dim rc As Integer = sqlite3_close(m_dbHandle)
this.LastError = rc
this.dbHandle = 0
Function = rc
End Function
''
'' Run zero or more semicolon separated SQL statements.
''
Function clsSqlite.SqlExec( ByVal sql As String ) As Integer
Dim errmsg As ZString Ptr
this.LastError = sqlite3_exec(this.dbHandle, sql, 0, 0, @errmsg)
sqlite3_free(errmsg)
Function = this.LastError
End Function
''
'' Safely escape embedded quotes within an sql statement
''
Function clsSqlite.SafeSql( ByVal sql As String ) As String
If Len(sql) = 0 Then Exit Function ' prevents mprintf from returning "(NULL)" literal string
Dim pzSql As ZString Ptr
pzSql = sqlite3_mprintf("%q", sql)
Function = *pzSql
sqlite3_free(pzSql)
End Function
''
'' Start/End Transaction (inserts much faster into database)
''
Sub clsSqlite.StartTransaction()
this.SqlExec( "BEGIN IMMEDIATE TRANSACTION;" )
End Sub
Sub clsSqlite.EndTransaction()
this.SqlExec( "COMMIT TRANSACTION;" )
End Sub
'' ---------------------------------------------------------------------
'' clsSQLITEQUERY
'' ---------------------------------------------------------------------
''
'' Close any open query conection when the object is destroyed
''
Destructor clsSqliteQuery
sqlite3_finalize(this.hstmt)
End Destructor
''
'' Prepare statement to execute (mostly SELECT queries) and step through the result set
''
Function clsSqliteQuery.PrepareQuery( ByRef db As clsSqlite, ByRef sSQL As String ) As Integer
this.LastError = sqlite3_prepare_v2( db.dbHandle, sSQL, Len(sSQL), @m_hStmt, 0 )
Function = this.LastError
End Function
''
'' Get the next row in the data set. Return TRUE if valid row returned, FALSE if no more records.
''
Function clsSqliteQuery.GetRow() As BOOLEAN
this.LastError = sqlite3_step(this.hStmt)
If this.LastError = SQLITE_ROW Then
Function = True
Else
Function = False
End If
End Function
''
'' hStmt (Property - handle to the compile statement)
''
Property clsSqliteQuery.hStmt() As sqlite3_stmt Ptr
Property = this.m_hStmt
End Property
Property clsSqliteQuery.hStmt( nValue As sqlite3_stmt Ptr )
this.m_hStmt = nValue
End Property
''
'' LastError (Property - error code from most recent sqlite function call)
''
Property clsSqliteQuery.LastError() As Integer
Property = this.m_LastError
End Property
Property clsSqliteQuery.LastError( nValue As Integer )
this.m_LastError = nValue
End Property
''
'' ColumnCount (Return the number of columns in the recordset)
''
Function clsSqliteQuery.ColumnCount() As Integer
Function = sqlite3_column_count( this.hStmt )
End Function
''
'' ColumnName (Return the column name based on ordinal position in recordset)
''
Function clsSqliteQuery.ColumnName( ByVal nCol As Integer ) As String
Function = *sqlite3_column_name( this.hStmt, nCol )
End Function
''
'' ColumnType (Return the column type based on ordinal position in recordset)
''
Function clsSqliteQuery.ColumnType( ByVal nCol As Integer ) As Integer
Function = sqlite3_column_type( this.hStmt, nCol )
' SQLITE_INTEGER 1
' SQLITE_FLOAT 2
' SQLITE_TEXT 3
' SQLITE_BLOB 4
' SQLITE_NULL 5
End Function
''
'' ColumnDeclType (Return the column declared type (from TABLE) based on ordinal position in recordset)
''
Function clsSqliteQuery.ColumnDeclType( ByVal nCol As Integer ) As String
Function = *sqlite3_column_decltype( this.hStmt, nCol )
End Function
''
'' GetText (Return the column text value based on ordinal position in recordset)
''
Function clsSqliteQuery.GetText Overload ( ByVal nCol As Integer ) As String
Function = *Cast(ZString Ptr, sqlite3_column_text( this.hStmt, nCol ))
End Function
''
'' GetText (Return the column text value based on column name in recordset)
''
Function clsSqliteQuery.GetText Overload ( ByVal sColName As String ) As String
Dim nCol As Integer = this.GetColIndex( sColName )
Function = *Cast(ZString Ptr, sqlite3_column_text( this.hStmt, nCol ))
End Function
''
'' (Private) GetColIndex (Return column position based on column name)
''
Function clsSqliteQuery.GetColIndex( ByVal sColName As String ) As Integer
Dim NumCols As Integer = this.ColumnCount
Dim i As Integer
sColName = Ucase(sColName)
For i = 0 To NumCols - 1
If Ucase(this.ColumnName(i)) = sColName Then
Function = i: Exit Function
End If
Next
End Function
''
'' Finalize a statement connection and release resources
''
Function clsSqliteQuery.FinalizeQuery() As Integer
this.m_LastError = sqlite3_finalize(this.hStmt)
this.hStmt = 0
Function = this.m_LastError
End Function
''
'' Reset a query connection so it can be reused without recompilation
''
Function clsSqliteQuery.ResetQuery() As Integer
this.m_LastError = sqlite3_reset(this.hStmt)
Function = this.m_LastError
End Function
''
'' Bind Reset a query connection so it can be reused without recompilation
''
Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Integer, ByRef nInteger As Integer) As Integer
this.m_LastError = sqlite3_bind_int(this.hStmt, nPos, nInteger)
Function = this.m_LastError
End Function
Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Integer, ByRef nInt64 As sqlite3_int64) As Integer
this.m_LastError = sqlite3_bind_int64(this.hStmt, nPos, nInt64)
Function = this.m_LastError
End Function
Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Integer, ByRef nDouble As Double) As Integer
this.m_LastError = sqlite3_bind_double(this.hStmt, nPos, nDouble)
Function = this.m_LastError
End Function
Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Integer ) As Integer
this.m_LastError = sqlite3_bind_null(this.hStmt, nPos)
Function = this.m_LastError
End Function
Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Integer, ByVal sText As Const ZString Ptr) As Integer
this.m_LastError = sqlite3_bind_text(this.hStmt, nPos, sText, -1, SQLITE_TRANSIENT)
Function = this.m_LastError
End Function
Function clsSqliteQuery.BindQueryBlob( ByRef nPos As Integer, ByVal pBinary As Any Ptr, ByVal nLength As Integer) As Integer
this.m_LastError = sqlite3_bind_blob(this.hStmt, nPos, pBinary, nLength, SQLITE_TRANSIENT)
Function = this.m_LastError
End Function
test.bas (This is a simple program to test the functions in the class)
Code: Select all
#Include Once "clsSqlite3.bas"
' //
' // Create an instance of our sqlite database class and query class
' //
Dim db As clsSqlite
Dim q As clsSqliteQuery
Dim sql As String
Dim i As Integer
Dim sFilename As String = "testdb.db3" ' use ":memory:" for an in-memory only database
? db.Version
? db.VersionNumber
? db.SourceId
' Delete any existing database
Kill sFilename
' //
' // Open/create the database
' //
? "Open: "; db.OpenDatabase( sFilename )
' //
' // Create a table
' //
' TEXT
' NUMERIC
' INTEGER
' REAL
' BLOB
Sql = "CREATE TABLE employees( firstname TEXT, lastname TEXT, salary REAL )"
db.SqlExec(sql)
? "Create Table LastError: "; db.LastError
' //
' // Insert some data
' //
db.StartTransaction
For i = 1 To 10
sql = "INSERT INTO employees (firstname, lastname, salary)" & _
" VALUES( '" & db.SafeSql("Paul") & "'," & _
" '" & db.SafeSql("Squires") & "'," & _
" " & Str(150000+(i*1000)) & ");"
db.SqlExec(sql)
Next
db.EndTransaction
' //
' // Insert some data using binding and reusing the same compiled query
' //
sql = "INSERT INTO employees( firstname, lastname, salary) VALUES( ?,?,?);"
q.PrepareQuery( db, sql )
db.StartTransaction
For i = 1 To 10
q.BindQuery( 1, "Richard" )
q.BindQuery( 2, "Hipp" )
q.BindQuery( 3, 200000+(i*10000) )
q.GetRow()
q.ResetQuery
Next
db.EndTransaction
q.FinalizeQuery ' finish with this query so destroy its resources
' //
' // Perform a query and display the results
' //
sql = "SELECT * FROM employees;"
q.PrepareQuery( db, sql )
? "Statement column count: "; q.ColumnCount
Dim nRec As Integer
Do While q.GetRow()
nRec = nRec + 1
? "Display data for record #: "; nRec
For i = 0 To q.ColumnCount - 1
? "Col:"; i; _
" Name: "; q.ColumnName(i); _
" Type: "; q.ColumnType(i); _
" DeclType: "; q.ColumnDeclType(i); _
" VALUE = "; q.GetText(i) ' we could also used named fields like q.GetText("firstname")
Next
Loop
q.FinalizeQuery ' finish with this query so destroy its resources
' //
' //
? "Close: "; db.CloseDatabase()
Sleep
Thanks!
Paul