The following is a VB example of how you would extract binary data from a column within a table, The example below connects to a MS Access Database file, but this can be modified to connect directly to a SQL database.

FUNCTION TO CREATE A CONNECTION TO THE DATABASE

Function GetSQLConn()
Const MDBFile = "C:\Program Files\Blackbaud\The Raisers Edge7\binarytest.mdb"
'Create Connection object
Dim GlobalADOConn
Set GlobalADOConn = CreateObject("ADODB.Connection")

'Open OLEDB connection To MDB/Jet engine
GlobalADOConn.Provider = "Microsoft.Jet.OLEDB.4.0"
GlobalADOConn.Open "Data Source=" & MDBFile
Set GetSQLConn = GlobalADOConn
End Function

FUNCTION TO SAVE BINARY DATA TO DISK

Function SaveBinaryData(FileName, ByteArray)
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

'Create Stream object
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

'Specify stream type - we want To save binary data.
BinaryStream.Type = adTypeBinary

'Open the stream And write binary data To the object
BinaryStream.Open
BinaryStream.Write ByteArray

'Save binary data To disk
BinaryStream.SaveToFile FileName, adSaveCreateOverWrite
End Function

MAIN FUNCTION TO EXTRACT & SAVE BINARY IMAGE DATA

This function retrieves binary data from the table. GetImageData returns a binary data of the ID from the WebData table. You can use output of the function for Response.BinaryWrite or for SaveBinaryData function.

Function GetImageData(ID)
Dim SQL, Conn, RS
'Create SQL command To retrieve data
SQL = "Select BinaryColumn from WebData"
SQL = SQL & " Where ID=" & ID

'Get connection To SQL database
Set Conn = GetSQLConn()
Set RS = Conn.Execute(SQL)

'GET binary data from recordset
GetImageData = RS("BinaryColumn")

'Use this code instead of previous line For ORACLE.
' GetImageData = RS("BinaryColumn").GetChunk( _
' RS("BinaryColumn").ActualSize)
End Function

'write image data To a disk
SaveBinaryData "C:\Profile1.gif", GetImageData(2)

SAMPLE VB CODE

Dim mediaid As Integer
Dim mediastring As String

mediaid = oMedia.Fields(EMediaFields.MEDIA_fld_ID)
mediastring = oMedia.Fields(EMediaFields.MEDIA_fld_Object)

Using cn As New OleDbConnection("Provider=SQLNCLI11;Password=EnterAPassword;Data Source=EnterASource;Persist Security Info=True;User ID=Webuser;Initial Catalog=RE7")
cn.Open()
Dim sqlText As String
sqlText = "SELECT [Object] " _
& "FROM [Media] " _
& "WHERE ([id] = " & mediaid & ")"

Using cm As New OleDbCommand(sqlText, cn)
Dim rdr As OleDbDataReader

rdr = cm.ExecuteReader

rdr.Read()

image = rdr.GetValue(0)
Dim ms As New MemoryStream(GetImageBytesFromOLEField(image))
Dim bm As System.Drawing.Bitmap
bm = System.Drawing.Image.FromStream(ms)
bm.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
image = ms.ToArray()
End Using
End Using

Public Function GetImageBytesFromOLEField(oleFieldBytes As Byte()) As Byte()
Const BITMAP_ID_BLOCK As String = "BM"
Const JPG_ID_BLOCK As String = "ÿØÿ"
Const JPG_ID_BLOCK_END As String = "ÿÙ"
Const JPG_ID_BLOCK2 As String = "ÿ Ø ÿ"
Const PNG_ID_BLOCK As String = ChrW(137) & "PNG" & vbCr & vbLf & ChrW(26) & vbLf
Const GIF_ID_BLOCK As String = "GIF8"
Const TIFF_ID_BLOCK As String = "II*" & vbNullChar


' Get a UTF7 Encoded string version
Dim u8 As Encoding = Encoding.UTF7
Dim strTemp As String = u8.GetString(oleFieldBytes)

' Get the first 300 characters from the string
Dim strVTemp As String = strTemp

' Search for the block
Dim iPos As Integer = -1
If strVTemp.IndexOf(BITMAP_ID_BLOCK) <> -1 Then
iPos = strVTemp.IndexOf(BITMAP_ID_BLOCK)
ElseIf strVTemp.IndexOf(JPG_ID_BLOCK) <> -1 Then
iPos = strVTemp.IndexOf(JPG_ID_BLOCK)
ElseIf strVTemp.IndexOf(JPG_ID_BLOCK2) <> -1 Then
iPos = strVTemp.IndexOf(JPG_ID_BLOCK2)
ElseIf strVTemp.IndexOf(PNG_ID_BLOCK) <> -1 Then
iPos = strVTemp.IndexOf(PNG_ID_BLOCK)
ElseIf strVTemp.IndexOf(GIF_ID_BLOCK) <> -1 Then
iPos = strVTemp.IndexOf(GIF_ID_BLOCK)
ElseIf strVTemp.IndexOf(TIFF_ID_BLOCK) <> -1 Then
iPos = strVTemp.IndexOf(TIFF_ID_BLOCK)
Else
Throw New Exception("Unable to determine header size for the OLE Object")
End If

' From the position above get the new image
If iPos = -1 Then
Throw New Exception("Unable to determine header size for the OLE Object")
End If

Dim iPosEnd As Integer = -1
If strVTemp.IndexOf(JPG_ID_BLOCK_END) <> -1 Then
iPosEnd = strVTemp.IndexOf(JPG_ID_BLOCK_END)
End If

Dim ms As New MemoryStream()
ms.Write(oleFieldBytes, iPos, oleFieldBytes.Length - iPos)

Return ms.ToArray()
End Function

Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.