Insert png ,jpeg or pdf into a SQLite BLOB

[metro]

Has anyone tackled inserting a document or picture into a SQLite BLOB?

sample code would be appreciated

my attempts to date trying to insert a *.png  result in an error "unrecognized token: "'‰PNG"

similar results with PDF

all files have been loaded as binary renaming them and saving to disk results in a perfect functioning copy of the original

any advice appreciated

*For some reason I cannot reply to this post, so I am responding to Chris's post below by editing my original post

Thanks Chris, I should have read the info that came with the wrapper first. seems there is no support for BLOBS in the wrapper

I'll do some reading www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

our wrapper and dll HERE


insert code here;
; SQ3_4_LB.dll v0.3 by Gerd Heger 9/2006
;
; SQ3_4_LB.dll is a wrapper for sqlite3.dll to be used with Liberty Basic.
; It has only the basic functions implemented, there is no support for blobs or unicode.
;
; This code is based on the PureBasic OpenSource Libraries (PBOSL)
; SQLite3 Userlibrary V1.1 (21.10.2005) by  Peter 'Kiffi' Tübben.
; PBOSL is distributed under the terms of the GNU Lesser General Public License.
; The same license applies to SQ3_4_LB.dll.
;
;>>   SQ3_4_LB.dll is released 'as is' in the hope that it will be useful, <<
;>>   but WITHOUT ANY WARRANTY; without even the implied warranty of       <<
;>>   MERCHANTABILITY Or FITNESS For A PARTICULAR PURPOSE.                 <<
;
; sqlite3.dll is by  D. Richard Hipp
;
; These are the modifications I made :
; - Recordset structured variable (RS) is defined in the dll. The calling program receives just a copy.
; - RS structure: sValue.s was changed to StrAdr.l to make it point to the original string not a copy of it.
; - every call to GetRecordset releases memory used by the previous call.
;   So there is only one query result in memory at a time.
; - added: RecordsetMoveToRow procedure to make a specified row the current one.
; - added: GetRecordsetValueOfRow procedure creates a string that contains all the fields of a row
;          separated by a separator character.
;v0.2
; - added: keep track of open files in a list and close them on shut down if still open.
;v0.3
; - added: GetFieldNameByIndex procedure to read column headers from recordset.
;          


; These are the exported Procedures:
;
;-~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ File Handling Procedures
;
; SQ3_4_LB_OpenDB(sDataBase.s)                                                     open an existing database
; parameter     :  sDataBase.s   filename of database
; return value  :  DBhandle.l or #False                                  
;
; SQ3_4_LB_CreateDB(sDataBase.s, bolOverwrite.l)                                       create a new database
; parameter     :  sDataBase.s   filename of database
;               :  bolOverwrite.l   1 = delete an existing file  0 = dont't delete
; return value  :  DBhandle.l or #False
;
; SQ3_4_LB_CloseDB(lDBhnd.l)                                                           close a database
; parameter     :  lDBhnd.l   handle of database
; return value  :  #True or #False
;
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL Query Procedures
;
; SQ3_4_LB_Execute(sSQLQuery.s, DBhandle.l)                                      execute an SQL command
; parameter     :  sSQLQuery.s   string with SQL command                                        
;               :  DBhandle.l    handle of database
; return value  :  #True or #False
;
;
; SQ3_4_LB_GetRecordset(sSQLQuery.s, DBhandle.l, PtrUserRS.l)                            execute an SQL command
; parameter     :  sSQLQuery.s   string with SQL command                                 that has a resulting
;               :  DBhandle.l    handle of database                                      recordset (select)
;               :  PtrUserRS.l   pointer to UserRS structure in calling program
; return value  :  #True or #False
;               :  fills UserRS structure
;
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Procedures to retrieve data from Recordset
;
; SQ3_4_LB_GetFieldIndexByName(sFieldname.s)                                          returns index of a
; parameter     :  sFieldname.s   string with fieldname                               named field
; return value  :  fieldindex.l or -1 in case of an error
;
; SQ3_4_LB_GetFieldNameByIndex(lIndex.l)                                              returns fieldname
; parameter     :  lIndex.l   fieldindex
; return value  :  fieldname.s or empty string in case of an error
;
; SQ3_4_LB_GetRecordsetValueByName(sFieldname.s)                                      get data item by name
; parameter     :  sFieldname.s   string with fieldname                               in the current row
; return value  :  #True or #False
;               :  fills UserRS structure
;
; SQ3_4_LB_GetRecordsetValueByIndex(lIndex.l)                                         get data item by index
; parameter     :  lIndex.l   fieldindex                                              in the current row
; return value  :  #True or #False
;               :  fills UserRS structure
;
; SQ3_4_LB_GetRecordsetValueOfRow(Separator$)                                        get all fields of current
; parameter     :  Separator$   string with separator character                      row separated by Separator$
; return value  :  #True or #False
;               :  fills UserRS structure
;
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Procedures to navigate in Recordset
;
; SQ3_4_LB_RecordsetMoveFirst()                                                      move to first row in recordset
; parameter     :  -                                                                    
; return value  :  void
;               :  updates UserRS structure
;
; SQ3_4_LB_RecordsetMoveLast()                                                       move to last row in recordset
; parameter     :  -
; return value  :  void
;               :  updates UserRS structure
;
; SQ3_4_LB_RecordsetMoveNext()                                                       move to next row in recordset
; parameter     :  -
; return value  :  void
;               :  updates UserRS structure
;
; SQ3_4_LB_RecordsetMovePrevious()                                                   move to previous row in recordset
; parameter     :  -
; return value  :  void
;               :  updates UserRS structure
;
; SQ3_4_LB_RecordsetMoveToRow(RowIndex.l)                                            move to specified  row
; parameter     :  RowIndex  index of row to move to
; return value  :  void
;               :  updates UserRS structure
;
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Procedures that return a string
;
; SQ3_4_LB_Version()                                                                 returns version of this dll
; parameter     :  -
; return value  :  version string
;
; SQ3_4_LB_GetLastMessage()                                                          returns last error-message
; parameter     :  -
; return value  :  error string
;
; SQ3_4_LB_GetLibVersion()                                                           version of sqlite3.dll used
; parameter     :  -
; return value  :  version string
;
; SQ3_4_LB_About()                                                                   returns credits string
; parameter     :  -
; return value  :  void
;
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ other Procedures
;
; SQ3_4_LB_LastInsertRowID(lDataBaseHandle.l)                                         returns RowID of the most recent    
; parameter     :  lDataBaseHandle.l   string with fieldname                          INSERT in the specified database
; return value  :  RowID.l or zero if no insert occurred
;
; SQ3_4_LB_ReleaseRecordset()                                                        release memory used by recordset
; parameter     :  -                                                                 can be called by user but memory
; return value  :  void                                                              is released automatically
;
;End

;- Structures

Structure SQ3_4_LB_RecordSet  ; this structure is used to navigate in the results table returned by sqlite3.dll  
   BOF.l                     ; is #True when CurrentPos = 1
   EOF.l                     ; is #True when CurrentPos = Rows
   Handle.l                  ; address of recordset data returned by sqlite3.dll
   Rows.l                    ; number of rows in recordset
   Cols.l                    ; number of columns in recordset  
   CurrentPos.l              ; current row in recordset
   ;sValue.s                 ; changed: from a PB managed string to a long that holds the address of a string
   StrAdr.l                  ; address of data item
EndStructure

;- Constants
;   #True                   =  1
;   #False                  =  0
   #SQLITE3_OK             =  0                              ; Successful Result
   #SQ3_4_LB_VERSION$      = "0.3"
   #MSG_OK$                = "OK"
   #MSG_NO_RS$             = "no Recordset available"
   #MSG_SQLITE3_NOTLOADED$ = "sqlite3.dll was not loaded"

;- Globals
   
   Global sqlite3DLL.l     = 0                              ; Dll Handle
   Global SQLiteDLLPath$   = "sqlite3.dll"
   Global LastMessage$     = ""
   Global ReturnString$    = ""
   Global Empty$           = ""
   Global *User_RS.l                                        ; pointer to RS structure in calling program
   
   Global RS.SQ3_4_LB_RecordSet                             ; RS = variable of type SQ3_4_LB_RecordSet
   
   Global sqlite3_open.l
   Global sqlite3_close.l
   Global sqlite3_libversion.l
   Global sqlite3_exec.l
   Global sqlite3_get_table.l
   Global sqlite3_free_table.l
   Global sqlite3_errmsg.l
   Global sqlite3_last_insert_rowid.l
   
   ;Global NewList OpenFiles.l()                            ; defined in AttachProcess()
   
; Declares

   Declare SQ3_4_LB_ReleaseRecordset()    


ProcedureDLL AttachProcess(Instance)
   
   sqlite3DLL = LoadLibrary_(@SQLiteDLLPath$)

   If sqlite3DLL
       
       sqlite3_open              = GetProcAddress_(sqlite3DLL, "sqlite3_open")
       sqlite3_close             = GetProcAddress_(sqlite3DLL, "sqlite3_close")
       sqlite3_libversion        = GetProcAddress_(sqlite3DLL, "sqlite3_libversion")
       sqlite3_exec              = GetProcAddress_(sqlite3DLL, "sqlite3_exec")
       sqlite3_get_table         = GetProcAddress_(sqlite3DLL, "sqlite3_get_table")
       sqlite3_free_table        = GetProcAddress_(sqlite3DLL, "sqlite3_free_table")
       sqlite3_errmsg            = GetProcAddress_(sqlite3DLL, "sqlite3_errmsg")
       sqlite3_last_insert_rowid = GetProcAddress_(sqlite3DLL, "sqlite3_last_insert_rowid")
       
       LastMessage$ = "'sqlite3.dll' is loaded"
       
       If sqlite3_errmsg = 0
           LastMessage$ = "Couldn't init 'sqlite3_errmsg'"
           ProcedureReturn
       EndIf
       
       If sqlite3_libversion = 0
           LastMessage$ = "Couldn't init 'sqlite3_libversion'"
           ProcedureReturn
       EndIf
       
       If sqlite3_get_table = 0
           LastMessage$ = "Couldn't init 'sqlite3_get_table'"
           ProcedureReturn
       EndIf

       If sqlite3_free_table = 0
           LastMessage$ = "Couldn't init 'sqlite3_free_table'"
           ProcedureReturn
       EndIf

       If sqlite3_open = 0
           LastMessage$ = "Couldn't init 'sqlite3_open'"
           ProcedureReturn
       EndIf

       If sqlite3_close = 0
           LastMessage$ = "Couldn't init 'sqlite3_close'"
           ProcedureReturn
       EndIf

       If sqlite3_exec = 0
           LastMessage$ = "Couldn't init 'sqlite3_exec'"
           ProcedureReturn
       EndIf
       
       If sqlite3_last_insert_rowid = 0
           LastMessage$ = "Couldn't init 'sqlite3_last_insert_rowid'"
           ProcedureReturn
       EndIf
       
       Global NewList OpenFiles.l()
       
   Else
       LastMessage$ = "General Error - Couldn't open '"+ SQLiteDLLPath$ +"'"
       ProcedureReturn
   EndIf
EndProcedure


ProcedureDLL DetachProcess(Instance)

   If sqlite3DLL
       
       ForEach OpenFiles()
           CallCFunctionFast(sqlite3_close, OpenFiles())  
       Next
       
       SQ3_4_LB_ReleaseRecordset()    
       FreeLibrary_(sqlite3DLL)
   EndIf
 
EndProcedure


ProcedureDLL.l SQ3_4_LB_OpenDB(sDataBase.s)                             ; open an existing database
                                                                       ; returns handle of DB  
   If sqlite3DLL    
           
   If FileSize(sDataBase) <> -1
       hResult = CallCFunctionFast(sqlite3_open, sDataBase, @sqlite3_dbHandle)
       If hResult = #SQLITE3_OK
           LastMessage$ = #MSG_OK$
           AddElement(OpenFiles()) : OpenFiles() = sqlite3_dbHandle
           ProcedureReturn sqlite3_dbHandle
       Else
           CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
           LastMessage$ = PeekS(ReturnValue)
           ProcedureReturn #False
       EndIf
   Else
       LastMessage$ = "Database doesn't exists"
       ProcedureReturn #False
   EndIf
   
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
       ProcedureReturn #False
   EndIf    
EndProcedure


ProcedureDLL.l SQ3_4_LB_CreateDB(sDataBase.s, bolOverwrite.l)           ; create a new database
                                                                       ; returns handle of DB    
   If sqlite3DLL    
   
   If FileSize(sDataBase) = -1  ; DB does not exist

       ; create

       hResult = CallCFunctionFast(sqlite3_open, sDataBase, @sqlite3_dbHandle)

       If hResult = #SQLITE3_OK
           LastMessage$ = #MSG_OK$
           AddElement(OpenFiles()) : OpenFiles() = sqlite3_dbHandle
           ProcedureReturn sqlite3_dbHandle
       Else
           LastMessage$ = "Couldn't create database"
           ProcedureReturn #False
       EndIf

   Else                         ; DB exists already

       If bolOverwrite

           ; overwrite

           If Not DeleteFile(sDataBase)
               LastMessage$ = "Couldn't delete '" + sDataBase + "'"
               ProcedureReturn #False
           EndIf
               
           hResult = CallCFunctionFast(sqlite3_open, sDataBase, @sqlite3_dbHandle)

           If hResult = #SQLITE3_OK
               LastMessage$ = #MSG_OK$
               AddElement(OpenFiles()) : OpenFiles() = sqlite3_dbHandle
               ProcedureReturn sqlite3_dbHandle
           Else
               LastMessage$ = "Couldn't create database"
           EndIf
       Else
           LastMessage$ = "There already exists a file named '" + sDataBase + "'"    
       EndIf
   EndIf
       ProcedureReturn #False
   
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
       ProcedureReturn #False
   EndIf            
EndProcedure


ProcedureDLL.l SQ3_4_LB_CloseDB(lDBhnd.l)                               ; close a database
                                                                       ; returns #True or #False  
   If sqlite3DLL
   
   If lDBhnd = 0
       LastMessage$ = "Wrong Database-Handle"
       ProcedureReturn #False
   EndIf

   hResult = CallCFunctionFast(sqlite3_close, lDBhnd)

   If hResult = #SQLITE3_OK
       LastMessage$ = #MSG_OK$
       
       ForEach OpenFiles()
                   If OpenFiles() = lDBhnd
                       DeleteElement(OpenFiles()) : Break
                   EndIf          
           Next

       ProcedureReturn #True
   Else
       LastMessage$ = "Couldn't close Database"
       ProcedureReturn #False
   EndIf
   
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
       ProcedureReturn #False
   EndIf        
EndProcedure


ProcedureDLL.l SQ3_4_LB_Execute(sSQLQuery.s, lDBhnd.l)                  ; execute SQL commands (Insert,Update,Delete)
                                                                       ; that do not return a recordset      
   If sqlite3DLL                                                       ; returns #True or #False                
   
   If lDBhnd = 0
       LastMessage$ = "Wrong Database-Handle"
       ProcedureReturn #False
   EndIf

   hResult = CallCFunctionFast(sqlite3_exec, lDBhnd, sSQLQuery, #Null, #Null, @ReturnValue)

   If hResult = #SQLITE3_OK
       LastMessage$ = #MSG_OK$
       ProcedureReturn #True
   Else
       CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
       LastMessage$ = PeekS(ReturnValue)
       ProcedureReturn #False
   EndIf
   
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
       ProcedureReturn #False
   EndIf        
EndProcedure


ProcedureDLL.l SQ3_4_LB_GetRecordset(sSQLQuery.s, lDBhnd.l,PtrUserRS.l) ; get results of a SELECT query in a recordset
                                                                       ; current row is set to 1    
   *User_RS = PtrUserRS ; make parameter global                        ; RS (=Recordset) structured variable is
                                                                       ; copied to User_RS in the calling program  
   If sqlite3DLL                                                       ; returns #True or #False  
       
   If lDBhnd = 0
       LastMessage$ = "Wrong Database-Handle"
       ProcedureReturn #False
   EndIf
   
   SQ3_4_LB_ReleaseRecordset()                                     ; changed: memory from previous call
                                                                   ;          is released automatically

   hResult = CallCFunctionFast(sqlite3_get_table, lDBhnd, sSQLQuery, @lResultsPtr, @lRows, @lCols, @ReturnValue)

   If hResult = #SQLITE3_OK

       RS\Handle = lResultsPtr
       RS\Rows = lRows
       RS\Cols = lCols
       RS\CurrentPos = 1
       RS\BOF = #True

       If lRows > 0
           RS\EOF = #False
       Else
           RS\EOF = #True
       EndIf

       LastMessage$ = #MSG_OK$
       CopyMemory(@RS,*User_RS,SizeOf(RS))
       ProcedureReturn #True

   Else
       CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
       LastMessage$ = PeekS(ReturnValue)
       ProcedureReturn #False
   EndIf
   
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
       ProcedureReturn #False
   EndIf    
EndProcedure


ProcedureDLL.l SQ3_4_LB_GetFieldIndexByName(sFieldname.s)               ; fieldindex by fieldname
                                                                       ; fieldindex is counted from 0 To RS\Cols - 1
   If RS\Handle = 0                                                    ; returns field (column) index
       LastMessage$ = #MSG_NO_RS$                                      ; or -1 for Error
       ProcedureReturn -1

   Else

       Address.l  = RS\Handle

       ; Search named Column
       For Col.l  = 0 To RS\Cols - 1
           If sFieldname = PeekS(PeekL(Address + Col  * 4))
               LastMessage$ = #MSG_OK$
               ProcedureReturn Col
           EndIf
       Next

   EndIf
   LastMessage$ = "No such Columnname: '" + sFieldname + "'"
   ProcedureReturn -1
EndProcedure


ProcedureDLL.s SQ3_4_LB_GetFieldNameByIndex(lIndex.l)                   ; fieldname by fieldindex (column header)
                                                                       ; fieldindex is counted from 0 To RS\Cols - 1
   If RS\Handle = 0                                                    ; returns field (column) name or
       LastMessage$ = #MSG_NO_RS$                                      ; empty string in case of an error
       ProcedureReturn Empty$

   Else
       If lIndex < 0 Or lIndex > RS\Cols - 1
           LastMessage$ = "No such Columnindex: '" + Str(lIndex) + "'"
           ProcedureReturn Empty$
       Else    
           Address.l  = RS\Handle
           LastMessage$ = #MSG_OK$
           ProcedureReturn PeekS(PeekL(Address + lIndex  * 4))
       EndIf
   EndIf

EndProcedure


Procedure GetStringAddress(lIndex.l)                                         ; private function to calculate address
                                                                            ; of data item, is stored in RS\StrAdr
                                   
   ValueAddress = PeekL(RS\Handle + (lIndex * 4) + (RS\CurrentPos * RS\Cols) * 4)
   If ValueAddress ; To avoid NULL-Values
       RS\StrAdr = ValueAddress
   Else
      RS\StrAdr = @Empty$
   EndIf
     
EndProcedure


ProcedureDLL.l SQ3_4_LB_GetRecordsetValueByName(sFieldname.s)                ; data item by name in the current row
   
   If RS\Handle = 0
       LastMessage$ = #MSG_NO_RS$
       ProcedureReturn #False
   EndIf
     
   myCol = SQ3_4_LB_GetFieldIndexByName(sFieldname)

   If myCol > -1
       LastMessage$ = #MSG_OK$
       GetStringAddress(myCol)
       CopyMemory(@RS,*User_RS,SizeOf(RS))
       ProcedureReturn #True
   Else
       LastMessage$ = "No such Columnname: '" + sFieldname + "'"
       RS\StrAdr = @Empty$
       CopyMemory(@RS,*User_RS,SizeOf(RS))
       ProcedureReturn #False
   EndIf    
EndProcedure


ProcedureDLL.l SQ3_4_LB_GetRecordsetValueByIndex(lIndex.l)                   ; data item by index in the current row
                                                                            ; columns index is zero based    
   If RS\Handle = 0
       LastMessage$ = #MSG_NO_RS$
       ProcedureReturn #False
   EndIf
   
   If lIndex < 0 Or lIndex > RS\Cols - 1
       LastMessage$ = "No such Columnindex: '" + Str(lIndex) + "'"
       RS\StrAdr = @Empty$
       CopyMemory(@RS,*User_RS,SizeOf(RS))
       ProcedureReturn #False
   EndIf
   
   LastMessage$ = #MSG_OK$
   GetStringAddress(lIndex.l)
   CopyMemory(@RS,*User_RS,SizeOf(RS))
   ProcedureReturn #True
         
EndProcedure


ProcedureDLL.l SQ3_4_LB_GetRecordsetValueOfRow(Separator$)                   ; retrieve the current row
                                                                            ; resulting string has fields separated        
   ReturnString$ = ""                                                       ; by character specified in Separator$
   Separator$ = Left(Separator$,1)
   
   If RS\Handle = 0
       LastMessage$ = #MSG_NO_RS$
       ProcedureReturn #False
   EndIf
   
   For i = 0 To RS\Cols - 1
       GetStringAddress(i)
       ReturnString$ = ReturnString$ + PeekS(RS\StrAdr) + Separator$
   Next i    
   
   ReturnString$ =  Mid(ReturnString$,1,Len(ReturnString$)-1)
   RS\StrAdr = @ReturnString$
   CopyMemory(@RS,*User_RS,SizeOf(RS))
   ProcedureReturn #True
   
EndProcedure


ProcedureDLL SQ3_4_LB_RecordsetMoveFirst()                                   ; move to first row in recordset
                                                                            ; has no return value  
   If RS\Handle = 0                                                         ; updates User_RS    
       LastMessage$ = #MSG_NO_RS$
       ProcedureReturn
   EndIf
   
   RS\BOF = #True
   RS\EOF = #False

   RS\CurrentPos = 1

   ;If RS\CurrentPos = RS\Rows
   ;    RS\EOF = #True
   ;EndIf
   
   CopyMemory(@RS,*User_RS,SizeOf(RS))
 
EndProcedure


ProcedureDLL SQ3_4_LB_RecordsetMoveLast()                                    ; move to last row in recordset
                                                                            ; has no return value  
   If RS\Handle = 0                                                         ; updates User_RS
       LastMessage$ = #MSG_NO_RS$
       ProcedureReturn
   EndIf
   
   RS\BOF = #False
   RS\EOF = #True

   RS\CurrentPos = RS\Rows

   ;If RS\CurrentPos = 1
   ;    RS\BOF = #True
   ;EndIf
   
   CopyMemory(@RS,*User_RS,SizeOf(RS))
     
EndProcedure


ProcedureDLL SQ3_4_LB_RecordsetMoveNext()                                    ; move to next row in recordset
                                                                            ; has no return value
   If RS\Handle = 0                                                         ; updates User_RS
       LastMessage$ = #MSG_NO_RS$
       ProcedureReturn
   EndIf
   
   RS\BOF = #False
   RS\EOF = #False

   RS\CurrentPos + 1

   If RS\CurrentPos > RS\Rows
       RS\CurrentPos = RS\Rows
       RS\EOF = #True
   EndIf

   If RS\CurrentPos = 1
       RS\BOF = #True
   EndIf
   
   CopyMemory(@RS,*User_RS,SizeOf(RS))
           
EndProcedure


ProcedureDLL SQ3_4_LB_RecordsetMovePrevious()                                ; move to previous row in recordset
                                                                            ; has no return value
   If RS\Handle = 0                                                         ; updates User_RS
       LastMessage$ = #MSG_NO_RS$
       ProcedureReturn
   EndIf
   
   RS\BOF = #False
   RS\EOF = #False

   RS\CurrentPos  - 1

   If RS\CurrentPos < 1
       RS\CurrentPos = 1
       RS\BOF = #True
   EndIf

   If RS\CurrentPos = RS\Rows
       RS\EOF = #True
   EndIf
   
   CopyMemory(@RS,*User_RS,SizeOf(RS))
   
EndProcedure


ProcedureDLL SQ3_4_LB_RecordsetMoveToRow(RowIndex.l)                         ; move to specified  row
                                                                            ; has no return value  
   If RS\Handle = 0                                                         ; updates User_RS
       LastMessage$ = #MSG_NO_RS$                                           ; RowIndex is counted from 1 to RS\Rows
       ProcedureReturn                                                      ; if RowIndex is out of bounds
   EndIf                                                                    ; CurrentPos is set to 1 or RS\Rows
   
   RS\CurrentPos = RowIndex
   RS\BOF = #False
   RS\EOF = #False

   If RS\CurrentPos <= 1
       RS\CurrentPos = 1
       RS\BOF = #True
   EndIf
   
   If RS\CurrentPos >= RS\Rows
       RS\CurrentPos = RS\Rows
       RS\EOF = #True
   EndIf

   CopyMemory(@RS,*User_RS,SizeOf(RS))
     
EndProcedure


ProcedureDLL SQ3_4_LB_ReleaseRecordset()                                     ; release memory used by recordset
 
   If sqlite3DLL    
             
   If RS\Handle <> 0
       
           CallCFunctionFast(sqlite3_free_table, RS\Handle)
           
       RS\BOF        = #True
       RS\EOF        = #True
       RS\Handle     = 0
       RS\Rows       = 0
       RS\Cols       = 0
       RS\CurrentPos = 0
       RS\StrAdr     = @Empty$
       CopyMemory(@RS,*User_RS,SizeOf(RS))
       
   EndIf
   LastMessage$ = #MSG_OK$
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
   EndIf
EndProcedure


ProcedureDLL.l SQ3_4_LB_LastInsertRowID(lDataBaseHandle.l)                   ; returns rowid of the most recent INSERT    
                                                                            ; or zero if no insert occurred
   If sqlite3DLL
       
       If lDataBaseHandle = 0
           LastMessage$ = "Wrong Database-Handle"
           ProcedureReturn
       EndIf

       ProcedureReturn CallCFunctionFast(sqlite3_last_insert_rowid, lDataBaseHandle)
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
   EndIf
EndProcedure


ProcedureDLL.s SQ3_4_LB_Version()                                            ; returns version of this dll as string
 ProcedureReturn #SQ3_4_LB_VERSION$                            
EndProcedure


ProcedureDLL.s SQ3_4_LB_GetLastMessage()                                     ; returns error-message as string  
   ProcedureReturn LastMessage$
EndProcedure


ProcedureDLL.s SQ3_4_LB_GetLibVersion()                                      ; version of sqlite3.dll used
                                                                            ; returned as string
   If sqlite3DLL
       ProcedureReturn PeekS(CallCFunctionFast(sqlite3_libversion))
   Else
       LastMessage$ = #MSG_SQLITE3_NOTLOADED$
       ProcedureReturn Empty$
   EndIf
EndProcedure


ProcedureDLL.s SQ3_4_LB_About()                                              ; returns credits string
   
   ver$ = SQ3_4_LB_GetLibVersion()
   If ver$ : ver$= "v" + ver$ : EndIf
   ver2$ = SQ3_4_LB_Version()
   ReturnString$ = ""
   ReturnString$ + "SQ3_4_LB.dll is a wrapper for sqlite3.dll to be used with Liberty Basic" + Chr(13)
   ReturnString$ + "It is based on the PureBasic OpenSource Libraries (PBOSL) " + Chr(13)
   ReturnString$ + "SQLite3 Userlibrary V1.1 (21.10.2005) by  Peter 'Kiffi' Tübben" + Chr(13)
   ReturnString$ + "sqlite3.dll "+ver$+" is by  D. Richard Hipp" + Chr(13)
   ReturnString$ + "SQ3_4_LB.dll v"+ver2$+" written by Gerd Heger 'Sputnik'  9/2006"+ Chr(13)
   ReturnString$ + "Compiled with PureBasic v4.00 by Fantaisie Software"+ Chr(13)
   ProcedureReturn ReturnString$
EndProcedure

; IDE Options = PureBasic v4.00 (Windows - x86)
; ExecutableFormat = Shared Dll
; CursorPosition = 419
; FirstLine = 372
; Folding = -----
; Executable = ..\SQ3_4_LB.dll
; DisableDebugger
; VersionField8 = SQ3_4_LB.dll
; VersionField16 = VFT_DLL

[Chris Iverson]

I'm not too familiar with the SQLite library, but it should have a way of running a parameterized query - one that completely segregates data and code. The problem is that it's interpreting the % in the headers of both PNG and PDF files as a control character, instead of as raw data.

Do you have any links to the library you're using?


EDIT: Ah, ok. Yeah, to be able to use parameterized queries, you need access to the various sqlite3_bind_*() functions in the SQLite3 library(not the wrapper).

www.sqlite.org/c3ref/bind_blob.html


Beyond that, I'm not sure. You can try Base64-encoding the data, which will make it longer, but will survive being put through the SQL parser.

You can also try manually sanitizing the blob, but that could be error-prone.

[metro]

Thanks Chris,
I'll read the info on that link.
Thanks for changing the permissions so others can chime in

Laurie

[metro]

Chris thanks for the tip to use a BASE64 encoder
using www.base64encode.net/ , I was able to encode the*.png file I want to store
I copied the result to a new file and loaded into my Sqlte DB with success.
I then extracted the data from the DB and pasted that back into the decoder
the result was a clone of my original *.png.
That's half the problem solved
The issue now is........https://rosettacode.org/wiki/Reports:Tasks_not_implemented_in_Liberty_BASIC
this link displays BASE64 encoding not implemented.
has anyone done any work with this or know of a command line program I can use.

thanks in advance

[Rod]

It does seem that base64 transform is built in to the command line. But that is a really quirky syntax as you have to pipe files from one place to the other. Google it and experiment. The home spun version is perfectly possible but would be ever so slow to enact the transformation.

I also have in my mind the preferences option to filter bad characters. In theory that is for the ide. But is there any difference turning that option on or off in your testing?

[Chris Iverson]

There's an API call you can use to do the conversion. Let me look up some code.



The "filter bad characters" option wouldn't apply here, as it's not something that's being put into the IDE.

Actually, if it did apply, it would probably make the problem worse, since those "bad characters" are required for the file formats of the various files to work properly.

[metro]

Thanks Rod and Chris
I have spent some time reading, it seems simple to use openssl via a batchfile
unfortunately I now have to resolve my issue with running 32bit Liberty in wine with 64bit OS
I have a conflict somewhere with winecfg.

A Dll would be great Chris if you get the time to investigate.

thanks for the input

[Chris Iverson]

Here ya go. It's a bit of a long one, due to the needed wrapper/helper functions, but direct usage is fairly straightforward.

var$ = "Some random test data"
base64$ = DataToBase64$(var$)

print base64$
print Base64ToData$(base64$)

Function DataToBase64$(string$)
   Call InitCrypto

   CRYPT.STRING.BASE64 = 1

   lenString = len(string$)
   lenFinal = 0
   a = CryptBinaryToString(string$, lenString, CRYPT.STRING.BASE64, dummy$, lenFinal)

   buf$ = space$(lenFinal)
   a = CryptBinaryToString(string$, lenString, CRYPT.STRING.BASE64, buf$, lenFinal)

   DataToBase64$ = buf$
   Call EndCrypto
End Function

Function Base64ToData$(base64$)
   Call InitCrypto

   CRYPT.STRING.BASE64 = 1

   lenBase64 = len(base64$)
   lenFinal = 0
   skip = 0
   outFlags = 0
   a = CryptStringToBinary(base64$, lenBase64, CRYPT.STRING.BASE64, dummy$, lenFinal, skip, outFlags)

   buf$ = space$(lenFinal)
   a = CryptStringToBinary(base64$, lenBase64, CRYPT.STRING.BASE64, buf$, lenFinal, skip, outFlags)

   Base64ToData$ = buf$
   Call EndCrypto
End Function


Sub InitCrypto
   Open "crypt32.dll" for DLL as #crypt32

   Global CRYPT.STRING.BASE64
   CRYPT.STRING.BASE64 = 1
End Sub

Sub EndCrypto
   close #crypt32
End Sub

Function CryptBinaryToString(pBinary$, cbBinary, dwFlags, byref pszString$, byref strLen)
   struct a, strLen as ulong
   a.strLen.struct = strLen

   if strLen = 0 then
       CallDLL #crypt32, "CryptBinaryToStringA",_
       pBinary$ as ptr,_
       cbBinary as ulong,_
       dwFlags as ulong,_
       0 as ulong,_
       a as struct,_
       CryptBinaryToString as long
   else
       CallDLL #crypt32, "CryptBinaryToStringA",_
       pBinary$ as ptr,_
       cbBinary as ulong,_
       dwFlags as ulong,_
       pszString$ as ptr,_
       a as struct,_
       CryptBinaryToString as long
   end if

   strLen = a.strLen.struct
End Function

Function CryptStringToBinary(pString$, cbString, dwFlags, byref pBinary$, byref cBinary, byref pdwSkip, byref pdwFlags)
   struct a, binLen as ulong
   a.binLen.struct = cBinary

   struct b, pdwSkip as long
   b.pdwSkip.struct = pdwSkip

   struct c, pdwFlags as long
   c.pdwFlags.struct = pdwFlags

   if cBinary = 0 then
       CallDLL #crypt32, "CryptStringToBinaryA",_
       pString$ as ptr,_
       cbString as ulong,_
       dwFlags as ulong,_
       0 as long,_
       a as struct,_
       b as struct,_
       c as struct,_
       CryptStringToBinary as long
   else
       CallDLL #crypt32, "CryptStringToBinaryA",_
       pString$ as ptr,_
       cbString as ulong,_
       dwFlags as ulong,_
       pBinary$ as ptr,_
       a as struct,_
       b as struct,_
       c as struct,_
       CryptStringToBinary as long
   end if

   cBinary = a.binLen.struct
   pdwSkip = b.pdwSkip.struct
   pdwFlags = c.pdwFlags.struct
End Function

[metro]

Fantastic, thanks Chris
I'll test this out in a few hours

I appreciate the help

[metro]

I have finally got a chance to take a closer look
Sorry Chris I cant figure our why Base64ToData$ always remains a string of spaces " "
it does change in length but CryptStringToBinary() does not seem to give the correct result.

I'm a little out of my depth with this one.

EDIT:

source.winehq.org/WineAPI/crypt32.html

seems its another wine issue (linux version would be great)

works as expected on window10

Thanks Chris

EDIT 2 :

for any other Linux user I installed crypt32.dll through winetricks

which downloads and install I believe a winXP version of the dll

it takes quite a while for some reason.

but it was worth it Chris's code works as it should.