Retrieve not encoded data from blob

[dontotore]

Hello,
I need to retrieve some data from a BD that I cannot modify.
The data are stored in a BLOB columns and contains not coded images.
This is the begin pieces of one of the data:

HEX:

Code: Select all

FFD8 FFE0 0010 4A46 4946 0001 0201 0060 0060

TEXT:

Code: Select all

ÿØÿà..JFIF.....'.'

The data retrieved from livecode is:

Code: Select all

×þß

The code I use is this:

Code: Select all

   put "SELECT "&tCol&" FROM "&tTab&" WHERE "&tWhere into query
   put revQueryDatabaseBlob(DbId,query) into DbCurs
   put empty into tData
   get revDatabaseColumnNamed(DbCurs, tCol, "tData")
   print tData

It seems that the 5th char end the retrievment of the binary data.
Is there any method I can use for retrieve the entire image?
I repeat, I cannot modify the DB and cannot use external (need portability).

Thank you very much!

[dontotore]

I forgot to mention thet the Database is SQLite.

No one know how to retrieve binary data from it?

[Klaus]

Hi dontotore,

found this in the docs:
...
To pass binary data in a variable in the variablesList, prepend "*b" to the variable name.
The revQueryDatabase function strips the binary marker "*b" and passes it to the database as binary data, rather than text data.
To pass binary data in an array element, prepend "*b" to the element's value.
...
So why not directly fetch the desired blob?
Try this, I suppose all variables hold the correct vlaues:
...
put "SELECT" && tCol && "FROM" && tTab && "WHERE" && tWhere into tQuery
put empty into tData
get revQueryDatabase(DbId,tQuery,"*btData")
## do something with tData
...

But what do you mean with the line:
...
print tData
...
??? You cannot print a variable!?


Best

Klaus

[dontotore]

Hello, sorry for the late reply.

Code: Select all

get revQueryDatabase(DbId,tQuery,"*btData")

is used for insert a blob in the DB, I'm trying to RETRIEVE blob from the sqlite DB so it is not good.

Code: Select all

print tData

sorry, is not print, is LOG:

Code: Select all

LOG tData

this is a message i've written for write the retrieved data in a field, but i've tryed without success also:

Code: Select all

   put "c:\myimage.jpg" into theFilePath
   open file theFilepath for binary write
   write tData to file theFilePath
   close file theFilePath
   
   put "c:\myimage2.jpg" into theFilePath2
   put tData into URL ("binfile:" & theFilePath2)
   
   
   put "c:\myimage3.jpg" into theFilePath3
   open file theFilepath3 for write
   write tData to file theFilePath3
   close file theFilePath3
   
   put "c:\myimage4.jpg" into theFilePath4
   put tData into URL ("file:" & theFilePath4)

I really don't know what to do anymore, can sombody help me?
Thanks

[Bernard]

get revQueryDatabase(DbId,tQuery,"*btData")

That is used to SELECT data from a database.

If you try to use it, do you get data in the variable "tData"?

[dontotore]

get revQueryDatabase(DbId,tQuery,"*btData")

That is used to SELECT data from a database.

If you try to use it, do you get data in the variable "tData"?

From the dictionary:

The revQueryDatabase function returns a record set ID which designates the record set (database cursor) selected by the SQLQuery. The record set ID is an integer.

and

revQueryDatabase(databaseID,SQLQuery[,{variablesList | arrayName}])
To pass binary data in a variable in the variablesList, prepend "*b" to the variable name.

You are right, is used for SELECT data, for example if you need to select all the rows in a table with a specific image:

Code: Select all

put URL ("binfile:" & "C:\Mq95hke.jpg") into tData
put revQueryDatabase(DbId,"SELECT * FROM table1 WHERE theImage=:1","*btData")

Anyway, sometimes the dictionary is wrong so i've tryed as you suggested, but no data is inserted in the variable after the query

******************************

In this days I've done many experiment with livecode and sqlite and found something VERY IMPORTANT:

you can extract binary data from a database ONLY IF you have inserted them by LiveCode

The reason, I think, is because Livecode does an automatic encode on the data during the INSERT or UPDATE, and an automatic decode during the SELECT.
If the retrieved blob is not encoded by Livecode, the decoding function will fail and return "empty" or garbage.

For the insert i've used:

Code: Select all

   put URL ("binfile:" & "C:\Mq95hke.jpg") into tImageData
   put "UPDATE myTable SET blob1 = :1 WHERE ind= 1" into tQuery
   revExecuteSQL DbId, tQuery, "tImageData"

For the test i've used:

Code: Select all

   put "SELECT blob1 FROM myTable WHERE ind=1" into query --inserted with LiveCode ( code above)
   put revQueryDatabase(DbId,query) into DbCurs
   put revDatabaseColumnNamed(DbCurs, "blob1", "tDataLiveCode") into tErrLiveCode


   put "SELECT blob1 FROM myTable WHERE ind=2" into query --same image inserted with SQLite Expert
   put revQueryDatabase(DbId,query) into DbCurs
   put revDatabaseColumnNamed(DbCurs, "blob1", "tDataNotLC") into tErrNotLC

   
   put URL ("binfile:" & "C:\Mq95hke.jpg") into tImageData
   LOG char 0 to 30 of tImageData --OUT:  ÿØÿâXICC_PROFILE
   LOG char 0 to 30 of tDataLiveCode  --OUT:   ÿØÿâXICC_PROFILE
   LOG char 0 to 30 of tDataNotLC  --OUT:    ×þß

From SQLite Expert (and other sqlite manager software), if i read the blobs, the first chars are:
Image inserted with Livecode: À??"L˜‰ƒƒŸ’†‰Œ…@AA@@LˆŒ©®¯BP
Image inserted with SQLite Expert ( same with other soft): ÿØÿâXICC_PROFILE

So the only answer for me is that Livecode encode the data during an insert or update and cannot retrieve non encoded data.

Am I missing something? Is there any way to disable the automatic encoding/decoding? Why we cannot retrieve raw data?

[kizmeth]

Hi dontototore, I have the same issue.... Have you found a solution?
Thank's

[dontotore]

Unfortunately there is no solution in livecode, so I'm writing externals giving up on portability.
I really would like to know why the developer have done something like this.

Greetings.

[Klaus]

Hi friends,

I have a faint memory, that the current SQLite implementation of Livecode cannot retrieve BLOB
data from a database correctly, if the blobs have NOT been inserted with Livecode.

But this is said to be fixed in LC 6.6 DP1:
http://downloads.livecode.com/livecode/

At least worth a try


Best

Klaus

[dontotore]

WOW!!! IT WORKS!!

Thank you so much!!

[Klaus]

Great! So my memory was right