Page 1 of 1
[BY_DESIGN]; SQLite get error no. -> use DLL instead
Posted: Fri Feb 19, 2010 3:50 am
by HanPBF
Hello!
I try to work with SQLite3 and do only want to use PB and not SQLite3.dll directly (which did not succeed with sqlite3_errcode or sqlite3_errmsg...).
I wonder if there is a PB-"native" solution to get a database error as number?
The reason is, that a text is not good usable for storing in a log or as an enumeration.
Mapping the text with NewMap() to an internal number seems to be a little strange...
I did search the forum but did not find a solution.
OnError-Library seems to work only for runtime-errors concerning PB directly.
Any idea? (Hopefully the problem is so easy that I didn't see it...
Thanks!
Re: DatabaseError(); get error number instead of text
Posted: Fri Feb 19, 2010 4:19 am
by ts-soft
You can mix the PB Functions with Sqlite3 API!
see here the example from the help with small changes:
Code: Select all
;
; ------------------------------------------------------------
;
; PureBasic - Database example file
;
; (c) Fantaisie Software
;
; ------------------------------------------------------------
;
UseSQLiteDatabase()
; ###########
ImportC ""
sqlite3_errcode (hDB.i)
sqlite3_errmsg (hDB.i)
EndImport
;#############
Procedure CheckDatabaseUpdate(Database, Query$)
Result = DatabaseUpdate(Database, Query$)
If Result = 0
Debug DatabaseError()
EndIf
ProcedureReturn Result
EndProcedure
DatabaseFile$ = GetTemporaryDirectory()+"/Database.sqlite"
If CreateFile(0, DatabaseFile$)
CloseFile(0)
If OpenDatabase(0, DatabaseFile$, "", "")
;###########################################
Debug PeekS(sqlite3_errmsg(DatabaseID(0)))
Debug sqlite3_errcode(DatabaseID(0))
;###########################################
CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")
CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
If DatabaseQuery(0, "SELECT * FROM food WHERE weight > 7")
While NextDatabaseRow(0)
Debug GetDatabaseString(0, 0)
Wend
FinishDatabaseQuery(0)
EndIf
CloseDatabase(0)
Else
Debug "Can't open database !"
EndIf
Else
Debug "Can't create the database file !"
EndIf
Greetings
Thomas
Re: DatabaseError(); get error number instead of text
Posted: Fri Feb 19, 2010 9:26 am
by HanPBF
Hello Thomas,
many thanks for the example!
What I did not respect in my tests was "DataBaseID()" and ImportC.
So, as a beginner in PB it's hopefully forgivable...
Thanks,
Hans
Re: DatabaseError(); get error number instead of text
Posted: Fri Feb 19, 2010 1:48 pm
by HanPBF
I tried the example and it seems it doesn't work.
When You create an error in the example code, no correct error no. is given back (ever no. 1) and text is ever "?????" so, illegal memory access...
Even if text is read wrong through PeekS, sqlite3_errcode should not give the same number on each try with different sql errors...
O.k., I will further work with the textual only error messages from DatabaseError() and hopefully they keep staying constantly...
Why in building an interface from PB to sqlite a textual representation is preferred over a numerical will keep me puzzling a while...
Isn't it easier to map a number (as a constant) to text than vice versa?
The reason for that design decision would be interesting to hear about.
Anyway, PB rocks!
Re: DatabaseError(); get error number instead of text
Posted: Sun Aug 18, 2013 1:25 am
by nalor
Although this thread is quite old I'd like to post a more complete example to demonstrate how to get the error number instead of the error message.
HanPBF had 2 problems in the end:
* ErrorNumber is always 1 >> this is correct for all sql-errors as the sqlite reference uses this for the error "SQL error or missing database" (SQLITE_ERROR)
* ErrorMessage is "????" - this is because it is stored as UTF8 in the memory and so the specification of the format is necessary for the PeekS
Details about the ResultMessages/Codes for SQLite can be found here
http://www.sqlite.org/c3ref/errcode.html
Here's my example:
Code: Select all
EnableExplicit
; Database
Enumeration
#DB
EndEnumeration
;- BEGIN SQLite ResultMessageArea
; http://www.sqlite.org/c3ref/errcode.html
ImportC ""
sqlite3_errcode(hDB.i)
sqlite3_extended_errcode(hDB.i)
sqlite3_errmsg(hDB.i)
sqlite3_errstr(ErrCode.i)
EndImport
; SQLite C Interface - Result Codes
#SQLITE_OK= 0 ; Successful result
; beginning-of-error-codes
#SQLITE_ERROR= 1 ; SQL error Or missing database
#SQLITE_INTERNAL= 2 ; 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 sqlite3_interrupt()*/
#SQLITE_IOERR= 10 ; Some kind of disk I/O error occurred
#SQLITE_CORRUPT= 11 ; The database disk image is malformed
#SQLITE_NOTFOUND= 12 ; Unknown opcode in sqlite3_file_control()
#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 ; Database is empty
#SQLITE_SCHEMA= 17 ; The database schema changed
#SQLITE_TOOBIG= 18 ; String Or BLOB exceeds size limit
#SQLITE_CONSTRAINT= 19 ; Abort due To constraint 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_FORMAT= 24 ; Auxiliary database format error
#SQLITE_RANGE= 25 ; 2nd parameter To sqlite3_bind out of range
#SQLITE_NOTADB= 26 ; File opened that is Not a database file
#SQLITE_NOTICE= 27 ; Notifications from sqlite3_log()
#SQLITE_WARNING= 28 ; Warnings from sqlite3_log()
#SQLITE_ROW= 100 ; sqlite3_step() has another row ready
#SQLITE_DONE= 101 ; sqlite3_step() has finished executing
; End-of-error-codes
; SQLite C Interface - Extended Result Codes
#SQLITE_IOERR_READ =(#SQLITE_IOERR | (1<<8))
#SQLITE_IOERR_SHORT_READ =(#SQLITE_IOERR | (2<<8))
#SQLITE_IOERR_WRITE =(#SQLITE_IOERR | (3<<8))
#SQLITE_IOERR_FSYNC =(#SQLITE_IOERR | (4<<8))
#SQLITE_IOERR_DIR_FSYNC =(#SQLITE_IOERR | (5<<8))
#SQLITE_IOERR_TRUNCATE =(#SQLITE_IOERR | (6<<8))
#SQLITE_IOERR_FSTAT =(#SQLITE_IOERR | (7<<8))
#SQLITE_IOERR_UNLOCK =(#SQLITE_IOERR | (8<<8))
#SQLITE_IOERR_RDLOCK =(#SQLITE_IOERR | (9<<8))
#SQLITE_IOERR_DELETE =(#SQLITE_IOERR | (10<<8))
#SQLITE_IOERR_BLOCKED =(#SQLITE_IOERR | (11<<8))
#SQLITE_IOERR_NOMEM =(#SQLITE_IOERR | (12<<8))
#SQLITE_IOERR_ACCESS =(#SQLITE_IOERR | (13<<8))
#SQLITE_IOERR_CHECKRESERVEDLOCK =(#SQLITE_IOERR | (14<<8))
#SQLITE_IOERR_LOCK =(#SQLITE_IOERR | (15<<8))
#SQLITE_IOERR_CLOSE =(#SQLITE_IOERR | (16<<8))
#SQLITE_IOERR_DIR_CLOSE =(#SQLITE_IOERR | (17<<8))
#SQLITE_IOERR_SHMOPEN =(#SQLITE_IOERR | (18<<8))
#SQLITE_IOERR_SHMSIZE =(#SQLITE_IOERR | (19<<8))
#SQLITE_IOERR_SHMLOCK =(#SQLITE_IOERR | (20<<8))
#SQLITE_IOERR_SHMMAP =(#SQLITE_IOERR | (21<<8))
#SQLITE_IOERR_SEEK =(#SQLITE_IOERR | (22<<8))
#SQLITE_IOERR_DELETE_NOENT =(#SQLITE_IOERR | (23<<8))
#SQLITE_IOERR_MMAP =(#SQLITE_IOERR | (24<<8))
#SQLITE_LOCKED_SHAREDCACHE =(#SQLITE_LOCKED | (1<<8))
#SQLITE_BUSY_RECOVERY =(#SQLITE_BUSY | (1<<8))
#SQLITE_CANTOPEN_NOTEMPDIR =(#SQLITE_CANTOPEN | (1<<8))
#SQLITE_CANTOPEN_ISDIR =(#SQLITE_CANTOPEN | (2<<8))
#SQLITE_CANTOPEN_FULLPATH =(#SQLITE_CANTOPEN | (3<<8))
#SQLITE_CORRUPT_VTAB =(#SQLITE_CORRUPT | (1<<8))
#SQLITE_READONLY_RECOVERY =(#SQLITE_READONLY | (1<<8))
#SQLITE_READONLY_CANTLOCK =(#SQLITE_READONLY | (2<<8))
#SQLITE_READONLY_ROLLBACK =(#SQLITE_READONLY | (3<<8))
#SQLITE_ABORT_ROLLBACK =(#SQLITE_ABORT | (2<<8))
#SQLITE_CONSTRAINT_CHECK =(#SQLITE_CONSTRAINT | (1<<8))
#SQLITE_CONSTRAINT_COMMITHOOK =(#SQLITE_CONSTRAINT | (2<<8))
#SQLITE_CONSTRAINT_FOREIGNKEY =(#SQLITE_CONSTRAINT | (3<<8))
#SQLITE_CONSTRAINT_FUNCTION =(#SQLITE_CONSTRAINT | (4<<8))
#SQLITE_CONSTRAINT_NOTNULL =(#SQLITE_CONSTRAINT | (5<<8))
#SQLITE_CONSTRAINT_PRIMARYKEY =(#SQLITE_CONSTRAINT | (6<<8))
#SQLITE_CONSTRAINT_TRIGGER =(#SQLITE_CONSTRAINT | (7<<8))
#SQLITE_CONSTRAINT_UNIQUE =(#SQLITE_CONSTRAINT | (8<<8))
#SQLITE_CONSTRAINT_VTAB =(#SQLITE_CONSTRAINT | (9<<8))
#SQLITE_NOTICE_RECOVER_WAL =(#SQLITE_NOTICE | (1<<8))
#SQLITE_NOTICE_RECOVER_ROLLBACK =(#SQLITE_NOTICE | (2<<8))
Procedure.s SQLite_ExtendedErrMsg(ExtErrCode.i)
Protected ErrMsg.s
Select ExtErrCode
Case #SQLITE_IOERR_READ
ErrMsg="SQLITE_IOERR_READ"
Case #SQLITE_IOERR_SHORT_READ
ErrMsg="SQLITE_IOERR_SHORT_READ"
Case #SQLITE_IOERR_WRITE
ErrMsg="SQLITE_IOERR_WRITE"
Case #SQLITE_IOERR_FSYNC
ErrMsg="SQLITE_IOERR_FSYNC"
Case #SQLITE_IOERR_DIR_FSYNC
ErrMsg="SQLITE_IOERR_DIR_FSYNC"
Case #SQLITE_IOERR_TRUNCATE
ErrMsg="SQLITE_IOERR_TRUNCATE"
Case #SQLITE_IOERR_FSTAT
ErrMsg="SQLITE_IOERR_FSTAT"
Case #SQLITE_IOERR_UNLOCK
ErrMsg="SQLITE_IOERR_UNLOCK"
Case #SQLITE_IOERR_RDLOCK
ErrMsg="SQLITE_IOERR_RDLOCK"
Case #SQLITE_IOERR_DELETE
ErrMsg="SQLITE_IOERR_DELETE"
Case #SQLITE_IOERR_BLOCKED
ErrMsg="SQLITE_IOERR_BLOCKED"
Case #SQLITE_IOERR_NOMEM
ErrMsg="SQLITE_IOERR_NOMEM"
Case #SQLITE_IOERR_ACCESS
ErrMsg="SQLITE_IOERR_ACCESS"
Case #SQLITE_IOERR_CHECKRESERVEDLOCK
ErrMsg="SQLITE_IOERR_CHECKRESERVEDLOCK"
Case #SQLITE_IOERR_LOCK
ErrMsg="SQLITE_IOERR_LOCK"
Case #SQLITE_IOERR_CLOSE
ErrMsg="SQLITE_IOERR_CLOSE"
Case #SQLITE_IOERR_DIR_CLOSE
ErrMsg="SQLITE_IOERR_DIR_CLOSE"
Case #SQLITE_IOERR_SHMOPEN
ErrMsg="SQLITE_IOERR_SHMOPEN"
Case #SQLITE_IOERR_SHMSIZE
ErrMsg="SQLITE_IOERR_SHMSIZE"
Case #SQLITE_IOERR_SHMLOCK
ErrMsg="SQLITE_IOERR_SHMLOCK"
Case #SQLITE_IOERR_SHMMAP
ErrMsg="SQLITE_IOERR_SHMMAP"
Case #SQLITE_IOERR_SEEK
ErrMsg="SQLITE_IOERR_SEEK"
Case #SQLITE_IOERR_DELETE_NOENT
ErrMsg="SQLITE_IOERR_DELETE_NOENT"
Case #SQLITE_IOERR_MMAP
ErrMsg="SQLITE_IOERR_MMAP"
Case #SQLITE_LOCKED_SHAREDCACHE
ErrMsg="SQLITE_LOCKED_SHAREDCACHE"
Case #SQLITE_BUSY_RECOVERY
ErrMsg="SQLITE_BUSY_RECOVERY"
Case #SQLITE_CANTOPEN_NOTEMPDIR
ErrMsg="SQLITE_CANTOPEN_NOTEMPDIR"
Case #SQLITE_CANTOPEN_ISDIR
ErrMsg="SQLITE_CANTOPEN_ISDIR"
Case #SQLITE_CANTOPEN_FULLPATH
ErrMsg="SQLITE_CANTOPEN_FULLPATH"
Case #SQLITE_CORRUPT_VTAB
ErrMsg="SQLITE_CORRUPT_VTAB"
Case #SQLITE_READONLY_RECOVERY
ErrMsg="SQLITE_READONLY_RECOVERY"
Case #SQLITE_READONLY_CANTLOCK
ErrMsg="SQLITE_READONLY_CANTLOCK"
Case #SQLITE_READONLY_ROLLBACK
ErrMsg="SQLITE_READONLY_ROLLBACK"
Case #SQLITE_ABORT_ROLLBACK
ErrMsg="SQLITE_ABORT_ROLLBACK"
Case #SQLITE_CONSTRAINT_CHECK
ErrMsg="SQLITE_CONSTRAINT_CHECK"
Case #SQLITE_CONSTRAINT_COMMITHOOK
ErrMsg="SQLITE_CONSTRAINT_COMMITHOOK"
Case #SQLITE_CONSTRAINT_FOREIGNKEY
ErrMsg="SQLITE_CONSTRAINT_FOREIGNKEY"
Case #SQLITE_CONSTRAINT_FUNCTION
ErrMsg="SQLITE_CONSTRAINT_FUNCTION"
Case #SQLITE_CONSTRAINT_NOTNULL
ErrMsg="SQLITE_CONSTRAINT_NOTNULL"
Case #SQLITE_CONSTRAINT_PRIMARYKEY
ErrMsg="SQLITE_CONSTRAINT_PRIMARYKEY"
Case #SQLITE_CONSTRAINT_TRIGGER
ErrMsg="SQLITE_CONSTRAINT_TRIGGER"
Case #SQLITE_CONSTRAINT_UNIQUE
ErrMsg="SQLITE_CONSTRAINT_UNIQUE"
Case #SQLITE_CONSTRAINT_VTAB
ErrMsg="SQLITE_CONSTRAINT_VTAB"
Case #SQLITE_NOTICE_RECOVER_WAL
ErrMsg="SQLITE_NOTICE_RECOVER_WAL"
Case #SQLITE_NOTICE_RECOVER_ROLLBACK
ErrMsg="SQLITE_NOTICE_RECOVER_ROLLBACK"
Default
ErrMsg=PeekS(sqlite3_errstr(ExtErrCode), -1, #PB_UTF8)
EndSelect
ErrMsg+" ("+Str(ExtErrCode)+")"
ProcedureReturn ErrMsg
EndProcedure
Procedure.i DatabaseErrorNr(hDB.i, ExtendedCode.b=#False)
If Not ExtendedCode
ProcedureReturn sqlite3_errcode(DatabaseID(hDB))
Else
ProcedureReturn sqlite3_extended_errcode(DatabaseID(hDB))
EndIf
EndProcedure
Procedure.s DatabaseErrorMsg(hDB.i, ExtendedMsg.b=#True)
If ExtendedMsg
ProcedureReturn PeekS(sqlite3_errmsg(DatabaseID(hDB)), -1, #PB_UTF8)+" ("+Str(DatabaseErrorNr(hDB))+") Extended: "+SQLite_ExtendedErrMsg(sqlite3_extended_errcode(DatabaseID(hDB)))
Else
ProcedureReturn PeekS(sqlite3_errmsg(DatabaseID(hDB)), -1, #PB_UTF8)+" ("+Str(DatabaseErrorNr(hDB))+")"
EndIf
EndProcedure
;- END SQLite ResultMessageArea
Procedure DbTest_Disconnect(DB_File.s)
Protected sSql.s
Protected start.i
Protected endtime.i
Protected iCnt.i
If OpenDatabase(#DB, DB_File, "", "")
For iCnt=0 To 10
If IsDatabase(#DB)
Debug "DbReadyToUse >"+Str(iCnt)+"<"
Else
Debug "DbNotReady >"+Str(iCnt)+"<"
EndIf
Delay(500)
Next
sSql="Select * from test"
If DatabaseQuery(#DB, sSql)
iCnt=0
start=ElapsedMilliseconds()
While NextDatabaseRow(#DB) ;alle Einträge abrufen
iCnt+1
Wend
endtime=ElapsedMilliseconds()
Debug "DURATION >"+StrF( (endtime-start)/1000, 3)+"< Count >"+Str(iCnt)+"<"
FinishDatabaseQuery(#DB)
Else
Debug "DB Query Error - Msg >"+DatabaseErrorMsg(#DB)+"< Code >"+Str(DatabaseErrorNr(#DB))+"< ExtendedCode >"+Str(DatabaseErrorNr(#DB, #True))+"<"
EndIf
CloseDatabase(#DB)
Else
Debug "DB error"
EndIf
EndProcedure
Procedure CreateDbFile(DB_File.s)
Protected iHdl.i
Protected sSql.s
Protected Cnt.i
Protected Thread.i
If (FileSize(DB_File)>=0)
ProcedureReturn ; usually we don't want to create a new db each time
DeleteFile(DB_File)
Debug "OldDatabaseDeleted"
EndIf
iHdl=CreateFile(#PB_Any, DB_File)
If (iHdl)
Debug "Created new database-file >"+DB_File+"<"
CloseFile(iHdl)
EndIf
If OpenDatabase(#DB, DB_File, "", "")
sSql="begin; CREATE TABLE test (prkey INTEGER PRIMARY KEY, name TEXT, value TEXT); "
If Not DatabaseUpdate(#DB, sSql)
Debug "Error! >"+sSql+"<"
EndIf
For Cnt=1 To 10
sSql="INSERT INTO test(name, value) VALUES('TestEntry"+RSet(Str(Cnt), 6, "0")+"', '"+Str(Random(99999, 0))+"');"
If Not DatabaseUpdate(#DB, sSql)
Debug "Error! >"+sSql+"<"
EndIf
Next
sSql="commit; "
If Not DatabaseUpdate(#DB, sSql)
Debug "Error! >"+sSql+"<"
Debug "DB Query Error - Msg >"+DatabaseErrorMsg(#DB)+"< Code >"+Str(DatabaseErrorNr(#DB))+"< ExtendedCode >"+Str(DatabaseErrorNr(#DB, #True))+"<"
EndIf
CloseDatabase(#DB)
Else
Debug "db error"
EndIf
EndProcedure
UseSQLiteDatabase()
Define DB_File.s=""
; USB Stick
DB_File="h:\DbTest.sqlite"
; DB_File="h:\test.png"
CreateDbFile(DB_File)
DbTest_Disconnect(DB_File)
Debug "end"
You can try to set DB_File for example to a file that is not a database (I tried it with a picture) - in this case you get the message:
DB Query Error - Msg >file is encrypted or is not a database (26) Extended: file is encrypted or is not a database (26)< Code >26< ExtendedCode >26<
Or use a USB Stick and remove it during the 'IsDatabase' Loop - you'll get another error message:
DB Query Error - Msg >disk I/O error (10) Extended: SQLITE_IOERR_FSTAT (1802)< Code >10< ExtendedCode >1802<
Hope this helps!
Re: DatabaseError(); get error number instead of text
Posted: Tue Feb 17, 2015 8:07 am
by HanPBF
Thanks lot even 2 more years have passed;-)
But now I have to use PB in a business application and appreciate any help from this forum!
:D
Re: DatabaseError(); get error number instead of text
Posted: Wed Feb 18, 2015 3:09 pm
by blueb
HanPBF...
Be sure to layout your business application using pseudo-code logic before trying to code in PureBasic.
It will save you days, possibly months of time.
See:
http://education-portal.com/academy/les ... -quiz.html
Then you can confidently post specific questions on how to best tackle a problem.
Re: DatabaseError(); get error number instead of text
Posted: Wed Feb 18, 2015 4:48 pm
by HanPBF
Hi!
I am developing in PB an application and use OOP with macros.
Structure having a link to a class with method table and class has ancestor.
So You have some constructs like this:
method(left_)
...
endMethod()
; the call is
left(*MyObject, 8)
I even got due to the help from macros a super method in each method:
...
super(*P1)
...
Disadvantages: signature is always (*P1.SObject, *P2.SObject=-1, etc.
And it is very diffucult to debug, because You can't go into macros.
And You must map literal strings into object (so called boxing).
But it works; I ignored in a first step destroy (my "free" for SObject) and could manage to put it into the object hierarchy very quickly.
Of course, I have only a little data window and have 15000 lines of code (incl. few comments).
But this includes also a data repeater object.
I stopped developing a persistence framework for tables as this goes too far and I am confident with using some SQL in the code; the data structure I have to work with is not so good, either.
From my experience, PB runs very stable and the OOP features given with C# or Delphi are not easy, too.
O.k., generics are great; but a system must have a good size to have a profit from this.
And, a team size of one or two make some paradigms obsolete.
The next step is definitely a precompiler; to prevent having macros and to better have some AOP (aspect orientation); of course manual or half-auto but that's the case today for my OOP implementation.
A great help would be if PB had double or multiple dispatch to not conflict with existing procedure names.
As You can see above, my solution for this is to put an underline to left or right to unconflict with the keywords.
But structures are not really types but pointers, so that would be a huge development.
That things are happening in PB and there is a need for improvement You can see with the runtime library.
But that is as I am concerned too dynamic. Generating code from XML or SDL or JSON would work also.
I consider PB a great tool; You know what You can do with it and what not (without some work).
Some things with the gadgets are annoying, but as for my repeater box, we got canvasGadget, so what...
Thanks for the hint!