PB and SQLite 3.x

kake26 · Post by **kake26** » Wed Jul 14, 2004 8:23 pm

Hi all,

I've been trying to us SQLite 3. with PB. Using the DLL is not a problem thats working,but when I call the close function for the DB I get this error.

library routine called out of sequence

Here is the app code. Maybe someone knows something I don't . I've been reading the docs and searching google all day for a clue and nothing so far.

;Setup constants
; This is all based on the SQLite 3.x API docs

#SQLITE_OK = 0 ; Successful result
#SQLITE_ERROR = 1 ; SQL error Or missing database
#SQLITE_INTERNAL = 2 ; An internal logic error in SQLite
#SQLITE_PERM = 3 ; Access permission denied
#SQLITE_ABORT = 4 ; Callback routine requested an abort
#SQLITE_BUSY = 5 ; The database file is locked
#SQLITE_LOCKED = 6 ; A table in the database is locked
#SQLITE_NOMEM = 7 ; A malloc() failed
#SQLITE_READONLY = 8 ; Attempt To write a readonly database
#SQLITE_INTERRUPT = 9 ; Operation terminated by sqlite_interrupt()
#SQLITE_IOERR = 10 ; Some kind of disk I/O error occurred
#SQLITE_CORRUPT = 11 ; The database disk image is malformed
#SQLITE_NOTFOUND = 12 ; (Internal Only) Table Or record not found
#SQLITE_FULL = 13 ; Insertion failed because database is full
#SQLITE_CANTOPEN = 14 ; Unable To open the database file
#SQLITE_PROTOCOL = 15 ; Database lock protocol error
#SQLITE_EMPTY = 16 ; (Internal Only) Database table is empty
#SQLITE_SCHEMA = 17 ; The database schema changed
#SQLITE_TOOBIG = 18 ; Too much Data For one row of a table
#SQLITE_CONSTRAINT = 19 ; Abort due To contraint violation
#SQLITE_MISMATCH = 20 ; Data type mismatch
#SQLITE_MISUSE = 21 ; Library used incorrectly
#SQLITE_NOLFS = 22 ; Uses OS features not supported on host
#SQLITE_AUTH = 23 ; Authorization denied
#SQLITE_ROW = 100 ; sqlite_step() has another row ready
#SQLITE_DONE = 101 ; sqlite_step() has finished executing

; open and load the DLL

If OpenLibrary(255,"sqlite3.dll") = 0
MessageRequester("Error", "sqlite3.dll Failed To load! Aborting execution!", 0)
EndIf

ExamineLibraryFunctions(255)

Repeat
Debug LibraryFunctionName()
retl = NextLibraryFunction()
Until retl = 0
; now import a few functions

OnErrorResume()

*sqlopen = IsFunction(255, "sqlite3_open")
*sqlexec = IsFunction(255, "sqlite3_exec")
*sqlfree = IsFunction(255, "sqlite3_free")
*sqlclose = IsFunction(255, "sqlite3_close")
*sqlerror = IsFunction(255, "sqlite3_errmsg")

;Debug GetLastError_()

If *sqlopen
ret.l = CallFunctionFast(*sqlopen,"c:\code\pb\test.db",@handle)
Debug ret
Debug handle
EndIf

;Debug GetLastError_()

query.s = "CREATE TABLE (name text,lname test);"

If *sqlexec
ret.l = CallFunctionFast(*sqlexec,@handle,query$,0,0,@ret)
Debug ret
If ret <> 0
ret.l = CallFunctionFast(*sqlerror,@ret)
Debug PeekS(ret)
EndIf
;Debug ret
EndIf

;Debug GetLastError_()

;If *sqlfree
;ret.l = CallFunctionFast(*sqlfree,@handle)
;Debug ret
;If ret <> 0
;ret.l = CallFunctionFast(*sqlerror,@ret)
;Debug PeekS(ret)
;EndIf
;EndIf

If *sqlclose
ret.l = CallFunctionFast(*sqlclose,@handle)
Debug ret
If ret <> 0
ret.l = CallFunctionFast(*sqlerror,@ret)
Debug PeekS(ret)
EndIf
EndIf

;Debug GetLastError_()

CloseLibrary(255)

Ideas?Clues? Help?! I need at least an inkling of the issue here any help is appreciated.

El_Choni · Post by **El_Choni** » Thu Jul 15, 2004 1:34 am

Hi,

You have to supply the functions the handle itself, not a pointer to the handle. So delete the leading "@" of "@handle" in all calls but in the call to sqlite3_open. Your code works here that way.

Regards,

kake26 · Post by **kake26** » Thu Jul 15, 2004 2:53 am

Thanks, got rid of the errors. However, what version of SQLite are you using cause I am using the 3.0.2 DLL. The errors are gone now and the file gets created and opened, but the size should jump to like 2 KB when I make a table and when I check it against the commandline version it says that the table doesn't exist. Any thoughts?

kake26 · Post by **kake26** » Thu Jul 15, 2004 3:59 am

Never mind, thanks for the clue it was just what I needed! Here is a working copy for all those that care. So SQLite in PB is a very feasable solution under win32 and *nix.

;Setup constants
; This is all based on the SQLite 3.x API docs

#SQLITE_OK = 0 ; Successful result
#SQLITE_ERROR = 1 ; SQL error Or missing database
#SQLITE_INTERNAL = 2 ; An internal logic error in SQLite
#SQLITE_PERM = 3 ; Access permission denied
#SQLITE_ABORT = 4 ; Callback routine requested an abort
#SQLITE_BUSY = 5 ; The database file is locked
#SQLITE_LOCKED = 6 ; A table in the database is locked
#SQLITE_NOMEM = 7 ; A malloc() failed
#SQLITE_READONLY = 8 ; Attempt To write a readonly database
#SQLITE_INTERRUPT = 9 ; Operation terminated by sqlite_interrupt()
#SQLITE_IOERR = 10 ; Some kind of disk I/O error occurred
#SQLITE_CORRUPT = 11 ; The database disk image is malformed
#SQLITE_NOTFOUND = 12 ; (Internal Only) Table Or record not found
#SQLITE_FULL = 13 ; Insertion failed because database is full
#SQLITE_CANTOPEN = 14 ; Unable To open the database file
#SQLITE_PROTOCOL = 15 ; Database lock protocol error
#SQLITE_EMPTY = 16 ; (Internal Only) Database table is empty
#SQLITE_SCHEMA = 17 ; The database schema changed
#SQLITE_TOOBIG = 18 ; Too much Data For one row of a table
#SQLITE_CONSTRAINT = 19 ; Abort due To contraint violation
#SQLITE_MISMATCH = 20 ; Data type mismatch
#SQLITE_MISUSE = 21 ; Library used incorrectly
#SQLITE_NOLFS = 22 ; Uses OS features not supported on host
#SQLITE_AUTH = 23 ; Authorization denied
#SQLITE_ROW = 100 ; sqlite_step() has another row ready
#SQLITE_DONE = 101 ; sqlite_step() has finished executing

; open and load the DLL

If OpenLibrary(255,"sqlite3.dll") = 0
MessageRequester("Error", "sqlite3.dll Failed To load! Aborting execution!", 0)
EndIf

ExamineLibraryFunctions(255)

Repeat
Debug LibraryFunctionName()
retl = NextLibraryFunction()
Until retl = 0
; now import a few functions

OnErrorResume()

*sqlopen = IsFunction(255, "sqlite3_open")
*sqlexec = IsFunction(255, "sqlite3_exec")
*sqlclose = IsFunction(255, "sqlite3_close")
*sqlerror = IsFunction(255, "sqlite3_errmsg")

If *sqlopen
ret.l = CallFunctionFast(*sqlopen,"c:\code\pb\test.db",@handle)
Debug ret
Debug handle
EndIf

query$ = "CREATE TABLE test (name text,lname test);"

If *sqlexec
ret.l = CallFunctionFast(*sqlexec,handle,@query$,0,0,@ret)
Debug ret
If ret <> 0
ret.l = CallFunctionFast(*sqlerror,@ret)
Debug PeekS(ret)
EndIf
EndIf

query$ = "INSERT INTO test (name,lname) VALUES ('some','thing');"

If *sqlexec
ret.l = CallFunctionFast(*sqlexec,handle,@query$,#null,0,@ret)
Debug ret
If ret <> 0
ret.l = CallFunctionFast(*sqlerror,@ret)
Debug PeekS(ret)
EndIf
EndIf

If *sqlclose
ret.l = CallFunctionFast(*sqlclose,handle)
Debug ret
If ret <> 0
ret.l = CallFunctionFast(*sqlerror,@ret)
Debug PeekS(ret)
EndIf
EndIf

CloseLibrary(255)

Karbon · Post by **Karbon** » Thu Jul 15, 2004 4:08 am

Keep in mind that SQLite 3 is *brand* new. You're likely to run into many operational bugs. Join the SQLite mailing list - they are a good group of people that are very responsive to questions!

dige · Post by **dige** » Thu Jul 15, 2004 7:40 am

You can also use El_Chonis great sqlite lib for easy direct accesss...
... and maybe there comes soon a new version which is thread safe ;-)

cya dige

El_Choni · Post by **El_Choni** » Thu Jul 15, 2004 9:31 am

My lib doesn't work with SQLite 3.0.2 yet, the next version will (and will be thread safe too, but I have to talk with you, dige, about thread safe functions when using strings;)

@kake26: I've used sqlite3.dll (3.0.2) for testing your code. IMHO, SQLite is good for one-user databases, but I'm not sure if version 3 has improved concurrent access.

Regards,

kake26 · Post by **kake26** » Thu Jul 15, 2004 2:30 pm

Yeah well according to the info I've come a cross it passed like 95% of all tests. So I figure I'll be safe at least for what I plan to use it for.

Karbon · Post by **Karbon** » Thu Jul 15, 2004 2:48 pm

SQLite 3 has improved the concurrent user (locking) issues *some* but it's far from replacing a real server. For single user (or careful multi-user) access SQLite is good.

kake26 · Post by **kake26** » Thu Jul 15, 2004 3:18 pm

Now the fun part grabbing results via a call back wohooo! Here goes another day.

Pointers to pointers this will be fun, NOT!