[BY_DESIGN]; SQLite get error no. -> use DLL instead

[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!

[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

[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

[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!

[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!

[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

[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.

[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!