psqlODBC HOWTO - Visual Basic 大对象

作者：Denis Gasparin (denis@edistar.com)
发布日期：2001 年 10 月 29 日
描述：基于示例的 Pgsql 大对象接口和 Visual Basic 的迷你指南

本文档介绍了如何使用大对象和 Visual Basic。讨论了 VB 中可用的所有主要连接接口：DAO、ADO 和 RDO。

使子例程正常工作的要求

VB 中可用的 DAO、ADO 和 RDO 接口
使用 PostgreSQL 源代码树中 contrib/lo 中提供的适当函数创建 lo 类型
安装并正确配置最新版本的 PostgreSQL ODBC 驱动程序。

在示例中，使用的数据库只有一个表，只有两个字段。以下是 SQL 定义

CREATE TABLE MYTABLE( MAIN INTEGER, OBJECT LO );

我在示例中使用的 ODBC DSN 名称为 pgsql_test_blob。要插入记录，我建议您使用 INSERT sql 语句，而不是 AddNew 方法（在 DAO、RDO 和 ADO 中可用）。AddNew 方法强制您声明一个 Recordset，这很糟糕，因为当您打开它时，VB 会创建一个游标，并且必须传递表中的所有记录，从而显着降低应用程序的速度。

我认为这些示例非常简单且不言自明。关于使用哪个接口的一些提示

ADO 和 RDO 是连接到 PostgreSQL 的最佳接口。就我个人而言，我认为 ADO 更好，仅仅因为它更新且受到 Microsoft 的积极支持。RDO 是旧的接口，不再开发。
DAO 非常非常重，我建议您不要使用它，除非您被迫这样做。

DAO（数据访问对象）

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 数据对象）

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（远程数据对象）

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