vba 插入oracle数据,用vba插入字符串到Oracle的BLOB字段

我们通过例子来介绍VBA对此类型数据的操作。在例子中，我尽量对重要的地方做说明。

1)数据表原型

-- 创建表, 需要注意的就是TAB_BLOB字段允许为空

create table TAB_BLOB_TEST (

TAB_ID   VARCHAR2(30) not null,

TAB_LANG CHAR(5) default 'zh_CN' not null,

TAB_BLOB BLOB

)

--在列上加注释

comment on column TAB_BLOB_TEST.TAB_ID   is '字段编码，主键';

comment on column TAB_BLOB_TEST.TAB_LANG   is '语言版本';

comment on column TAB_BLOB_TEST.TAB_BLOB   is '测试长字段';

--  create sequence SEQTAB_BLOB_TEST_ID

minvalue 1

maxvalue 999999999999999999999999999

start with 1 increment by 1

cache 20;

2)VBA相关函数

'函数名称ConverStringBoBin

'函数作用把字符串转换成对应的二进制表示形式,如果是图片或其它文件,可参考其它转换函数

'因为在VB中使用的字符集是Unicode类型,所以就这样转换了.实际上只是字符串的二进制表示方式

Function ConverStringBoBin(strData As String) As Byte()

Dim byt()     As Byte

byt = strData

ConverStringBoBin = byt

End Function

'===========================================

'==                                       ==

'==    函数 GetConnString                     ==

'==    功能 获取连接字符串                ==

'==    参数                               ==

'==    Author Alpzhizhi                   ==

'==                                       ==

'===========================================

Function GetConnString()

GetConnString = "Provider=OraOLEDB.Oracle.1;User ID=YourDataBaseUserName;Password=YourDataBasePassword;Data Source=YourDataBaseName;"

End Function

3)、VBA对数据库操作

'===========================================

'==                                       ==

'==    函数 SaveToDateBaseOfString                     ==

'==    功能 保存字符串到数据库                ==

'==    参数                               ==

'==    Author Alpzhizhi                   ==

'==                                       ==

'===========================================

Sub SaveToDateBaseOfString(ByVal strData As String)

On Error Resume Next

Dim Sql As String

Dim KeyValue As String

Dim Cn As Connection

Dim Rs As Recordset

Dim Byts() As Byte

'创建数据库连接对象

Set Cn = New Connection

Cn.Open GetConnString

''获取下一个关键ID

Sql = "SELECT SEQTAB_BLOB_TEST_ID.NEXTVAL VAL FROM DUAL"

Set Rs = Cn.Execute(Sql)

KeyValue = Rs("VAL")

'向数据库中插入一条数据,

Sql = "INSERT INTO TAB_BLOB_TEST(TAB_ID) VALUES('" & KeyValue & "')"

Cn.Execute Sql

'获取刚刚插入的记录的BLOB字段

Sql = "SELECT TAB_BLOB FROM TAB_BLOB_TEST WHERE TAB_ID = '" & KeyValue & "'"

'把要插入的字符串改转换为二进制类型(字节型)

Byts = ConverStringBoBin(strData)

'创建并打开数据记录集对象

Set Rs = New Recordset

Rs.Open Sql, Cn, 3, 3 '注意这里的打开模式

Rs("BBZD_GS") = Byts  '把二进制数据写入到 Recordset 对象

Rs.Update             '更新数据库

Set Rs = Nothing

Set Cn = Nothing

End Sub