Prev: ImmGetDescription to find out Japanese IME
Next: APPDATA folder question
From: Sarah M. Weinberger on 16 Dec 2008 18:13
Hi All,

(Olaf, I forgot to mention in my response to you. I am using cCnn.Execute. I
just did not remember the method correctly in my head. I apologize for
that.)

I asked Olaf (thecommon.net), if I could have permission to post his awesome
response to me on how to insert a blob into a SQLite record. If you ask me,
the how to insert a blob was not obvious, even after searching on Google. I
think that Olaf's writeup is great even for those that are using C/C++ or
someone else's library (why?). He gives an insightful explanation on how
SQLite treats blobs, which is something not too aparent from the
documentation.

Also, I sure hope that this community will be helpful, as I for one, would
love to see the VB6 compiler/IDE project come to fruition. Microsoft is not
doing anything, and Classic VB is still the best.

I agree with Olaf that the second of the two simpler methods, if it can be
used, is the best. The first method translating the blob into hex code is
soemthing that is nice to know and do once, although you got me on the why
(think school), and then never do again (think real life). Hey, one has to
have a bit of a sense of humor with dry stuff, although VB6 with SQLite is
quite interesting stuff. I am looking forward to playing with the new
library that he has.

Anyways, here is what Olaf wrote me:

Regards,

Sarah

==================================================================================
Hi Sarah,





> How does your API deal with inserting a blob?

> I saw that different APIs handle that differently.

> If I have already read a blob (think image, Microsoft Word document,

> whatever) into a byte array. How do I pass that into the INSERT / Exec

> statement for your API.



> byBlobData = <binary data read in from somewhere>



> strSQLCommand = "INSERT INTO myTable VALUES (null, " + byBlobData + ");"



> cCnn.Exec strSQLCommand



Sarah, are you sure, you are using my wrapper?

Cannot remember an Exec-Method.

This one is named .Execute in my Connection-Class.



But maybe you've already wrapped the Connection- Object of my wrapper behind
another Class.



So here come the different methods for dhSQLite.



1. Direct Insert-Statements per SQL-Execute:

To insert Blob-Content over an SQL-Insert-Statement you will have to encode
your BlobBytes as HexChars

(1 Byte gives 2 HexChars, but whom do I tell that, sorry) So, assuming you
have a ByteArray with 3 Bytes, containing the Values 65, 66 and 67 (matching
the ANSI-Chars A,B,C) you will have to encode these three values as a
HexString in a speparate Routine with the result: 414243 More than that, the
result has to wrapped within SQL-Text-Delimiters '414243' and you will also
have to add a leading x before that HexString: x'414243'

Finally your Insert-Statement would have to look this way:

"INSERT INTO myTable VALUES (null, x'414243')"



As you see, that's unnecessary complicated - there are two additional ways:



2. Rs.UpdateBatch (IMO the easiest method) First select an empty Rs from
your Table where you want to run your inserts on:

Set Rs = Cnn.OpenRecordset("Select * from myTable Where 0")



Then add a new (yet empty) record to the Rs:

Rs.AddNew

(under the hood there was no DB-interaction here - every Rs-change happens
only inside the Rs, also when you call .Delete on the current Rs-Record or
when you change existing Record-Values inside the Rs-Fields)



Now we change Rs-Values of the new added record (if you omit a Field, so
that it remains empty, then this Field will be handled as
'NullContent'-Field in the appropriate "final UpdateBatch-Call")
Rs.Fields("MyBlobField").Value = byBlobData 'that's it



Now write the current Rs-Changes back into the DB (in our case we have only
added one single record, with only one Field with new content - I write
this, because you can of course add more records or also delete or update
record-Fields as you wish - you can always write back all changes at once
within one single UpdateBatch-Call which always works within an implicite
transaction.

Rs.UpdateBatch 'if there's no error raised here, ...

then the data is in the DB ... in case there

was an error, *all* changes on the DB

which came from this Rs were rolled back



3. CommandObjects (the fastest method)

Dim Cmd as cCommand

Set Cmd = Cnn.CreateCommand("INSERT INTO myTable VALUES(?,?)") (note the two
question-marks, since we want to insert two values)



Now the insert:

On Error Resume Next

Cnn.BeginTrans

Cmd.SetNull 1 'set the first FieldValue-Param to NULL

Cmd.SetBlob 2, byBlobData 'second Param to BlobBytes

Cmd.Execute 'execute the insert-command If Err.Number = 0 Then 'success

Cnn.CommitTrans

Else

Cnn.RollbackTrans

Endif



Ok, these are the three ways, to write data into an SQLite-DB using dhSQlite
(or dhRichClient, which works calling-compatible).



Please have a look also at my insert-performance-example within the
SQLiteDemo-VBProject.



Regards,



Olaf


 | 
Pages: 1
Prev: ImmGetDescription to find out Japanese IME
Next: APPDATA folder question