Restored from previous forum. Originally posted by ebs.
Well, after a day of work, here are PB wrapper functions to use with the SQLite database DLL (http://www.hwaci.com/sw/sqlite/).
Since the Windows DLL uses the CDECL calling convention, I had to pop the calling parameters off the stack manually. Make sure that you have "Enable Inline ASM Support" checked. For speed reasons, I wanted to use CallFunctionFast() instead of CallFunction(), but I couldn't make it work.
I also attached the SQLite DLL to the PB executable and extracted it at run-time. This isn't absolutely necessary, but I like the idea of having everything in one executable, with no external DLLs.
Fred: I continue to be amazed that you have created such an incredible product! It's hard to believe that I can provide an entire SQL database application in one executable under 200KB, even less if you compress it! That's *not* to downplay the work of D. Richard Hipp, who created SQLite, an equally amazing piece of work. Considering that accomplishing the same thing with Visual Basic/ODBC/Access/SQL Server requires hundreds of megabytes, I think this is GREAT!
Let's hear it for all the folks using PB for boring old database programming!
Code: Select all
#True = 1
#False = 0
#SQLiteLib = 1
#SQLITE_OK = 0
Declare.l SQLite_Init()
Declare.l SQLite_Open(Filename.s, Mode.l)
Declare.l SQLite_Get_Table(DB_Handle.l, SQLString.s, *Rows, *Cols)
Declare.l SQLite_Exec(DB_Handle.l, SQLString.s)
Declare SQLite_Close(DB_Handle.l)
Declare.s SQLite_LibVersion()
; data returned from database query
Dim DBData.s(1,1)
; number of rows/columns returned
; NB: row 0 is column headers
Rows.l
Cols.l
; database operation result
Result.l
; extract SQLite DLL and write to disk
If OpenFile(1, "PBsqlite.dll")
WriteData(1,?SQLite, 196096)
CloseFile(1)
Else
MessageRequester("SQLite Error", "Can't write DLL file", #MB_ICONERROR | #MB_OK)
End
EndIf
; make sure SQLite library is present
If SQLite_Init()
; display SQLite version
Debug "SQLite version " + SQLite_LibVersion()
Debug ""
; open database
DBName.s = "test.db"
DBHandle.l = SQLite_Open(DBName, 0)
If DBHandle
; insert new row into database
SQL.s = "INSERT INTO bookmarks VALUES(789,'http://junk','junk','djunk')"
Result = SQLite_Exec(DBHandle, SQL)
Debug "INSERT result = " + Str(Result)
Debug ""
; execute SQL query
SQL.s = "SELECT id, url, site, category FROM bookmarks WHERE category LIKE 'd%'"
Result = SQLite_Get_Table(DBHandle, SQL, @Rows, @Cols)
If Result = #SQLITE_OK
; get the results
; display number of rows/columns
Debug "Rows = " + Str(Rows)
Debug ""
Debug "Columns = " + Str(Cols)
; display column headers
For Col.l = 0 To Cols-1
Debug DBData(0, Col)
Next
; display returned rows
For Row.l = 1 To Rows
Debug ""
Debug "Data row " + Str(Row) + " :"
For Col.l = 0 To Cols-1
Debug DBData(Row, Col)
Next
Next
Else
MessageRequester("SQLite Error", "Get_Table error = " + Str(Result), #MB_ICONERROR | #MB_OK)
EndIf
Else
MessageRequester("SQLite Error", "Can't open database " + DBName, #MB_ICONERROR | #MB_OK)
EndIf
Else
MessageRequester("SQLite Error", "Can't open sqlite.dll", #MB_ICONERROR | #MB_OK)
EndIf
; remember to close database
SQLite_Close(DBHandle)
; and delete SQLite DLL
CloseLibrary(#SQLiteLib)
DeleteFile("PBsqlite.dll")
End
; *** SQLite Wrapper Functions ***
; make sure SQLite DLL is present
Procedure.l SQLite_Init()
If OpenLibrary(#SQLiteLib, "PBsqlite.dll")
ProcedureReturn #True
Else
ProcedureReturn #False
EndIf
EndProcedure
; open specified database
; NB: Mode is currently not used
Procedure.l SQLite_Open(Filename.s, Mode.l)
Shared Result.l
Result = CallFunction(#SQLiteLib, "sqlite_open", @Filename, Mode, 0)
!ADD esp,12
ProcedureReturn Result
EndProcedure
; execute SQL query and return results
Procedure.l SQLite_Get_Table(DB_Handle.l, SQLString.s, *Rows, *Cols)
Shared Result.l
Shared LResultsPtr.l
Shared LRows.l
Shared LCols.l
Result = CallFunction(#SQLiteLib, "sqlite_get_table", DB_Handle, @SQLString, @LResultsPtr, @LRows, @LCols, 0)
!ADD esp,24
If Result = #SQLITE_OK
; return number of rows/columns
PokeL(*Rows, LRows)
PokeL(*Cols, LCols)
; redimension results array
Dim DBData.s(LRows, LCols-1)
; copy data into array
Address.l = 0
For row.l = 0 To LRows
For col.l = 0 To LCols-1
DBData(row, col) = PeekS(PeekL(LResultsPtr + Address + col * 4))
Next
Address + LCols * 4
Next
; free table memory
CallFunction(#SQLiteLib, "sqlite_free_table", LResultsPtr)
!ADD esp,4
EndIf
ProcedureReturn Result
EndProcedure
; execute SQL statement (no results)
Procedure.l SQLite_Exec(DB_Handle.l, SQLString.s)
Shared Result.l
Result = CallFunction(#SQLiteLib, "sqlite_exec", DB_Handle, @SQLString, 0, 0, 0)
!ADD esp,20
ProcedureReturn Result
EndProcedure
; close database
Procedure SQLite_Close(DB_Handle.l)
CallFunction(#SQLiteLib, "sql_close", DB_Handle)
!ADD esp,4
EndProcedure
; get SQLite version
Procedure.s SQLite_LibVersion()
ProcedureReturn PeekS(CallFunction(#SQLiteLib, "sqlite_libversion"))
EndProcedure
SQLite:
IncludeBinary "SQLite\sqlite.dll"
