Lists: | pgsql-odbc |
---|
From: | "A Mohan" <abmohan75(at)rediffmail(dot)com> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Storing Binary Data |
Date: | 2003-03-24 11:26:00 |
Message-ID: | 20030324112600.30310.qmail@webmail27.rediffmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
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.
Regards,
A.Mohan
_______________________________________________________________________
Odomos - the only mosquito protection outside 4 walls -
Click here to know more!
http://r.rediff.com/r?http://clients.rediff.com/odomos/Odomos.htm&&odomos&&wn
From: | "Mike Miller" <mike(at)psy(dot)otago(dot)ac(dot)nz> |
---|---|
To: | <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Storing Binary Data |
Date: | 2003-03-24 23:43:51 |
Message-ID: | 000201c2f25f$39c76920$1bf014ac@gwain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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.