Bernard wrote: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?
