# 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