SQLite3 database class (32-bit)

Post your FreeBASIC tips and tricks here. Please don’t post your code without including an explanation.
PaulSquires
Posts: 746
Joined: Jul 14, 2005 23:41
Contact:

SQLite3 database class (32-bit)

Postby PaulSquires » Aug 16, 2015 0:47

I am posting here a simple to use SQLite3 class to help the programmer interface to the wonderful SQLite3 relational database. Visit http://www.sqlite.org for more information about the database. You will need to download the actual SQLite3 DLL and put it in the same folder as your application. Download from here: http://www.sqlite.org/download.html

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
                         


If you make any modifications to this class then it is appreciated if you post them here so that the class can grow and become even more useful.

Thanks!
Paul
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Feb 08, 2016 23:26

Thank you Paul. I'm writing this program on Windows also. Your answer appears to be more of how I thought this would work.

Bill
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Feb 17, 2016 23:26

Paul, I compiled and ran your TEST.BAS and it compiled and ran fine. However when I added the

Code: Select all

#Include Once "clsSqlite3.bas"


to my program and tried to compile, this is what I got:

Code: Select all

FreeBASIC Compiler - Version 1.01.0 (12-28-2014), built for win32 (32bit)
Copyright (C) 2004-2014 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(92) error 41: Variable not declared, sqlite3_api in 'psz = sqlite3_libversion'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(92) warning 4(1): Suspicious pointer assignment
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(101) error 28: Expected pointer, before '->' in 'Function = sqlite3_libversion_number()'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(110) error 28: Expected pointer, before '->' in 'psz = sqlite3_sourceid'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(110) warning 4(1): Suspicious pointer assignment
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(143) error 28: Expected pointer, before '->' in 'Dim rc As Integer = sqlite3_open( DBName, @m_dbHandle)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(156) error 28: Expected pointer, before '->' in 'Dim rc As Integer = sqlite3_close(m_dbHandle)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(168) error 28: Expected pointer, before '->' in 'this.LastError = sqlite3_exec(this.dbHandle, sql, 0, 0, @errmsg)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(169) error 28: Expected pointer, before '->' in 'sqlite3_free(errmsg)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(180) error 28: Expected pointer, before '->' in 'pzSql = sqlite3_mprintf("%q", sql)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(180) warning 4(1): Suspicious pointer assignment
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(182) error 28: Expected pointer, before '->' in 'sqlite3_free(pzSql)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(208) error 28: Expected pointer, before '->' in 'sqlite3_finalize(this.hstmt)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bas(208) error 132: Too many errors, exiting


No other changes. Any ideas? I like your suggestion as it gives very good concrete examples of how to use it.

Bill
PaulSquires
Posts: 746
Joined: Jul 14, 2005 23:41
Contact:

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Feb 18, 2016 19:21

Hi Bill, I can't think of anything special that would cause the problems you are seeing other than make sure that these lines are present at the top of your clsSqlite3.bas file:

#Include Once "windows.bi"
#Include Once "sqlite3.bi"

Seems strange that you can compile the test program but not be able to include the class in your program.
MOD
Posts: 554
Joined: Jun 11, 2009 20:15

Re: SQLite3 database class (32-bit)

Postby MOD » Feb 21, 2016 11:36

mdTypes also offers a feature like that, and also uses the same interfaces for MySQL and a simple file based data format: mdTypes persistence api tutorial
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Feb 22, 2016 21:47

Hi Bill, I can't think of anything special that would cause the problems you are seeing other than make sure that these lines are present at the top of your clsSqlite3.bas file:

#Include Once "windows.bi"
#Include Once "sqlite3.bi"

Seems strange that you can compile the test program but not be able to include the class in your program.


Hi Paul. I was also curious about being able to compile and run the test problem and then running into this problem.

Here is the beginning lines of my .BI file:

Code: Select all

' ********************
'   ptbcxxxx.bi - this file contains all #defines, #includes, SUB and FUNCTION declarations,
'               global variables, for the specific interface program,
'               in this case - console BASIC
' ********************
' version and change log
'       x1.x.x - first version
' ********************

' *****************************
'   includes
' *****************************

#INCLUDE ONCE "C:\Users\Public\Documents\Documents\src\util\BASIC\freeBASIC\21.1\console.bi"
#INCLUDE ONCE "..\..\common\pttdx11x.bi"     ' this is all type def's
#INCLUDE ONCE "..\..\common\pt__x21x.bi"     ' all interface independent routines
REM     #INCLUDE ONCE "c:\freebasic-1.01.0-win32\inc\sqlite3.bi"      ' for SQLite functions
REM     #INCLUDE ONCE "c:\freebasic-1.01.0-win32\inc\sqlite3ext.bi"

#INCLUDE ONCE "c:\freebasic-1.05.0-win32\inc\sqlite3.bi"      ' for SQLite functions
#INCLUDE ONCE "c:\freebasic-1.05.0-win32\inc\sqlite3ext.bi"

#INCLUDE ONCE "clsSqlite3.bas"          ' class wrapper for SQLite

' *****************************
'   defines
' *****************************
#IFNDEF dfPI
    #DEFINE dfPI 3.1415927
#ENDIF

#IFNDEF dfTRQ_HP
    #DEFINE dfTRQ_HP 5252.1311
#ENDIF


Code: Select all

'' clsSQLite3.bas (this is the actual class)
''
''
''   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"


As you can see, the includes are in place. I've been wondering if I should rename "clsSQLite3.bas" to "clsSQLite3.bi" to see if that would make a difference. Let me know if you have any other thoughts on this. Thanks again.

BIll
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Feb 22, 2016 21:54

by MOD » Feb 21, 2016 11:36
mdTypes also offers a feature like that, and also uses the same interfaces for MySQL and a simple file based data format: mdTypes persistence api tutorial


Mod, I am completely unfamiliar with mdTypes. Can you point me to an English based site for more info????

Bill
MOD
Posts: 554
Joined: Jun 11, 2009 20:15

Re: SQLite3 database class (32-bit)

Postby MOD » Feb 23, 2016 19:19

The download contains a lot of examples and test codes and the linked tutorial is hosted by a german site but is written completely in english.
dbickin
Posts: 39
Joined: Aug 03, 2005 16:40

Re: SQLite3 database class (32-bit)

Postby dbickin » Feb 24, 2016 14:19

Paul,
Probably something obvious I am not seeing, but why is there a dependency on windows.bi?

And is there a reason you did not implement calls to sqlite3_column_int and sqlite3_column_double?

Thanks,
David
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Feb 24, 2016 18:58

by MOD » Feb 23, 2016 19:19
The download contains a lot of examples and test codes and the linked tutorial is hosted by a german site but is written completely in english.


I did take a look at the site. Call me old fashioned but I personally prefer a .PDF file to learn something. Having to keep a browser open for a tutorial is anathema to me. Is there such a downloadable file available?

by dbickin » Feb 24, 2016 14:19
Paul,
Probably something obvious I am not seeing, but why is there a dependency on windows.bi?

And is there a reason you did not implement calls to sqlite3_column_int and sqlite3_column_double?


Not really sure what you're talking about. I think you want to direct this to Paul, who is the author of the code in clsSqlite3.bas file:

Bill
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Feb 24, 2016 23:26

Well, had to try something. First off, I renamed "clsSqlite3.bas" to "clsSqlite3.bi" Still got the same error msg:

Code: Select all

FreeBASIC Compiler - Version 1.05.0 (01-31-2016), built for win32 (32bit)
Copyright (C) 2004-2016 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(92) error 41: Variable not declared, sqlite3_api in 'psz = sqlite3_libversion'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(101) error 9: Expected expression, found 'sqlite3_api' in 'Function = sqlite3_libversion_number()'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(102) warning 13(0): Function result was not explicitly set
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(110) error 9: Expected expression, found 'sqlite3_api' in 'psz = sqlite3_sourceid'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(143) error 9: Expected expression, found 'sqlite3_api' in 'Dim rc As Integer = sqlite3_open( DBName, @m_dbHandle)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(156) error 9: Expected expression, found 'sqlite3_api' in 'Dim rc As Integer = sqlite3_close(m_dbHandle)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(168) error 98: No matching overloaded function, LASTERROR() in 'this.LastError = sqlite3_exec(this.dbHandle, sql, 0, 0, @errmsg)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(169) error 3: Expected End-of-Line, found 'sqlite3_api' in 'sqlite3_free(errmsg)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(180) error 9: Expected expression, found 'sqlite3_api' in 'pzSql = sqlite3_mprintf("%q", sql)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(182) error 3: Expected End-of-Line, found 'sqlite3_api' in 'sqlite3_free(pzSql)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(208) error 3: Expected End-of-Line, found 'sqlite3_api' in 'sqlite3_finalize(this.hstmt)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(208) error 132: Too many errors, exiting


Next, tried copying the "windows.bi" and "sqlite3.bi" from clsSqlite3.bas to my file,

Code: Select all

' ********************
'   ptbcxxxx.bi - this file contains all #defines, #includes, SUB and FUNCTION declarations,
'               global variables, for the specific interface program,
'               in this case - console BASIC
' ********************
' *****************************
'   includes
' *****************************

#INCLUDE ONCE "C:\Users\Public\Documents\Documents\src\util\BASIC\freeBASIC\21.1\console.bi"
#INCLUDE ONCE "..\..\common\pttdx11x.bi"     ' this is all type def's
#INCLUDE ONCE "..\..\common\pt__x21x.bi"     ' all interface independent routines
REM     #INCLUDE ONCE "c:\freebasic-1.01.0-win32\inc\sqlite3.bi"      ' for SQLite functions
REM     #INCLUDE ONCE "c:\freebasic-1.01.0-win32\inc\sqlite3ext.bi"

#INCLUDE ONCE "c:\freebasic-1.05.0-win32\inc\sqlite3.bi"      ' for SQLite functions
#INCLUDE ONCE "c:\freebasic-1.05.0-win32\inc\sqlite3ext.bi"

#INCLUDE ONCE "clsSqlite3.bas"          ' class wrapper for SQLite
REM     #INCLUDE ONCE "clsSqlite3.bi"          ' class wrapper for SQLite
REM     #Include Once "windows.bi"
REM     #Include Once "sqlite3.bi"


Still same error msg. I know in the past, I've made the error of not putting my INCLUDES in the correct order but I can't see that happening here.

Any other suggestions would be appreciated.

Bill
PaulSquires
Posts: 746
Joined: Jul 14, 2005 23:41
Contact:

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Feb 25, 2016 0:58

dbickin wrote:Paul,
Probably something obvious I am not seeing, but why is there a dependency on windows.bi?

And is there a reason you did not implement calls to sqlite3_column_int and sqlite3_column_double?

Thanks,
David


Hi David, yes, looks like you are correct. You can compile without the windows.bi include.
PaulSquires
Posts: 746
Joined: Jul 14, 2005 23:41
Contact:

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Feb 25, 2016 1:13

TriumphRacer wrote:

Code: Select all

FreeBASIC Compiler - Version 1.05.0 (01-31-2016), built for win32 (32bit)
Copyright (C) 2004-2016 The FreeBASIC development team.
standalone
target:       win32, 486, 32bit
compiling:    ptbcx3xx-sql.bas -o ptbcx3xx-sql.asm (main module)
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(92) error 41: Variable not declared, sqlite3_api in 'psz = sqlite3_libversion'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(101) error 9: Expected expression, found 'sqlite3_api' in 'Function = sqlite3_libversion_number()'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(102) warning 13(0): Function result was not explicitly set
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(110) error 9: Expected expression, found 'sqlite3_api' in 'psz = sqlite3_sourceid'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(143) error 9: Expected expression, found 'sqlite3_api' in 'Dim rc As Integer = sqlite3_open( DBName, @m_dbHandle)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(156) error 9: Expected expression, found 'sqlite3_api' in 'Dim rc As Integer = sqlite3_close(m_dbHandle)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(168) error 98: No matching overloaded function, LASTERROR() in 'this.LastError = sqlite3_exec(this.dbHandle, sql, 0, 0, @errmsg)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(169) error 3: Expected End-of-Line, found 'sqlite3_api' in 'sqlite3_free(errmsg)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(180) error 9: Expected expression, found 'sqlite3_api' in 'pzSql = sqlite3_mprintf("%q", sql)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(182) error 3: Expected End-of-Line, found 'sqlite3_api' in 'sqlite3_free(pzSql)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(208) error 3: Expected End-of-Line, found 'sqlite3_api' in 'sqlite3_finalize(this.hstmt)'
C:\Users\Public\Documents\Documents\src\perftune\console\freeBASIC-SQL\clsSqlite3.bi(208) error 132: Too many errors, exiting



Hi Bill,

I was a little confused by all those "sqlite3_api" errors being reported because my code did not produce such types of errors. I searched the includes and found that it is the "sqlite3ext.bi" file that produces those errors. When you include that file, the errors appear.

#Include Once "sqlite3ext.bi"

I expect that if you remove all occurrences of the above include then your errors will go away?
TriumphRacer
Posts: 164
Joined: Aug 28, 2005 21:06
Location: irwin, pa
Contact:

Re: SQLite3 database class (32-bit)

Postby TriumphRacer » Feb 26, 2016 15:30

Thanks Paul. I'll try that today. I also meant to ask you what version of SQLite you used. I believe I'm using 3.8.3.1.

Bill
PaulSquires
Posts: 746
Joined: Jul 14, 2005 23:41
Contact:

Re: SQLite3 database class (32-bit)

Postby PaulSquires » Feb 26, 2016 15:37

Hi Bill, hope it works for you!

The SQLite dll that I tested my code with is Version 3.8.11.1 dated 2015-07-30 (665KB).

Return to “Tips and Tricks”

Who is online

Users browsing this forum: Google [Bot] and 2 guests