SQL and Blob question

[KennyR]

Ok...I have been reading all the posts regarding submitting binary data to a blob field in a SQL database, and from what I can tell, I have been able to add binary data. The issue I am having, I think, is retrieving the original formatting of the binary data (font, color etc) when the data is being retrieved from a query on the database. If I have no formatting on the document that was stored,(just plain text), the data imports to a field on my card correctly. But if you have any formatting on the document, I get extra characters or placeholders....(I think). Any suggestions on how to retrieve data like a txt file with the original formatting?

code for placing data into my table_

Code: Select all

on mouseUp
   --Connect to DB
   connectMe
   --Choose File
   answer file "Select a file:"
if the result is not "Cancel" then
   put it into tChosenFile
end if
put URL ("binfile:" & tChosenFile) into tDocuments
 
    -- check the global connection ID to make sure we have a database connection
    global gConnectionID
    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if
    
   --Table Names
    put "jcasMessages" into tTableName
    put "date,subject, body,attachment" into tFields

--Just some data for testing
put "" into tDate
put "TestDoc" into tSubject
    put "test" into tBody
    put tDocuments into tAttachment
    
    
    -- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
    put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3, :4)" into tSQL
    
    -- send the SQL to the database, filling in the placeholders with data from variables
    revExecuteSQL gConnectionID, tSQL,"tDate", "tSubject", "tBody", "tAttachment"
    
    -- check the result and display the data or an error message
    if the result is a number then
           answer info "Message Sent!"
    else
        answer error "There was a problem adding the record to the database:" & cr & the result
    end if
end mouseUp

Code for getting data into my field

Code: Select all

on mouseUp
   --connect to DB
      connectMe
    -- check the global connection ID to make sure we have a database connection
    global gConnectionID
    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if
    -- construct the SQL (this selects all the data from the specified table) 
    put "jcasMessages" into tTableName    -- set this to the name of a table in your database
    put "SELECT attachment FROM jcasMessages WHERE subject='TestDoc'" into btSQL
    
    -- query the database
    put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
    
    -- check the result and display the data or an error message
    if item 1 of btData = "revdberr" then
        answer error "There was a problem querying the database:" & cr & tData
    else
           put  tData into field "testing"
  
    end if
end mouseUp

[KennyR]

After doing some thinking about it I wonder if I have to allow the document to be opened up in a wordprocessing program instead of a field in order to keep its formatting....

[Simon]

How about "set the rtfText of field "testing" to tData"?

Not sure what happens if a .doc or .docx is selected.

Simon

[KennyR]

wow! That did the trick! I am going to have to tinker with it a bit more to see what other formats will do...like .pdf, .doc etc....Thanks for pointing this out man!

[KennyR]

so I am curious about viewing .PDF attachments in a standalone....From what I can tell by reading, the preferred method would be to use a revBrowserOpen command to view the binary data in a browser window. But what if I wanted to save the binary data to lets say the "Documents" folder as a .PDF then launch it using the default program on the computer for viewing .PDF's? Secondly, what distinguishes the binary data saved on the server from being a .PDF, .TXT or .DOC format? When I query the database for the binary data, how will the standalone figure out what type of format it is?

On a more simple note, what I am attempting to accomplish here is nothing more than an email type of client that allows one user to send a message and add an attachment if needed. The receiving computer just simply needs to read the message and open any attachments.

Edit- okay after looking at this link I'm going to use it to display the PDF files...but what I need to know is how I would distinguish file types saved as binary data so that I can save them with the appropriate extensions.

http://lessons.runrev.com/s/lessons/m/4 ... pdf-in-rev

[KennyR]

I don't mean to keep bumping this thread, but I want to make myself clear....What would be the preferred way in storing a .PDF on a SQL server table so that it can be retrieved and displayed in stack? When I store it as a BLOB on the server, the item is encoded, and when retrieved, it is encoded and not necessarily a .PDF...I assume I have to convert it somehow?

[phaworth]

I'll start with the disclaimer that I have not used BLOBs in an SQL database.

I'll assume that the db columns the binary data is going into is defined with datatype BLOB. First thing I notice is that, according to the dictionary, variables that contain binary data must have "b" prepended to their name and I didn't see that in your code. Next, when retrieving the data, the dictionary says don't use revDataFromQuery to get binary data. You can either encode/decode the binary data on input/output, or you can use revQueryDatabse/revDatabaseColumnNamed to read it in.

It sounds like you've got this working but I'd be nervous about relying on the way you are doing it in view of the dictionary notes.

Pete

[KennyR]

Thank you for your help! I have been meaning to get back to this topic since I just discovered how this all works. After a few long moonlight walks, a little crying and yelling at the dog, I realized I just needed to dig into the material and figure this out. Ultimately , the trick here is to retrieve the data from the blob column on the SQL server just like you would query any other column then base64Decode the binary data and write it to a file. It appears that LC encodes the data upon adding data to the server....I may be wrong here but from what I have read, that is what appears to be happening . I'll post my code shortly so that others can take a peek....

[KennyR]

just as promised, the bit of code I used to upload the binary to the SQL database and retrieve it....This is prob the worst method and I am sure smarter people here could do this is one line, but otherwise this works for me!

-- I use this answer dialog to select the file for upload....

Code: Select all

answer file "Please select a file.."
put it into fileToLoad
open file fileToLoad for binary read
read from file fileToLoad until end
put base64Encode(it) into tAttachments
close file fileToLoad

--I then have my data in binary form stored in the variable fileToLoad to insert into my SQL "insert" command
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL
revExecuteSQL gConnectionID, tSQL,"tSubject", "tBody", "tAttachment"

-- I use this to retrieve the binary data

Code: Select all

put "SELECT attachment FROM  jcasMessages WHERE date = '"&vText&"'" into btSQL
    -- query the database
    put revDataFromQuery(tab, cr, gConnectionID, btSQL) into btData
    put base64Decode(btData) into  vtemp

--Then I use this to write the file to disk and use it for whatever

Code: Select all

put specialFolderPath("desktop") & "/myData.pdf" into vPath
      open file vPath for binary write
       write vtemp to file vPath 
      close file vPath

[phaworth]

Hi Kenny,
Glad you found a way to make it work, that's one of the solutions mentioned in the dictionary. Once you base64Encode the data, it becomes text not binary data.

The only drawback I can see to that approach is if any applications other than yours need to access the blob data since they will be expecting binary data not base64Encoded binary data.

What version of Livecode are you using? Version 6.6 included some major changes to the SQLite database library, one of which was to store binary data as true binary data. Prior to that release it was apparently encoded in some way. I don't think you're using SQLite but I guess it's possible that the handling of binary data affected other SQL implementations.

Pete

[KennyR]

Hey phaworth...currently I use the latest build 7.0 rc2 I think... And no I don't use MySQL for this app...I have a server on godaddy ...I appreciate your insight...