An OxygenBasic sqlite3 database class based on Stan Durham's excellent PowerBASIC code.
File attached.
James
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'O2Bas sqlite3 database class based on PowerBASIC code by Stan Durham
'translated to O2Bas by James C. Fuller 9-28-2010
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
extern cdecl lib "sqlite3.dll"
long sqlite3_open(char*,sys);
long sqlite3_close(long);
char * sqlite3_errmsg (long);
char * sqlite3_libversion();
void sqlite3_free (char *);
void sqlite3_free_table(long);
int sqlite3_get_table(long,const char *,sys,sys,sys,sys);
end extern
'==============================================================================
% SQLITE_OK = 0
'==============================================================================
Class cSqliteDataBase
Method ctor()
Method dtor()
Method file()As String
Method file(byval f As String)
Method hDb() As Long
Method open()As bool
Method close()
Method errmsg()As String
Method sql() As String
Method sql(byval s As String)
Method query()As bool
Method freequery()
Method rowcount()As long
Method colcount()As long
Method first()As bool
Method last()As bool
Method next()As bool
Method previous()As bool
Method moveto(byval r As long)As bool
Method ColNum(byval s As String)As long
Method ColName(byval c As long)As string
Method get(byval c As string)As string
Method getat(byval c As long)As string
Method ColumnList(ByVal table As String) As String
Method TableList() As String
private
meFile As String
meDB As Long
meSQL As String
meTablePtr As Long
meColumns() As String
meRowCount As Long
meColCount As Long
meRowNum As Long
meFirstCol As Long
/
End Class
Methods of cSqliteDataBase
'------------------------------------------------------------------------------
Method ctor()
'Print "ctor"
End Method
'------------------------------------------------------------------------------
Method dtor()
'Print "dtor"
If meTablePtr Then
sqlite3_free_table meTablePtr
End If
End Method
'------------------------------------------------------------------------------
Method file()As String
Method = meFile
End Method
'------------------------------------------------------------------------------
Method file(byval f As String)
meFile = f
End Method
'------------------------------------------------------------------------------
Method hDb() As Long
Method = meDB
End Method
'------------------------------------------------------------------------------
Method open()As bool
If Len(meFile) Then
meDB = 0
If sqlite3_open(meFile, &meDB) = 0 Then
Method = true
Else
meDB = 0
End If
End If
End Method
'------------------------------------------------------------------------------
Method close()
If meDB Then
FreeQuery()
sqlite3_close(meDB)
meDB = 0
End If
End Method
'------------------------------------------------------------------------------
Method errmsg()As String
Dim szPtr As zstring Ptr
& szPtr = sqlite3_errmsg(meDB)
Method = szPtr
End Method
'------------------------------------------------------------------------------
Method sql() As String
Method = meSQL
End Method
'------------------------------------------------------------------------------
Method sql(byval s As String)
meSQL = s
End Method
'------------------------------------------------------------------------------
Method query() As bool
Dim As long lpTable,RowCount, ColCount,rv,lpError
rv = false
If meRowCount Then
FreeQuery()
End If
If meDB And Len(meSQL) Then
If sqlite3_get_table(meDb, *meSQL, &lpTable, &RowCount, &ColCount, &lpError)= SQLITE_OK Then
If RowCount = 0 Then
rv = true 'command succeed - no return
sqlite3_free_table lpTable
meTablePtr = 0
Else
meTablePtr = lpTable
meRowCount = RowCount
meColCount = ColCount
rv = true
End If
End If
End If
sqlite3_free(lpError)
Method = rv
End Method
'------------------------------------------------------------------------------
Method freequery()
If meRowCount Then
sqlite3_free_table meTablePtr
meTablePtr = 0
meRowCount = 0
meColCount = 0
meRowNum = 0
meFirstCol = 0
End If
End Method
'------------------------------------------------------------------------------
Method rowcount()As long
Method = meRowCount
End Method
'------------------------------------------------------------------------------
Method colcount()As long
Method = meColCount
End Method
'------------------------------------------------------------------------------
Method first()As bool
'move to first row in query results
'True/False success
If meRowCount Then
meRowNum = 1
meFirstCol = meRowNum * meColCount
Method = true
End If
End Method
'------------------------------------------------------------------------------
Method Last() As bool
'move to last row in query results
'True/False success
If meRowCount Then
meRowNum = meRowCount
meFirstCol = meRowNum * meColCount
Method = true
End If
End Method
'------------------------------------------------------------------------------
Method Next() As bool
'move to next row in query results
'True/False success
If meRowCount Then
meRowNum += 1
If (meRowNum > 0) And (meRowNum <= meRowCount) Then
meFirstCol = meRowNum * meColCount
Method = true
End If
End If
End Method
'------------------------------------------------------------------------------
Method Previous() As bool
'move to previous row in query results
'True/False success
If meRowCount Then
meRowNum -= 1
If (meRowNum > 0) And (meRowNum <= meRowCount) Then
meFirstCol = meRowNum * meColCount
Method = true
End If
End If
End Method
'------------------------------------------------------------------------------
Method moveto(byval row As long)As bool
'move to row in query results
'query results = one-based index
'True/False success
If (meRowCount And row > 0) And (row <= meRowCount) Then
meRowNum = row
meFirstCol = meRowNum * meColCount
Method = true
End If
End Method
'------------------------------------------------------------------------------
Method ColNum(byval sCol As String) As Long
Dim pTable As Long Ptr
Dim pzStr As zstring Ptr
Dim s As String
Dim i As Long
If meColCount < 1 Then
Method = 0
Exit Method
End If
& pTable = meTablePtr
For i = 1 To meColCount
& pzStr = pTable[i]
s = pzStr
If s = sCol Then
Method = i
Exit Method
End If
Next i
End Method
'------------------------------------------------------------------------------
Method ColName(Byval cn As long) As string
Dim pTable As Long Ptr
Dim pzStr As zstring Ptr
If (cn > 0) And (cn <= meColCount) Then
& pTable = meTablePtr
& pzStr = pTable[cn]
Method = pzStr
Exit Method
End If
End Method
'------------------------------------------------------------------------------
Method get(byval scol As String) As string
Dim pTable As Long Ptr
Dim pzStr As zstring Ptr
Dim As Long cn,ndx
If (meRowNum > 0) And (meRowNum <= meRowCount) Then
cn = ColNum(scol)
ndx = meFirstCol + cn
& pTable = meTablePtr
& pzStr = pTable[ndx]
Method = pzStr
End If
End Method
'------------------------------------------------------------------------------
Method getat(byval cn As long) As string
Dim pTable As Long Ptr
Dim pzStr As zstring Ptr
Dim As Long ndx
If (meRowNum > 0) And (meRowNum <= meRowCount) Then
If (cn > 0) And (cn <= meColCount) Then
ndx = meFirstCol + cn
& pTable = meTablePtr
& pzStr = pTable[ndx]
Method = pzStr
End If
End If
End Method
'------------------------------------------------------------------------------
Method ColumnList(ByVal table As String) As String
'get list of columns (and column info) for Table
'each column's info separated by \n
'column info separated by ","
' ColName,
' ColType,
' NotNull,
' DefaultValue
'ColName1,ColType,NotNull,DefaultValue
'ColName2,ColType,NotNull,DefaultValue
local string s
bool ok
FreeQuery()
meSQL = "PRAGMA table_info(["+table+"])"
ok = Query()
ok = First()
s=""
While ok
s+= GetAt(2) +" "+ GetAt(3) +" "+ GetAt(4)+" "
If Len(GetAt(5)) Then
'Print "Yes"
s+=GetAt(5)+chr(10)
Else
s+=" "+chr(10)
End If
ok = This.Next()
Wend
Method = s
End Method
'------------------------------------------------------------------------------
Method TableList() As String
'get list of tables in database:
'Table1'
'Table2'
'Table3,...
local string s
local bool ok
FreeQuery()
meSQL = "select name from sqlite_master where type = 'table'"
ok = Query()
ok = First()
s=""
While ok
s += GetAt(1) + chr(10)
ok = This.Next()
Wend
Method = s
End Method
End Methods
'==============================================================================
Demo
#basic
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
dim kernel32 As long
kernel32=LoadLibrary `kernel32.dll`
bind kernel32
(
DeleteFile DeleteFileA ; @4
)
' macros
'**********************************************************************
def new
dim as %1 byref %2
&%2 = news sizeof %1
%2.ctor()
end def
'------------------------------------------------------------------------------
def delete
%1.dtor()
frees &%1
end def
'***********************************************************************
' constants
'**********************************************************************
def true 1
def false 0
#include "SQLCLS_04.o2bas"
Sub O2Main()
local string s
local long rv,i,j,col,row,rc,cc
Print "SQLite Version: " sqlite3_libversion()
'start with a new database
DeleteFile("jcf99.sdb")
'create instance of cSqliteDataBase class
new cSqliteDataBase oDb
'set database file name
oDb.file = "jcf99.sdb"
'Open database. rv returns zero on failure
rv = oDb.Open()
If rv Then
'Create a table
oDb.SQL = "CREATE TABLE demo(someval INTEGER, sometxt TEXT);"
rv = oDb.Query()
If rv Then
'Add data to table
oDb.SQL = "begin"
oDb.Query()
for i = 1 to 5
oDb.Sql = "insert into demo(someval,sometxt) values("+str(i+7)+",'ab"+chr(i+64)+"')"
rv = oDb.Query()
If rv = 0 Then
Exit loop
End If
next i
oDb.Sql = "commit"
oDb.Query()
Else
Print oDb.ErrMsg()
goto cleanexit
End If
'Query the demo table for all it's records
oDb.SQL = "select rowid,* from demo"
rv = oDb.Query()
If rv Then
rc = oDb.RowCount
cc = oDb.ColCount
'move to the first row of data
oDb.First()
'This implementation is not written to return col names so I added them here for display
s = "Get result set using Column Number"+chr(10)+chr(10)
s += "rowid"+chr(9)+"someval"+chr(9)+"sometxt"+chr(10)
'Get the data using 'GetAt(col #)
For Row = 1 To rc
For Col = 1 To cc
s+=oDb.GetAt(Col)
If Col != cc Then
s+=chr(9)
End If
Next Col
s+= chr(10)
'move to the next result row
oDb.Next()
Next Row
Print s
'now get data using col names
oDb.First()
s = "Get result set using Column Names"+chr(10)+chr(10)
s += "rowid"+chr(9)+"someval"+chr(9)+"sometxt"+chr(10)
For Row = 1 To rc
For Col = 1 To cc
Select Col
case 1
s+=oDb.Get("rowid")
case 2
s+=oDb.Get("someval")
case 3
s+=oDb.Get("sometxt")
End Select
If Col != cc Then
s+=chr(9)
End If
Next Col
s+=chr(10)
oDb.Next()
Next Row
Print s
Else
s = oDb.ErrMsg()
Print s
goto cleanexit
End If
'Get Column Information for demo table
oDb.FreeQuery()
s = "Column Info for demo table" + chr(10)+ chr(10)
s+= "'Col Name' 'Col Type' 'Not Null' 'Default Value'"+chr(10)+chr(10)
s+= oDb.ColumnList("demo")
Print s
'Get Table List
s ="Table List of jcf99.sdb"+chr(10)+chr(10)
s+=oDb.TableList()
Print s
Else
Print "No Db"
End If
cleanexit:
oDb.Close()
delete oDb
End Sub
O2Main()
.
