ODBC连接大对象
通过ODBC驱动访问大对象¶
Seabox数据库支持对大对象的存储，本章通过两个示例介绍了如何通过ODBC驱动来访问数据库中的大对象。
在Access中的OLE对象¶
大对象被映射到驱动程序中的LONGVARBINARY类型，以允许在Microsoft Access中存储进行存储，例如OLE对象。通常使用SQLPutData和SQLGetData来发送和接收这些对象。驱动程序创建一个新的大对象，并简单的将其“标识符”插入相应的表中。但是，SeaboxSQL内部创建了新的Oid来标识大对象，以区别于原有的Oid。在 驱动程序中使用的类型简称为“ lo”，这是用于创建它的命令：
create type lo (
internallength=4,
externallength=10,
input=int4in,
output=int4out,
default='',
passedbyvalue
);
完成此操作后，只需使用新的“ lo”类型在该数据库中定义列。 请注意，必须对要在驱动程序中使用大对象的每个数据库执行此操作。 当驱动程序看到“ lo”类型时，它将作为LONGVARBINARY处理。 另一个重要的注意事项是，这种新类型相关功能仍在完善中。它本身不会在更新和删除后自动进行清理，因此会造成磁盘空间的浪费。请查阅相关手册以确定这部分功能的完善程度。
在Visual Basic中访问大对象¶
需要的环境准备
-
Visual Basic中DAO、ADO和RDO的接口可用
-
使用Seabox源码contrib/lo中的对应函数创建了lo类型
-
安装并配置好了新版的SeaboxSQL ODBC驱动
本示例中使用的表包含两个字段，建表SQL如下：
CREATE TABLE MYTABLE(
MAIN INTEGER,
OBJECT LO
);
示例中使用的ODBC DSN名为sdsql_test_blob。 若要插入一条记录，建议您使用INSERT sql语句而不是AddNew方法（在DAO，RDO和ADO中可用）。AddNew方法强制声明一个Recordset，这很不好，因为在打开它时，VB创建了一个游标，并且必须传递表中的所有记录，从而大大降低了应用程序的速度。
这些示例非常简单，根据注释和命名很容易理解。 有关使用哪个接口的一些提示：
-
ADO和RDO是连接到SeaboxSQL的最佳接口。 一般认为ADO更好是因为它是新的并且受到Microsoft的积极支持。 RDO是旧的接口，并且还不完善。
-
DAO非常非常重，建议您不要使用它，除非特殊原因不得已。
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=sdsql_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=sdsql_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()
``` vb
'
' 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=sdsql_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=sdsql_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=sdsql_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