psqlODBC HOWTO - Visual Basic Large Objects

Author: Denis Gasparin (denis@edistar.com)
Release Date: 29 October 2001
Description: Example based Mini-Howto on Pgsql Large Objects Interface and Visual Basic

This document tells about using Large Objects and Visual Basic. All the main connection interfaces available in VB are discussed: DAO, ADO and RDO.

Requirements to get the subroutines to work:

DAO, ADO and RDO interfaces available in VB
Created the lo type using the appropriate functions available in contrib/lo in the PostgreSQL source tree
Installed and properly configured the latest version of the PostgreSQL ODBC driver.

In the example the database used has one table with only two fields. Here is the SQL definition:

CREATE TABLE MYTABLE( MAIN INTEGER, OBJECT LO );

The ODBC DSN I used in the example is named pgsql_test_blob. To insert a record, I suggest you to use the INSERT sql statement instead of the AddNew method (available in DAO, RDO and ADO). The AddNew method force you to declare a Recordset and this is bad because when you open it, VB creates a cursor and has to pass all the records in the table, slowing your application significantly.

I think the examples are very simple and self explanatory. Some tips about which interface to use:

ADO and RDO are the best interfaces to connect to PostgreSQL. Personally, I think ADO is better only because it is new and actively supported by Microsoft. RDO is the old interface and it is not more developed.
DAO is very, very heavy and I suggest you not use it unless you are forced to do so.

DAO (Data Access Objects)

Private Sub DAO_Connect()
	Dim chunk() As Byte
	Dim fd As Integer
	Dim flen As Long
	Dim ws As Workspace
	Dim cn As Database
	Dim rs As DAO.Recordset
	Dim strConnection As String

	' Initialize the DB Engine
	Set ws = DBEngine.Workspaces(0)
	Let strConnection = "ODBC;DSN=pgsql_test_blob;"
	Set cn = ws.OpenDatabase("", False, False, strConnection)

	' Open the table MYTABLE
	Set rs = cn.OpenRecordset("MYTABLE")

	'
	' Add a new record to the table
	'
    rs.AddNew


    rs!main = 100 '' a random integer value ''

    fd = FreeFile
    Open "mydocument" For Binary Access Read As fd
    flen = LOF(fd)
    If flen = 0 Then
        Close
        MsgBox "Error while opening the file"
        End
    End If

    ' Get the blob object into the chunk variable
    ReDim chunk(1 to flen)
    Get fd, , chunk()

    ' Store it in the database
	rs!object.AppendChunk chunk()

	' Update changes
    rs.Update

    ' Close the file
    Close fd

    ' Close the record set
	rs.Close

	'
	' Read the blob object from the first record of MYTABLE
	'
	Set rs = Nothing

	' Open the table
	Set rs = cn.OpenRecordset("MYTABLE")

	' Open a file for writing
	fd = FreeFile
	Open "mydocument" For Binary Access Write As fd
	flen = rs!object.FieldSize
	ReDim chunk(1 to flen)

	' Get it from the database
	chunk() = rs!object.GetChunk(0, flen)
	' ...and put it into the file
	Put fd, , chunk()

	' Close all...
	rs.Close
	Close fd
	cn.Close
	Close
End Sub

ADO (ActiveX Data Objects)

Private Sub ADO_Store()
	Dim cn As New ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim cmd As ADODB.Command
	Dim chunk() As Byte
	Dim fd As Integer
	Dim flen As Long
	Dim main As ADODB.Parameter
	Dim object As ADODB.Parameter

	' Connect to the database using ODBC
	With cn
	    .ConnectionString = "dsn=pgsql_test_blob;"
	    .Open
	    .CursorLocation = adUseClient
	End With

	' Here is an example if you want to issue a direct command to the database
	'
	'Set cmd = New ADODB.Command
	'With cmd
	'    .CommandText = "delete from MYTABLE"
	'    .ActiveConnection = cn
	'    .Execute
	'End With
	'Set cmd = Nothing

	'
	' Here is an example of how insert directly into the database without using
	' a recordset and the AddNew method
	'
	Set cmd = New ADODB.Command
	cmd.ActiveConnection = cn
	cmd.CommandText = "insert into MYTABLE(main,object) values(?,?)"
	cmd.CommandType = adCmdText

	' The main parameter
	Set main = cmd.CreateParameter("main", adInteger, adParamInput)
	main.Value = 100 '' a random integer value ''
	cmd.Parameters.Append main

	' Open the file for reading
	fd = FreeFile
	Open "mydocument" For Binary Access Read As fd
	flen = LOF(fd)
	If flen = 0 Then
	    Close
	    MsgBox "Error while opening the file"
	    End
	End If

	' The object parameter
	'
	' The fourth parameter indicates the memory to allocate to store the object
	Set object = cmd.CreateParameter("object", _
                                         adLongVarBinary, _
                                         adParamInput, _
                                         flen + 100)
	ReDim chunk(1 to flen)
	Get fd, , chunk()

	' Insert the object into the parameter object
	object.AppendChunk chunk()
	cmd.Parameters.Append object

	' Now execute the command
	Set rs = cmd.Execute

	' ... and close all
	cn.Close
	Close
End Sub

Private Sub ADO_Fetch()
	'
	' Fetch the first record present in MYTABLE with a lo object stored

	Dim cn As New ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim fd As Integer
	Dim flen As Long
	Dim chunk() As Byte

	' Connect to the database using ODBC
	With cn
	    .ConnectionString = "dsn=pgsql_test_blob;"
	    .Open
	    .CursorLocation = adUseClient
	End With

	' Open a recordset of the table
	Set rs = New ADODB.Recordset
	rs.Open "MYTABLE", cn, adOpenKeyset, adLockOptimistic, adCmdTable

	' Get the len of the stored object
	flen = rs!object.ActualSize

	' Initialize the file where to store the blob
	fd = FreeFile
	Open "mydocument" For Binary Access Write As fd

	ReDim chunk(1 to flen)

	' Get it from the database
	chunk() = rs!object.GetChunk(flen)
	' ... and store in the file
	Put fd, , chunk()


	Close
End Sub

RDO (Remote Data Objects)

Private Sub RDO_Store()
	Dim cn As New RDO.rdoConnection
	Dim rs As RDO.rdoResultset
	Dim cmd As RDO.rdoQuery
	Dim fd As Integer
	Dim flen As Long
	Dim chunk() As Byte

	' Connect to the database using ODBC
	With cn
	    .Connect = "dsn=pgsql_test_blob;"
	    .LoginTimeout = 3
	    .CursorDriver = rdUseOdbc
	    .EstablishConnection rdDriverNoPrompt, True
	End With

	' Create the INSERT statement to store the record in the database
	Set cmd = cn.CreateQuery("insert", _
                                 "insert into MYTABLE (main,object) values(?,?)")

	' Insert the first parameter
	cmd.rdoParameters(0).Value = 100 '' a random integer value ''

	' Open the file for reading
	fd = FreeFile
	Open "mydocument" For Binary Access Read As fd
	flen = LOF(fd)
	If flen = 0 Then
	    Close
	    MsgBox "errore in apertura file"
	    End
	End If

	ReDim chunk(1 To flen)
	' Get it ...
	Get fd, , chunk()
	' and store into the parameter object
	cmd.rdoParameters(1).Type = rdTypeLONGVARBINARY
	cmd.rdoParameters(1).AppendChunk chunk()

	' Finally execute the INSERT statement
	cmd.Execute

	' Close all
	Close
End Sub

Private Sub RDO_Fetch()
	'
	' Fetch the first record present in MYTABLE with a lo object stored

	Dim cn As New RDO.rdoConnection
	Dim rs As RDO.rdoResultset
	Dim fd As Integer
	Dim flen As Long
	Dim chunk() As Byte

	' Connect to the database using ODBC
	With cn
	    .Connect = "dsn=pgsql_test_blob;"
	    .LoginTimeout = 3
	    .CursorDriver = rdUseOdbc
	    .EstablishConnection rdDriverNoPrompt, True
	End With

	' Open the table
	Set rs = cn.OpenResultset("select * from MYTABLE", rdOpenKeyset)

	' Get the length of the file
	flen = rs!object.ColumnSize

	' Initialize the file where to store the object
	fd = FreeFile
	Open "mydocument" For Binary Access Write As fd

	ReDim chunk(1 To flen)

	' Get it from the database
	chunk() = rs!object.GetChunk(flen)
	Put fd, , chunk()
	Close
End Sub