Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: Extracting BLOB into file system

    Hello all,

    I have read access to a database that appears to store files as "BLOBS".

    I am trying to create a VB macro that extracts the blob file to a location (lets say... my desktop) as an easy way of viewing the content of that file.

    I am using Oracle SQL Developer with Read access to the database. The BLOBS can be located but I am unable to find a SQL string that will convert the blobs back into files located on my desktop.

    I can locate the blob in the database and obviously it appears appears as a text string. I have heard of two keywords: DBMS_LOB and UTL_File however im not sure if it is necessary for my situation or not.

    apologies for not understanding too much, I am not only new to databases but also new to storing files as strings in a database.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If the BLOBs are actual files, then you can just stream the binary data out to a file, and voila. The file can then be opened by whatever program created it. Just be sure you write the data as binary, and not character data.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by MCrowley View Post
    If the BLOBs are actual files, then you can just stream the binary data out to a file, and voila. The file can then be opened by whatever program created it. Just be sure you write the data as binary, and not character data.
    I am currently trying to get out ONE blob. I know that its a zip file.
    the field it is saved in as type "TEXT".

    You are right, I want to stream the binary data out to a file.
    so here is what I have (current written in VB as I want to be able to select SEQUENCE values from Excel and drag the associated files out.)

    please excuse my poor code!

    Code:
    Sub test100()
    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open "DRIVER=SQL Server; SERVER=<removed>; UID=user; " & _
            "PWD=user; APP=dbname; WSID=TOLD091; DATABASE=dbname;"
    
    
    Set rs = New ADODB.Recordset
    rs.Open "select top 1 FILEBLOB from dbname.table WHERE SEQUENCE = '776620'", cn, adOpenKeyset, adLockOptimistic
    
    
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write rs.Fields(0)
    mstream.SaveToFile "C:\Users\Kevin\Downloads\test.zip", adSaveCreateOverWrite
    
    rs.Close
    cn.Close
    
    End Sub
    but I get the error:
    "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

    on the line "mstream.Write rs.Fields(0)"

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm. The fact that the field in the table is defined as "TEXT" may mean this is already broken. when you select the field in Management Studio, does it show a lot of unprintable gibberish, or does it start with 0x?

  5. #5
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by MCrowley View Post
    Hmm. The fact that the field in the table is defined as "TEXT" may mean this is already broken. when you select the field in Management Studio, does it show a lot of unprintable gibberish, or does it start with 0x?
    it contains this:

    Code:
    begin 660 <name of file>.zip
    M4$L#!!0``@`(`,EE,$30_HR/RAa```",```?````82UU:S(P,30P,#$W,R`H
    <more and more gibberish...>
    4``````$``0!-````!R``````#0HQ
    end
    does this help?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, it does. See if you can stream the text into a file, and then open that file with WinZip. With luck, it may work, but you may get a couple of extra characters at the end that Winzip may not know what to do with.

  7. #7
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by MCrowley View Post
    Actually, it does. See if you can stream the text into a file, and then open that file with WinZip. With luck, it may work, but you may get a couple of extra characters at the end that Winzip may not know what to do with.
    just wondering... if i was to view this in developer tools, then copy the field and pasted the text into notepad and saved it as a .zip

    would it open correctly?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It's worth a try.

  9. #9
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by MCrowley View Post
    It's worth a try.
    is it the same thing though? (copying and pasting the data and saving it rather than streaming it to a file)

    especially because you mentioned that there might be char's added at the end of the string that would mess the file up.

  10. #10
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by MCrowley View Post
    It's worth a try.
    hm... when I stream to a file and open the zip... there is a zip inside which looks like the file (so it appears streaming it puts it into a file that saves it as a zip... instead i just want to stream it directly out.)

    taking the contents and putting it into a file and renaming it to zip works better but not correctly... it asks me if I want to decode the file which I can accept or reject but the actual message inside of the zip file is unreadable =(
    the file name looks like the name of the attachment but with a few extra chars on the end that look like gibberish.

    i had a thought that it might be to do with carriage returns (if its stored on a unix system and pulled from a windows system) or maybe its related to the layout of the data...

    would it be:
    Code:
    begin 660 (filename.zip)
    
    iawuiuhawuguag
    
    end
    with returns at either end... im so unfamiliar I feel like im stabbing into the dark but maybe I am quite close :P

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm nearly (99.9999%) certain that your file has been UUENCODED.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    I'm nearly (99.9999%) certain that your file has been UUENCODED.

    -PatP
    is it possible to decode this in SQL or vb... bare in mind that I do not have the ability to download applications or libraries (otherwise i would never have had this problem in the first place lol)

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It might be possible to UUDECODE a file within SQL, it can definitely be done in VB, but it is trivial from a batch/cmd/PowerShell file. I'd recommend one of the easy choices if you can.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •