• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Binary Data from SQL database to actual FIle?

I'm developing a system to house all kinds of files in a SQL database. I need to be able to send these files via email, so I'm trying to create a temp file to attach to the email that is generated. I'm able to create an empty file, open it for writing but I'm getting error when i try to put the file. My Question is do I need to loop through the data byte by byte or can I just dump the whole thing. Either way I'd appreciate the code for the PUT statement or the loop?

This is the code that's generating the error

    intFileNumber = FreeFile
    Open str For Binary Access Write Lock Read Write As #intFileNumber
    'Write binary data to the file
    Put #intFileNumber, , objRecordSet("binary_data")
    'Close the file
    Close #intFileNumber

Thanx in advance

  • 5
  • 5
1 Solution
use an ado mstream..

'General Decs
Dim picrs As ADODB.Recordset
Dim mstream As ADODB.Stream

'In a sub somewhere
On Error GoTo e_trap
    Screen.MousePointer = vbHourglass
    Set picrs = New ADODB.Recordset
    picrs.Open sqlLine, cn, adOpenKeyset, adLockOptimistic
   mstream.Write picrs!MYBINARYFIELD
   mstream.SaveToFile App.path & "\MYFILENAME.EXT", SaveCreateOverWrite


make sure to change MYBINARYFIELD to your field in your table, MYFILENAME.EXT to the filename you want to output to
and also that "sqlLine" is deinfed as your sql statement to pick a specific record (ex..  "select * from mytable where fileNo = 12")

jfeldhakeAuthor Commented:
I tried that and I'm getting the following error

3704:Operation is not allowed when the object is closed.

Do I need to open the stream object or initialize it in some way
oops my bad...

sorry, yes you need to open a connection to your server

'Leave this in a module
Public cn As New ADODB.Connection
Public rs As ADODB.Recordset
Public cmd As ADODB.Command

'Put this in a connecting sub somewhere...i usually leave this connection open through the duration of my application
' Specify the OLE DB provider.
    cn.Provider = "sqloledb"
    ' Set SQLOLEDB connection properties.
    cn.Properties("Data Source").value = userServer
    cn.Properties("Initial Catalog").value = DATABASE_NAME
    ' Decision code for login authorization type:
    ' Windows NT or SQL Server authentication.
    cn.Properties("User ID").value = userID
    cn.Properties("Password").value = userPW
    ' Open the database.
    On Error GoTo e_trap

i'm not sure if you need the CMD defined the the global module to allow your program to work

hope this helps

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

jfeldhakeAuthor Commented:
Sorry I need to be more specific

I'm writing this in an ActiveX dll FYI

This is the code I have
Dim objConn
Dim objRecSet
Dim objStream

Set objConn = CreateObject("ADODB.Connection")
objConn.Open Connection_String

Set objRS = CreateObject("ADODB.RecordSet")
objRS.Open SQL_String, objConn

Set objStream = CreateObject("ADODB.Stream")
objStream.Write objRS("binary_field")
objStream.saveToFile Full_File_Path, SaveCreateOverWrite

Set objStream = Nothing
Set objRS = Nothing
Set objConn = nothing

What am I doing wrong?
what errors are u getting?

that code is coming directly off a project i'm currently working on where I upload jpeg images to an sql field, and then a client can choose to 'save' the file in order to email it.


jfeldhakeAuthor Commented:
Err.Number = 3704
Err.Description = Operation is not allowed when the object is closed.

Sounds Like we are working on a very similar project.
This is so that documents of any type can be uploaded to a SQL database and then displayed or sent on request.
I have everything working accept sending the file as an attachment. The process I'm working on creates a document in a temporary folder. Creates an email with that document as an attachment. Sends the email. Then destroys the file.

The error seems to indicate that the stream object needs to be "Opened". I haven't worked with ADODB.Stream before so I may just be missing the obvious here.

can you f8 through the process and find out which line its kicking out on?

looks like we are working on the exact same project...in fact, i'll take your activex when your done =)

I'm trying to go to pdf first by way of shell (since its a jpeg scan originally)

the reason i'm not so much help, is that i'm working on a different portion of the program and I had left the binary emailing till last :(

Sorry this is taking so long!

jfeldhakeAuthor Commented:
The error is happening on the following statement

objStream.Write objRS("binary_field")

is there an objStream.Open method that I need to perform before the .Write

If you can get me through this you can have the ActiveX.

That code is working for me without any open statements. I do know that my connection is globally open and the recordset is being opened exactly where yours is....

if i think of anything i'll message back (sorry for a sort of incomplete answer thus far)

jfeldhakeAuthor Commented:
Good Solution, but I did have to use a Stream.Open statement to get it to work. FYI

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now