Binary Data from SQL database to actual FIle?

Posted on 2003-03-07
Medium Priority
Last Modified: 2010-05-01
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

Question by:jfeldhake
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5

Accepted Solution

o_Rage_o earned 750 total points
ID: 8090282
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")


Author Comment

ID: 8090433
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

Expert Comment

ID: 8090544
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
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!


Author Comment

ID: 8090720
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?

Expert Comment

ID: 8090821
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.



Author Comment

ID: 8090880
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.


Expert Comment

ID: 8090914
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!


Author Comment

ID: 8091032
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.

Expert Comment

ID: 8103129

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)


Author Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question