作者：Denis Gasparin ([email protected])
发布日期：2001 年 10 月 29 日
描述：基于示例的 Pgsql 大对象接口和 Visual Basic 的迷你指南
本文档介绍了如何使用大对象和 Visual Basic。讨论了 VB 中可用的所有主要连接接口：DAO、ADO 和 RDO。
使子例程正常工作的要求
CREATE TABLE MYTABLE(
MAIN INTEGER,
OBJECT LO
);
我在示例中使用的 ODBC DSN 名称为 pgsql_test_blob。要插入记录，我建议您使用 INSERT sql 语句，而不是 AddNew 方法（在 DAO、RDO 和 ADO 中可用）。AddNew 方法强制您声明一个 Recordset，这很糟糕，因为当您打开它时，VB 会创建一个游标，并且必须传递表中的所有记录，从而显着降低应用程序的速度。
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
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
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