Re: Storing Binary Data

Here are two functions I wrote about two years ago to do this very thing.
You will have to modify them to fit your needs, but it will give you a
start.

intGetPicture = SQLGetLargeObject("Student", "StudentID = " &
strStudentID, "Picture", "C:\temppic.jpg", "select * from Student where
StudentID = " & strStudentID)

' 0 = OK
' 1 = Error
' 2 = No piture available
Function SQLGetLargeObject(strTableName As String, strThisRow As String,
strField As String, strFile As String, strSQL As String) As Integer

Dim rsFetch As ADODB.Recordset
Dim intFileHandle As Integer
Dim lngFileLength As Long
Dim byteMemChunk() As Byte

SQLGetLargeObject = 1

' Open a recordset of the table
Set rsFetch = New ADODB.Recordset

With rsFetch

'.Open strTableName, adoConnection, adOpenKeyset, adLockOptimistic,
adCmdTable
.Open strSQL, adoConnection, adOpenKeyset, adLockOptimistic, adCmdText
.MoveFirst
.Find strThisRow

' If the end of the recordset is reached then exit with error
If .EOF = True Then
rsFetch.Close
Set rsFetch = Nothing
'SevereErrorMessage "Could not find the requested row when looking
for a large object."
Exit Function
End If

End With

' Get the length of the stored object
lngFileLength = -1
lngFileLength = rsFetch.Fields(strField).ActualSize

'DebugShow "Size of blob:" & CStr(lngFileLength)

' Check to see if blob available
If lngFileLength = 0 Then
SQLGetLargeObject = 2
Exit Function
End If

' Initialize the file where to store the blob
intFileHandle = FreeFile
Open strFile For Binary Access Write As intFileHandle

ReDim byteMemChunk(1 To lngFileLength)

' Get it from the database
byteMemChunk() = rsFetch.Fields(strField).GetChunk(lngFileLength)

' ... and store in the file
Put intFileHandle, , byteMemChunk()

' Tidy up
Close
rsFetch.Close
Set rsFetch = Nothing

SQLGetLargeObject = 0

End Function

strSQL = "update Student set Picture = ? where StudentID = " & Me.Tag
If SQLPutLargeObject(strSQL, "Student", Me.picStudent.Tag) = False
Then
DebugShow "Could not update the student table with a new picture."
bError = True
End If

Function SQLPutLargeObject(strSQL As String, strPlaceName As String, strFile
As String) As Boolean

Dim adoCommand As ADODB.Command
Dim adoParmFile As ADODB.Parameter
Dim intFileHandle As Integer
Dim lngFileLength As Long
Dim byteMemChunk() As Byte
Dim lngRecordsAffected As Long

Set adoCommand = New ADODB.Command

SQLPutLargeObject = False

' Set up the command
adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = strSQL
adoCommand.CommandType = adCmdText

DebugShow strSQL

' Open the file for reading
intFileHandle = FreeFile
Open strFile For Binary Access Read As intFileHandle
lngFileLength = LOF(intFileHandle)

' If file length is 0 then exit function
If lngFileLength = 0 Then
' Tidy Up
Close
Set adoCommand = Nothing
'SevereErrorMessage "Could not open " & strFile
Exit Function
End If

' The fourth parameter indicates the memory to allocate to store the
object
Set adoParmFile = adoCommand.CreateParameter(strPlaceName,
adLongVarBinary, adParamInput, lngFileLength + 100)
ReDim byteMemChunk(1 To lngFileLength)
Get intFileHandle, , byteMemChunk()

' Insert the object into the parameter object
adoParmFile.AppendChunk byteMemChunk()
adoCommand.Parameters.Append adoParmFile

' Now execute the command
adoCommand.Execute lngRecordsAffected

'DebugShow "Records affected:" & lngRecordsAffected

' Tidy Up
Set adoCommand = Nothing
Close

SQLPutLargeObject = True

End Function

-----Original Message-----
I would like to know how to store and retrieve binary data
in PostgreSQL thourgh ODBC from Visual Basic Program. Please help me for
this. I want to store image files. This is most urgent.