Set and Get Blob (with Sqlite / ODBC)

[Duffer123]

Let's say I've created a table with several columns already and then I go to Set Blob command.

How do I know which numbered column /column index (0,1,2,3?) the blob column is at?

Does the Get Blob command work differently?

Or must I always create blob columns when first creating the table?

Do they have to be the first columns in a table?

Can someone shed some light.... thanks,

[TI-994A]

Hi Duffer123.

Let's say I've created a table with several columns already and then I go to Set Blob command.

The data types must be specified for each and every column at the time of creation.

How do I know which numbered column /column index (0,1,2,3?) the blob column is at?

Again, this would be according to how the table was created.

Does the Get Blob command work differently?

This would vary according to the tool used. In PureBasic, there are different functions for reading and writing strings, numerical values, and blobs from the database.

Or must I always create blob columns when first creating the table?

If the column is to be used for storing blobs (Binary Large OBjects), then yes.

Do they have to be the first columns in a table?

No.

[Duffer123]

@ TI-994A,

Thanks for getting back to me so quickly on this. I almost get it.

I couldn't persuade you to post some pseudo-code could I? Creating a table with a record/id column, two text columns and then say three blob columns and then getting data from the middle blob?

[infratec]

Hi Duffer123,

an example:

Code: Select all

UseSQLiteDatabase()


*Buffer = AllocateMemory(10)
FillMemory(*Buffer, 4, $32)

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
If DB
  SQL$ = "Create table records (id integer, t1 text, t2 text, b1 blob, b2 blob, b3 blob)"
  If DatabaseUpdate(DB, SQL$)
    SetDatabaseBlob(DB, 0, *Buffer, 4)
    SQL$ = "Insert into records (id, t2, b2) values (1, 'Hello', ?);"
    If DatabaseUpdate(DB, SQL$)
      FreeMemory(*Buffer)
      SQL$ = "Select id, t2, b1, b2, b3 From records Where id = 1;"
      If DatabaseQuery(DB, SQL$)
        If NextDatabaseRow(DB)
          Debug GetDatabaseLong(DB, 0)
          Debug GetDatabaseString(DB, 1)
          ; ignore b1 (DB, 2)
          BlobSize = DatabaseColumnSize(DB, 3)
          *Buffer = AllocateMemory(BlobSize)
          GetDatabaseBlob(DB, 3, *Buffer, BlobSize)
          ShowMemoryViewer(*Buffer, BlobSize)
          Debug PeekS(*Buffer, -1, #PB_Ascii)
          FreeMemory(*Buffer)
          ; ignore b3 (DB, 4)
        EndIf
        FinishDatabaseQuery(DB)
      EndIf
    EndIf
  EndIf
  CloseDatabase(DB)
EndIf

Bernd

[TI-994A]

I couldn't persuade you to post some pseudo-code could I? Creating a table with a record/id column, two text columns and then say three blob columns and then getting data from the middle blob?

Hi Duffer123. Here's a fully working example that demonstrates the creation, writing, and reading of such a database, with text and images. You could substitute the images with your own, or download them from the Dropbox links below (3 x ~30k jpeg files):

Code: Select all

UseSQLiteDatabase()
UseJPEGImageDecoder()

Enumeration
  #MainWindow
  #img1
  #txt1
  #txt2
  #btn1
  #btn2
  #dbID
EndEnumeration

Global dbFileName.s = "sample.db", currentRecord = 1

Procedure createSampleDatabase()
  If CreateFile(0, dbFileName)
    CloseFile(0)
    
    If OpenDatabase(#dbID, dbFileName, "", "")
      DatabaseUpdate(#dbID, "CREATE TABLE bio (idx INT, name CHAR(20), picture BLOB, dob CHAR(20))")
      Read.s name$
      Read.s dob$
      
      SetDatabaseBlob(#dbID, 0, ?picAlbert, ?picIsaac - ?picAlbert)
      queryString.s = "INSERT INTO bio (idx, name, picture, dob) values (1, '" + name$ + "', ?, '" + dob$ + "')"
      DatabaseUpdate(#dbID, queryString)
      
      Read.s name$
      Read.s dob$
      SetDatabaseBlob(#dbID, 0, ?picIsaac, ?picLeo - ?picIsaac)
      queryString.s = "INSERT INTO bio (idx, name, picture, dob) values (2, '" + name$ + "', ?, '" + dob$ + "')"
      DatabaseUpdate(#dbID, queryString)
      
      Read.s name$
      Read.s dob$
      SetDatabaseBlob(#dbID, 0, ?picLeo, ?binaryEnd - ?picLeo)
      queryString.s = "INSERT INTO bio (idx, name, picture, dob) values (3, '" + name$ + "', ?, '" + dob$ + "')"
      DatabaseUpdate(#dbID, queryString)
      
      CloseDatabase(#dbID)
    EndIf
    
  EndIf
EndProcedure

Procedure getNextRecord()
  If DatabaseQuery(#dbID, "SELECT * FROM bio WHERE idx = '" + currentRecord + "'")
    
    While NextDatabaseRow(#dbID)
      SetGadgetText(#txt1, GetDatabaseString(#dbID, 1))
      pictureSize = DatabaseColumnSize(#dbId, 2) 
      *picture = AllocateMemory(pictureSize)
      GetDatabaseBlob(#dbID, 2, *picture, pictureSize)
      CatchImage(1, *picture, pictureSize)
      ResizeImage(1, 300, 300)
      SetGadgetState(#img1, ImageID(1))
      SetGadgetText(#txt2, GetDatabaseString(#dbID, 3))
    Wend      
    
    FinishDatabaseQuery(#dbID)
    FreeMemory(*picture)
    
  EndIf
EndProcedure

createSampleDatabase()

If OpenDatabase(#dbID, dbFileName, "", "")
  wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
  OpenWindow(#MainWindow, 0, 0, 320, 390, "Database Example", wFlags)
  ImageGadget(#img1, 10, 10, 300, 300, 0)
  TextGadget(#txt1, 80, 320, 160, 30, "", #PB_Text_Center)
  TextGadget(#txt2, 80, 350, 160, 30, "", #PB_Text_Center)
  ButtonGadget(#btn1, 10, 320, 60, 30, "<< PREV")
  ButtonGadget(#btn2, 250, 320, 60, 30, "NEXT >>")  
  getNextRecord()
EndIf

Repeat
  Select WaitWindowEvent()
    Case #PB_Event_CloseWindow
      appQuit = 1
    Case #PB_Event_Gadget
      Select EventGadget()
        Case #btn1
          currentRecord - 1
          If currentRecord < 1 
            currentRecord = 3
          EndIf
          getNextRecord()
        Case #btn2
          currentRecord + 1
          If currentRecord > 3  
            currentRecord = 1
          EndIf
          getNextRecord()
      EndSelect
  EndSelect
Until appQuit = 1 

CloseDatabase(#dbID)

DataSection
  bio:
  Data.s "Albert Einstein", "March 14, 1879", "Isaac Newton", "January 4, 1643", "Leonardo da Vinci", "April 15, 1452"
  picAlbert: 
  IncludeBinary "albert.jpg"
  picIsaac: 
  IncludeBinary "isaac.jpg"
  picLeo: 
  IncludeBinary "leonardo.jpg"
  binaryEnd:
EndDataSection

Here are the sample images:
albert.jpg
isaac.jpg
leonardo.jpg

Alternatively, this zip file contains the source code as well as all the images; just unzip them and run:
PureBasic Database.zip

Hope it helps.

18th February 2019: updated download links

[Duffer123]

@ TI-994A,

That is totally excellent - thanks v much. V helpful....

[Duffer123]

@ TI-994A, All,

Using your code, with the Get Blob command, does the *buffer have to have come from a *buffer = AllocateMemory command or can it be any old pointer to any point in memory?

[infratec]

You already have an answer to this question.
Look in your other posting.

In general you can only access memory which is owned by your program.
It is not necessarry that it is a direct pointer from AllocateMemory()
It can be any address owned by your program.

Bernd

[Duffer123]

Thanks Bernd, think I may have found my way through my coding problems...

Must now test that and see...

[jassing]

Something to note on sqlite blobs... I had a text file to store in a blob; I was using the unicode string, but fts needed ascii ...
Fred would need to enable the ICU module...