SQLite Blob from file the easy way, retrieve via callback

[pdwyer]

SQLite has a neat feature whereby you can load binary data into a blob field using hex so that you don't have to do all the preping and binding etc (which while prefered by some people I like just the raw SQL)

Not possible before PB4 due to the string limit you can now load files into the DB with normal SQL, you just need a fast Bin2Hex() function (supplied free of charge )

In the code below, you are prompted for a file, it saves it, the name and size into the db then you are prompted for a directory and it dumps all the files out of the DB into that directory (so you can check they are okay), running this a few times shows you the dumping process better as the DB contains more files.

The insert statement for doing this uses the X' <hexdata> ' format like this

Code: Select all

sSQL = "Insert into FileDB (Filename,FileSize ,Filedata) values('MyFile', 1000, X'123456789ABCDEF')"   

The file is actually stored in binary though, not as a string of hex which you can see from the DB size and when you return the data you get a pointer which you just dump to disk using the saved file info it doesn't pass back the data in hex but I guess this means that this method has an overhead of the conversion to hex by the programmer and the conversion to binary by the SQLite DLL.

Anyway, here is the example:

Code: Select all

#SQLITE3_ISO8859         =   1   ;
#SQLITE3_OK              =   0   ;'*/ Successful result */
#SQLITE3_ERROR           =   1   ;'*/ SQL error or missing database */
#SQLITE3_INTERNAL        =   2   ;'*/ An internal logic error in SQLite */
#SQLITE3_PERM            =   3   ;'*/ Access permission denied */
#SQLITE3_ABORT           =   4   ;'*/ Callback routine requested an abort */
#SQLITE3_BUSY            =   5   ;'*/ The database file is locked */
#SQLITE3_LOCKED          =   6   ;'*/ A table in the database is locked */
#SQLITE3_NOMEM           =   7   ;'*/ A malloc() failed */
#SQLITE3_READONLY        =   8   ;'*/ Attempt to write a readonly database */
#SQLITE3_INTERRUPT       =   9   ;'*/ Operation terminated by sqlite3_interrupt() */
#SQLITE3_IOERR           =   10   ;'*/ Some kind of disk I/O error occurred */
#SQLITE3_CORRUPT         =   11   ;'*/ The database disk image is malformed */
#SQLITE3_NOTFOUND        =   12   ;'*/ (Internal Only) Table or record not found */
#SQLITE3_FULL            =   13   ;'*/ Insertion failed because database is full */
#SQLITE3_CANTOPEN        =   14   ;'*/ Unable to open the database file */
#SQLITE3_PROTOCOL        =   15   ;'*/ Database lock protocol error */
#SQLITE3_EMPTY           =   16   ;'*/ (Internal Only) Database table is empty */
#SQLITE3_SCHEMA          =   17   ;'*/ The database schema changed */
#SQLITE3_TOOBIG          =   18   ;'*/ Too much data for one row of a table */
#SQLITE3_CONSTRAINT      =   19   ;'*/ Abort due to contraint violation */
#SQLITE3_MISMATCH        =   20   ;'*/ Data type mismatch */
#SQLITE3_MISUSE          =   21   ;'*/ Library used incorrectly */
#SQLITE3_NOLFS           =   22   ;'*/ Uses OS features not supported on host */
#SQLITE3_AUTH            =   23   ;'*/ Authorization denied */
#SQLITE3_ROW             =   100  ;'*/ sqlite3_step() has another row ready */
#SQLITE3_DONE            =   101  ;'*/ sqlite3_step() has finished executing */

#SQL_LIB = 1 ; dll ID

; may need a "/headers" option for csv2sql cmdline
;if no headers then table col names are...? col1,col2 etc?

Structure MemoryArray 
    Byte.c[0] 
    word.w[0]
EndStructure 

Structure HexBin 
    StructureUnion
        word.w
        Str.s{2} 
    EndStructureUnion
EndStructure

Declare ProcMain()
Declare.s Bin2Hex(*pdata, lLen.l)
Declare CallBack_ReturnRows(NullPtr.l,ArgCount.l,*Values.l, *ColNames.l)

Global FolderName.s

ProcMain()

;==========================================================

Procedure.l ProcMain()

    sDBFileName.s = "BlobFile.db"
    Filename.s = OpenFileRequester("Add File to Database", "", "*.*", 0)

    hFile.l
    RetResult.l
    hDB.l
    FileLen.l
    sSQL.s
    
    hFile = ReadFile(#PB_Any,Filename)
        FileLen = Lof(hfile)
        *BytePtr = AllocateMemory(FileLen)  
        ReadData(hfile, *BytePtr, FileLen )  ;FileLen
    CloseFile(hfile)

    CreateDB.s =            "CREATE TABLE 'FileDB' (" + #CRLF$ 
    CreateDB = CreateDB +   "'ID' INTEGER  Not NULL PRIMARY KEY AUTOINCREMENT," + #CRLF$
    CreateDB = CreateDB +   "'Filename' TEXT  NULL," + #CRLF$
    CreateDB = CreateDB +   "'Filesize' INTEGER  NULL," + #CRLF$
    CreateDB = CreateDB +   "'FileData' blob  NULL" + #CRLF$
    CreateDB = CreateDB +   ")"    
    CreateDB = ReplaceString(CreateDB, "'", #DOUBLEQUOTE$)

    OpenLibrary(#SQL_LIB, "sqlite3.dll")

    ;Create Table
    RetResult = CallCFunction(#SQL_LIB, "sqlite3_open" ,sDBFileName, @hDB)
    Debug RetResult
 
RetResult = CallCFunction(#SQL_LIB, "sqlite3_exec" ,hDB, CreateDB, #Null, #Null, @ReturnValue)
    If RetResult = 1
        Debug PeekS(ReturnValue)
    EndIf
       
    sSQL = "Insert into FileDB (Filename,FileSize ,Filedata) values('" + GetFilePart(Filename) + "'," + Str(FileLen) + ", X'" + Bin2Hex(*BytePtr, FileLen) + "')"
    RetResult = CallCFunction(#SQL_LIB, "sqlite3_exec" ,hDB, sSQL, #Null, #Null, @ReturnValue)
    If RetResult = 1
        Debug PeekS(ReturnValue)
    EndIf
    
    ;Get Folder to write to
    FolderName = PathRequester("Enter Folder path to dump files", "c:\")
        ;Send  Query
        QueryRet.l = CallCFunction(#SQL_LIB, "sqlite3_exec" ,hDB, "Select * from FileDB", @CallBack_ReturnRows() , #Null, @ReturnValue) 
        If QueryRet
            Debug  "ret:" + PeekS(ReturnValue)
        EndIf 
    
    RetResult = CallCFunction(#SQL_LIB, "sqlite3_close" ,@hDB) 
    CloseLibrary(#SQL_LIB)
        

EndProcedure

; ==========================================================

Procedure.s Bin2Hex(*pdata, lLen.l)

    Dim Hexs.HexBin(255)
    ReturnBuffer.s = Space(lLen * 2)
    
    For I = 0 To 255
        hexs(i)\Str = RSet(Hex(i),2,"0")
    Next

    *StrByteArray.MemoryArray = *pdata
    *HexByteArray.MemoryArray = @ReturnBuffer

    For i = 0 To lLen - 1
        *HexByteArray\word[i] = hexs(*StrByteArray\byte[i])\word
    Next

    ProcedureReturn ReturnBuffer

EndProcedure

; ==========================================================

ProcedureC.l CallBack_ReturnRows(NullPtr.l,ArgCount.l,*Values.l, *ColNames.l) ;argcount = column count
  
    Dim ValuePts.l(ArgCount)
    Dim ColPts.l(ArgCount)

    For ValLoop.l = 0 To ArgCount -1
         ;Get Data Pointers  
        ValuePts(ValLoop) = PeekL(*Values + (ValLoop * 4)) 
        ColPts(ValLoop) = PeekL(*ColNames + (ValLoop * 4))
        
        ;Get Data for current column
        If LCase(PeekS(ColPts(ValLoop))) = "filename"
            Filename.s = PeekS(ValuePts(ValLoop)) 
        EndIf
 
         If LCase(PeekS(ColPts(ValLoop))) = "filesize"
            Filesize.l = Val(PeekS(ValuePts(ValLoop))) 
        EndIf
               
        If LCase(PeekS(ColPts(ValLoop))) = "filedata"
            *PtrFiledata = ValuePts(ValLoop)
        EndIf

    Next

    ;Write File
    hFile.l = OpenFile(#PB_Any,FolderName + "\" + Filename) 
    WriteData(hFile, *PtrFiledata, Filesize)
    CloseFile(hfile)   
        
    ProcedureReturn  0  ;Proceed with next row

EndProcedure                               

;============================================================

Now, with PB's compression lib you could make your own backup program or photo album I suppose

[rsts]

Hi Paul,

Took me a while, but I finally got around to really looking at this. It's a great example.

I already have a database into which I insert images as blob data from clipboard captures but I'm fascinated at the possibility of getting the data directly from a file. I've adapted this method to accomodate small items.

Do you know of a way, or if this could be adapted, to read from and write to a file which is larger than the available memory - e.g. a way to 'chunk' data into and from the sqlite database as a single record? Or would we have to create multiple items in the database for each 'chunk' of the file?

Your examples of sqlite have been very beneficial. Please keep them up as you get a chance.

cheers

[pdwyer]

I think you'd have to split across records otherwise you'd end up paging out to disk and losing most of the performance.

I would probably have two tables, one with all the information about the files you want to keep, the other with just data chunks of a set size (1mb or whatever)

So table one might have

FileID, Finename, ChunkCount, FileSize, CRC

and table 2 has

FileID, ChunkNo, ChunkData

Then get the data out something like

Code: Select all

Select Chunkdata from Table2 where FileID in (Select FileID from Table1 where filename = 'c:\Myfile') order by ChunkNo

And stream the data back to disk one record at a time

[rsts]

Thanks for the answer.

Was just hoping there was some way to build single sqlite records that were larger than what I could accomodate in memory at any particular time. Some way like using pipes or something to build the record, but still have it be a single record.

Thanks again for your contributions to our (my) understanding of sqlite.

cheers

[pdwyer]

With what you are trying to do I suspect that All DBs can do it and yet none of them can..

In that, all DBs can return a row larger than the physical memory of a system by using disk swapping but I've never heard of a method of querying a database so that data coming out of the DB goes into memory more granularly than one record at a time (even if that Row is set just as a sinlge column - thus 1 field).

I think that backup/restore programs that archive files that are gigs in size do it by chopping it up to store, this means you can be as granular as you want to keep memory usage low.

I don't see any disadvantage of chopping things up except that it adds a little extra complexity. A lot less complexity that writing your own file archiving system to handle streaming out of a single record.

I might have a play with it later as the idea interests me If I get a sample working I'll post it

[rsts]

Yes, the more I think about it the more I agree it's going to have to be 'chunked' some way in order to effectively manage it.

I'll probably just go with mutiple records which I can combine for a single entity as I write them back out.

Thanks again.

P.S. Yes, If you do work something up, please post it.

cheers

[USCode]

It would be cool if SQLite allowed you to insert into a BLOB directly from a file, maybe something like this:

sSQL = Insert into FileDB (Filename,FileSize ,Filedata) values('MyFile', 1000, C:\TEMP\MYFILE.DAT)