Incorrect data from database

[Gurgen]

Hi all,

I have a simple code that downloads .zip archive from server, extracting it into temp files and after saves the data on database in base64 format. The problem is that when I read this data from DB, where the type is "BLOB", I get incorrect simbols which are not in base64 format.


Code to save the data

Code: Select all

            -- Extract the file from zip archive
            revZipExtractItemToFile pArchive, tItem, tFileLocation
            
            -- Get the file data
            put URL("binfile:" & tFileLocation) into tFileBin
            
            if tFileBin is not empty then
               -- Encode the file to base64
               put base64Encode(tFileBin) into tFileBase64
               
               -- Insert the file to DB
               put "INSERT INTO images values('" & tItem & "','" &  tFileBase64 & "')" into tSQL
               revExecuteSQL gMediaDbID, tSQL

code to read the data

Code: Select all

          put revQueryDatabase(gMediaDBID,"select imagedata from images where imageid ='"& pID &"'") into tCursor
   
       --Read the image from the database
        put revDatabaseColumnNamed(tCursor, "imagedata", "tImage") into tErrorMsg

       revCloseCursor tCursor --Close Cursor

        put base64Decode(tImage) into tImage

Thanks in advance.

[makeshyft]

I'm not sure if this is the problem or if you are using sqlite...but i know that working with blobs requires a "binary" connection to the sqlite db...

from the dictionary for revOpenDatabase:

put revOpenDatabase("sqlite", "mydb.sqlite", "binary") -- open the connection in the 'new' binary mode

[MaxV]

From http://livecode.wikia.com/wiki/SQLite :
Working with binaries
In order to upload binaries, you need to use the variable in the revExecuteSQL.
The SQLStatement may contain one or more placeholders, which are sequential numbers prepended by a colon. The revExecuteSQL command substitutes the corresponding item in the variablesList for each of these placeholders. For example, if you have two variables called "valueX" and "valueY", you can use a SQLStatement that includes placeholders as follows:

Code: Select all

revExecuteSQL myID, "insert into mytable values(:1,:2)", "valueX","valueY"

when using binaries, you have to insert the *b prefix in variable name; so if you variable containing a binary is "valueX", the correct code is:

Code: Select all

revExecuteSQL connID, "insert into mytable values(:1)", "*bvalueX"

Since the revExecuteSQL command strips the binary marker "*b" and passes it to the database as binary data, rather than text data.
In order to get binary you must use revQueryDatabase and revDatabaseColumnNamed (no other way), for example to get the image data store in a database wher images data are in a image column:

Code: Select all

put revQueryDatabase(connID, "SELECT * FROM images WHERE id=" & field "id" & ";") into tRecordSet
put revDatabaseColumnNamed(tRecordSet, "image", tImage) into tError
revCloseCursor tRecordSet

Example with UPDATE:

Code: Select all

 put "UPDATE flags SET  logo=:1  WHERE ID="& tID &" ;" into tSQL 
 revExecuteSQL connID,tSQL,"*blogo"