Re: Storing Binary Data - Mailing list pgsql-odbc

From Mike Miller
Subject Re: Storing Binary Data
Date
Msg-id 000201c2f25f$39c76920$1bf014ac@gwain
Whole thread Raw
In response to Storing Binary Data  ("A Mohan" <abmohan75@rediffmail.com>)
List pgsql-odbc
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.


pgsql-odbc by date:

Previous
From: "Dave Page"
Date:
Subject: Re: Message for: dpage
Next
From: "Joachim Zauner"
Date:
Subject: Fastest way to read tuples