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 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 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.Write ByteArray

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


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)


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")
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


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)
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.