http://odbc.postgresql.org/psqlodbc.php?DocID=howto-vblo
Author: Denis Gasparin ([email protected])
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 postgres. 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 SubADO (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 SubPrivate 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 SubRDO (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 SubPrivate 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
Author: Denis Gasparin ([email protected])
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 postgres. 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 SubADO (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 SubPrivate 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 SubRDO (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 SubPrivate 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
请耐心看完下文，尤其对asp非组件文件上传提供了非.net的好方法
在 ActiveX Data Objects (ADO) 2.5 中引入的 Stream 对象可以用来大大简化编写访问和修改 SQL Server 中的 BLOB 数据的代码。 以前版本的 ADO [ 2.0、2.1 和 2.1 SP2 ] 则需要小心使用 Field 对象的 GetChunk 和 AppendChunk 方法，才能将固定大小的大型数据块中的 BLOB 数据写入 BLOB 列（或从中读取）。 由于 ADO 2.5 的出现，该方法有了可以替代的方案。 本文包含相应的代码示例，这些示例演示了如何使用 Stream 对象来为如下常见任务进行编程： 将存储在 SQL Server Image 列的数据保存到硬盘的文件中。
将 .gif 文件的内容移到 SQL Server 表的 Image 列。 更多信息
下面的代码示例所针对的数据存储在 SQL Server 7.0 pubs 示例数据库的 pub_info 表中。 您需要修改 ADO 连接字符串，使之指向您安装的 SQL Server。 示例 1： 将 SQL Server Image 列中的数据保存到硬盘文件中
该示例代码将打开在 pubs 数据库的 pub_info 表中的记录集，并将存储在第一条记录 logo 列中的二进制图像数据保存到硬盘文件中，如下所示：
新建一个标准 EXE Visual Basic 工程。
在工程菜单上，单击选择引用，然后设置一个到 Microsoft ActiveX Data Objects 2.5 Object Library 的引用。
将 CommandButton 控件放在 Form1 上。
在该窗体的常规声明段中作如下声明：
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
将下面的代码剪切并粘贴到添加到该窗体的 CommandButton 所对应的 Click 事件中：
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("logo").Value
mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWriters.Close
cn.Close
保存并运行该 Visual Basic 工程。
单击 CommandButton，即可将第一条记录的 logo 列所包含的二进制数据保存到文件 c:\publogo.gid 中。请用资源管理器找到并打开该文件，以查看所保存的图像。 该示例中的代码声明了一个 ADODB Stream 对象，并将它的 Type 属性设置为 adTypeBinary，以表示将使用该对象处理二进制数据。 然后，通过调用 Stream 对象的 Write 方法，存储在 pub_info 表第一条记录的 logo 列中的数据被写出到 Stream 对象中。 现在，Stream 对象包含了二进制数据，该二进制数据是通过调用该对象的 SaveToFile 方法并传入文件的路径保存到文件中的。 作为第二个参数被传入的常量 adSaveCreateOverWrite 则导致 SaveToFile 方法覆盖指定的文件（如果它已经存在）。
示例 2： 将以 .gif 文件格式存储的图像传输到 SQL Server 表的 Image 列
该示例代码将通过覆盖 pub_info 表的当前内容，将一个以 .gif 文件格式存储的图像保存到该表的第一条记录中，如下所示：
新建一个标准 EXE Visual Basic 工程。
在工程菜单上，单击选择引用，然后设置一个到 Microsoft ActiveX Data Objects 2.5 Object Library 的引用。
将 CommandButton 按钮放在 Form1 上。
在该窗体的常规声明段中作如下声明：
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
将下面的代码剪切并粘贴到添加到该窗体的 CommandButton 所对应的 Click 事件中：
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimisticSet mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "<path to .gif file>"
rs.Fields("logo").Value = mstream.Read
rs.Updaters.Close
cn.Close
保存并运行该 Visual Basic 工程。
单击 CommandButton 运行该代码，即可将 .gif 文件的内容以数据流的方式传输到 ADO Stream 对象中，并将 Stream 中的数据保存到记录集内第一条记录的 logo 列。
请使用示例 1 中的代码，确认 logo 列中的图像已被修改。
是这样写的，"insert pub_info (pu_id,logo)values('0002','"& mstream.Read &"')",你能告诉我为应怎么写吗？谢谢
Dim ste AS New ADO.Stream'假设表名tblImage,columns: ID(AutoNumber),FileName(String), MyImage(Image)rs.Open "Select * From tblImage Where ID=0", MyConnection,adOpenKeySet, adLockOptimisticstm.Type=adTypeBinary
stm.Open
stm.LoadFromFile "c:\XXX.jpg"rs.AddNew
rs!FileName="XXX.jpg"
rs!MyImage=stm.Readrs.Updaters.Close
stm.CloseSet rs=Nothing
Set stm=Nothing
Open strReportFileName For Binary As #1
Get #1, 1, bRecord
Close #1
rsReport.Update "iReport", bRecordstrReportFileName是要写入的文件名，其他就不用讲了吧，我就是这样把整个EXCEL写到SQL里的
这种方法实在是巧妙，佩服！