PureBasic Forums - English

Posted: **Fri Jun 20, 2008 6:26 am**

Kiffi has been very patient with me and provided me with an excellent example of adding a blob to a database and then updating it in-place. He gave permission to post his example to me below.

The original source is from ts-soft which is based on an
older code from him and uses the "SQLite3 BaseFunction-Include for Win + Lin + Unicode" includes found in those posts.

http://www.purebasic.fr/english/viewtop ... 707#194707

Code: Select all

EnableExplicit

XIncludeFile "SQLite3_Include.pbi"
; --> http://www.purebasic.fr/english/viewtopic.php?p=240853#240853

Define.l File, hDB, lStatement
Define.l SizeOfBlob, MemOfBlob

If SQLiteInit() = #False : Debug "Can't initialisize SQLite3" : End : EndIf

; ------------------------------------------
; Reading PureBasicLogo.bmp
; ------------------------------------------

File = ReadFile(#PB_Any, #PB_Compiler_Home + "examples/sources/data/PureBasicLogo.bmp")
If File = #False : Debug "Can't open PureBasicLogo.bmp" : End : EndIf
SizeOfBlob = Lof(File) : MemOfBlob = AllocateMemory(SizeOfBlob)
If MemOfBlob = #False : Debug "Can't allocate memory" : End : EndIf
ReadData(File, MemOfBlob, SizeOfBlob) : CloseFile(File)

; ------------------------------------------
; Open Database (here: in memory)
; ------------------------------------------

hDB = SQLiteOpen(":memory:")
If hDB = #False : Debug "Can't create database" : End : EndIf

SQLiteExecute(hDB, "Create Table tblBlob (fldBlob BLOB)")

; ------------------------------------------
; Insert Blob into table
; ------------------------------------------

lStatement = SQLitePrepare(hDB, "insert into tblBlob (fldBlob) values (?);")
If lStatement
  SQLiteBindBlob(lStatement, 1, MemOfBlob, SizeOfBlob)
  SQLiteStep(lStatement)
  SQLiteFinalize(lStatement)
EndIf

; ------------------------------------------
; checking, if the correct object is in the database
; ------------------------------------------

lStatement = SQLitePrepare(hDB, "Select fldBlob From tblBlob")
If lStatement
  SQLiteStep(lStatement)
  SizeOfBlob = SQLiteColumnByte(lStatement, 0)
  MemOfBlob  = SQLiteColumnBlob(lStatement, 0)
  If MemOfBlob
    File = CreateFile(#PB_Any, "logo.bmp")
    If File
      WriteData(File, MemOfBlob, SizeOfBlob) : CloseFile(File)
      RunProgram("logo.bmp")
    EndIf
  EndIf
  SQLiteFinalize(lStatement)
EndIf

MessageRequester("", "After the picture appears on your screen, click on 'OK' to update the blob with another picture")

; ------------------------------------------
; Reading PureBasic.bmp
; ------------------------------------------

File = ReadFile(#PB_Any, #PB_Compiler_Home + "examples/sources/data/PureBasic.bmp")
If File = #False : Debug "Can't open PureBasic.bmp" : End : EndIf
SizeOfBlob = Lof(File) : MemOfBlob = AllocateMemory(SizeOfBlob)
If MemOfBlob = #False : Debug "Can't allocate memory" : End : EndIf
ReadData(File, MemOfBlob, SizeOfBlob) : CloseFile(File)

; ------------------------------------------
; Update Blob in table
; ------------------------------------------

lStatement = SQLitePrepare(hDB, "update tblBlob set fldBlob = ?;")
If lStatement
  SQLiteBindBlob(lStatement, 1, MemOfBlob, SizeOfBlob)
  SQLiteStep(lStatement)
  SQLiteFinalize(lStatement)
EndIf

; ------------------------------------------
; checking, if the correct object is in the database
; ------------------------------------------

lStatement = SQLitePrepare(hDB, "Select fldBlob From tblBlob")
If lStatement
  SQLiteStep(lStatement)
  SizeOfBlob = SQLiteColumnByte(lStatement, 0)
  MemOfBlob  = SQLiteColumnBlob(lStatement, 0)
  If MemOfBlob
    File = CreateFile(#PB_Any, "logo.bmp")
    If File
      WriteData(File, MemOfBlob, SizeOfBlob) : CloseFile(File)
      RunProgram("logo.bmp")
    EndIf
  EndIf
  SQLiteFinalize(lStatement)
EndIf

SQLiteClose(hDB)

SQLiteEnd()

Note: This is just adding and updating a single blob in a single record database with no other data. I learned enough from this example to add blob handling to my main (current) project that adds normal table data and blobs and updates them all. Wasn't too bad to learn.

Thanks Kiffi!

Posted: **Fri Jun 20, 2008 9:46 am**

funny... I didn't know the term 'blob' in connection with databases...

Posted: **Fri Jun 20, 2008 10:10 am**

@Kaeru Gaman: http://www.purebasic.fr/german/viewtopi ... 674#115674

(i know, long time ago)

Greetings ... Kiffi

Posted: **Fri Jun 20, 2008 11:34 am**

now can you get the PB implimentation of of SQLite to do this?

Posted: **Fri Jun 20, 2008 12:11 pm**

pdwyer wrote:now can you get the PB implimentation of of SQLite to do this?

i'm afraid, i don't understand your question.

in Native-PB there are no equivalents for

Code: Select all

SQLitePrepare()
SQLiteBind*()
SQLiteStep()
SQLiteFinalize()

so need to include the *.pbi from ts-soft.

Greetings ... Kiffi

Posted: **Fri Jun 20, 2008 12:45 pm**

A good thing with the include is just how easy it is to use. PB includes the basic command set for all databases but the extras (as Kiffi pointed out) don't exist so that the PB command set is unified across all databases supported.

Sure, you could import the functions from the library and use prototypes to make your missing commands but mixing pb database commands and imported functions is messy and may lead to more bugs (At least it does for me!!).

Using the *source* wrapper is great as it gives you a coherent command set; all starting with the same starting identifying name "SQLite" to use on it, no confusion.

Some people says that wrappers are bad but if the wrapper is source code, where is the problem? You study it and modify it to suit yourself.

And, if many people use the same wrapper instead of importing their own functions that they specifically need, supporting each other becomes a whole lot easier.

Posted: **Fri Jun 20, 2008 2:04 pm**

Kiffi wrote:
pdwyer wrote:now can you get the PB implimentation of of SQLite to do this?
i'm afraid, i don't understand your question.

in Native-PB there are no equivalents for...

So you do understand and your answer is "No"

Posted: **Fri Jun 20, 2008 2:38 pm**

What's a blob like fangbeast doing talking about blobs?

Posted: **Fri Jun 20, 2008 2:46 pm**

srod wrote:What's a blob like fangbeast doing talking about blobs?

Whis is a bloke with a face like a babboon's backside talking about me being a blob eh?? That's a g-string sniffing tart calling a blob a blob.

Posted: **Fri Jun 20, 2008 2:48 pm**

Fangbeast wrote:Whis is a bloke with a face like a babboon's backside talking about me ......

I wish!

Wait......

Posted: **Fri Jun 20, 2008 3:10 pm**

srod wrote:
Fangbeast wrote:Whis is a bloke with a face like a babboon's backside talking about me ......
I wish!

Wait......

I can't wait damnit!! I am off to steal the g-string you have been sniffing, to stop the vicious lies.

Psss, srod.. did the cheque arrive yet???

Posted: **Fri Jun 20, 2008 10:48 pm**

I prefer the late 80's version of the Blob to the 1950's... Much better camera work!

Posted: **Fri Sep 26, 2008 12:26 am**

Rook Zimbabwe wrote:I prefer the late 80's version of the Blob to the 1950's... Much better camera work!

LOL

And it was real "software"!

But i like this one, too...

Greetings
Pforzheimer

Posted: **Fri Sep 26, 2008 2:08 am**

just 4 more pforzheimer and you can edit your profile!

Posted: **Fri Sep 26, 2008 7:08 am**

Could a moderator lock this topic please? Enough off-topic spam has been generated I think. Makes it harder for new people to find anything actually useful.

PureBasic Forums - English

Working with BLOBS baby. yeah!

Working with BLOBS baby. yeah!