Thread: Storing Binary Data
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
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.
Hello,
Unfortunately you will probably need to build the large object extension in.
In the source contrib directory there is a directory called lo. Below is
the readme for this directory. If you read it, it will explain the problem
(why oids don't work) and the solution. And of course how to install it.
Don't forget to run the lo.sql script on the database in question.
eg:
For my installation, I found the directory at:
/usr/src/postgresql-7.3.2/contrib/lo
Hope that helps ...
Mike.
README.lo
PostgreSQL type extension for managing Large Objects
----------------------------------------------------
Overview
One of the problems with the JDBC driver (and this affects the ODBC driver
also), is that the specification assumes that references to BLOBS (Binary
Large OBjectS) are stored within a table, and if that entry is changed, the
associated BLOB is deleted from the database.
As PostgreSQL stands, this doesn't occur. It allocates an OID for each
object, and it is up to the application to store, and ultimately delete the
objects.
Now this is fine for new postgresql specific applications, but existing ones
using JDBC or ODBC wont delete the objects, arising to orphaning - objects
that are not referenced by anything, and simply occupy disk space.
The Fix
I've fixed this by creating a new data type 'lo', some support functions,
and a Trigger which handles the orphaning problem.
The 'lo' type was created because we needed to differenciate between normal
Oid's and Large Objects. Currently the JDBC driver handles this dilema
easily, but (after talking to Byron), the ODBC driver needed a unique type.
They had created an 'lo' type, but not the solution to orphaning.
Install
Ok, first build the shared library, and install. Typing 'make install' in
the contrib/lo directory should do it.
Then, as the postgres super user, run the lo.sql script. This will install
the type, and define the support functions.
How to Use
The easiest way is by an example:
> create table image (title text,raster lo);
> create trigger t_image before update or delete on image for each row
> execute procedure lo_manage(raster);
Here, a trigger is created for each column that contains a lo type.
Issues
* dropping a table will still orphan any objects it contains, as the trigger
is not actioned.
For now, precede the 'drop table' with 'delete from {table}'. However,
this
could be fixed by having 'drop table' perform an additional
'select lo_unlink({colname}::oid) from {tablename}'
for each column, before actually dropping the table.
* Some frontends may create their own tables, and will not create the
associated trigger(s). Also, users may not remember (or know) to create
the triggers.
This can be solved, but would involve changes to the parser.
As the ODBC driver needs a permanent lo type (& JDBC could be optimised to
use it if it's Oid is fixed), and as the above issues can only be fixed by
some internal changes, I feel it should become a permanent built-in type.
I'm releasing this into contrib, just to get it out, and tested.
Peter Mount <peter@retep.org.uk> June 13 1998
-----Original Message-----
There is no data type lo like you mentioned in PGSQL. O.K.
Here I gave my table structure.
Table name photo:
ddocode varchar 5
treasurycode varchar 5
signature bytea
photo1 bytea
photo2 bytea
some times instead of bytea, I have used oid also.
>CREATE TABLE Student
>(
>StudentID integer NOT NULL,
>Picture lo,
>PRIMARY KEY( StudentID )
>);
>
>CREATE TRIGGER TRIGStudentPicture
>BEFORE UPDATE OR DElETE
>ON Student
>FOR EACH ROW
>EXECUTE PROCEDURE lo_manage(Picture);
>
>Make sure you are using the latest pgsql odbc driver from Hiroshi Inoue
>site as well.
--
Mike Miller,
Computer Programmer,
Department of Psychology,
University Of Otago
mike@psy.otago.ac.nz
+64 3 479 5402
...when you lay awake at night hoping that those elves from "The Elves and
the Shoemaker" know where you work and can program in C++ as well as they
can sew together sandles...
-- stolen from (http://www.gameai.com/youknow.html)