ORACLE和SQL SERVER数据交换的二进制问题

用BIZTALK交换数据也有不少时间了，主要是SQL SERVER 和ORACLE之间数据交互，同时还有 webservice，file，smtp，pop，ftp，http，excel，access等。曾经遇到的问题也一一解决，未解决的问题也正在尝试解决。以前从SQL到ORACLE遇到二进制交换，研究半天终于解决，后来遇到ORACLE到SQL的二进制交换，研究了几天，有所收获。

1 SQL SERVER

从SQL SERVER中取出数据，其中有的字段为二进制的，比如IMAGE。表结构如下：

如图1 ：

使用 SELECT * FROM RYZPK for xml auto,xmldata

得到的结果如下：

zp字段为二进制，得出的xml文档中不可能解析出二进制流，于是使用dbobject/RYZPK[@id='1']/@zp来替代，意思就是在RYZPK中主键为id='1'的记录，字段zp的值，相当于C++编程时的指针，传递的是一个内存地址，而不是实际值。

使用SELECT * FROM RYZPK for xml auto生成的架构文件如下：

 

 

可以看出image字段zp类型为xs:anyURI

如图2：

 

1.2 要想真正解析出二进制

可以将二进制转换成BASE64编码形势保存在xml中，方法如下：
 
  SELECT * FROM RYZPK for xml auto ,binary base64,xmldata
  得到的结果如下：

 

 

可以看出zp字段直接解析为文本了。

使用SELECT * FROM RYZPK for xml auto,binary base64生成的架构文件如下：

可以看出image字段zp类型为xs:base64Binary
如图3：

1.3 但是更新SQL SERVER

 没法以字符串形式更新二进制字段，生成的更新该表架构文件如下：

 

 

 如图 ：

2 ORACLE

  使用Microsoft BizTalk Adapter for Oracle(r) Database适配器，
  表结构如下

 

2.1 导出的架构文件如下：

 

 

BLOB字段PIC本身就是xsd:base64Binary类型
<xsd:element minOccurs="0" name="PIC" nillable="true" type="xsd:base64Binary" />

如图：

3 数据交换

从上面的分析可以看出

ORACLE为源时，可以以字符串形式base64编码方式读出；ORACLE为目标库时，也可以用base64编码的字符串更新目标字段，看来到现在为止，ORACLE还是很好很强大的。SQL为源，也可以以字符串形式base64编码方式读出，只有一个问题比较棘手，就是SQL SERVER为目标库时，如何更新目标二进制字段。

因为不能直接用base64编码的文本直接去更新该二进制字段，否则会报错。我曾经试着直接将该字段的架构类型改为xsd:base64Binary，结果是一样的。

主要原因是，SQL SERVER中未提供自动通过字符更新二进制字段的方法。而biztalk是基于xml文件的，里面不可能直接保存二进制字符。

解决方法

 

本人做到的是一个ORACLE到SQL SERVER的交换，有个字段为二进制，保存的是照片信息。
思路如下：
从ORACLE获得xml的消息，里面通过base64编码保存了一个二进制字段信息。
BIZTALK更新SQL SERVER 时，通过一个能接收base64编码字符串的存储过程，接收到该消息。
改存储过程将该消息中的二进制字段，即base64编码字符串解码成二进制字符，更新目标表中的字段。

值得注意的是：ORACLE出来的base64可能有点喝SQL SERVER的base64有点不同，通过以下转换（从代码看，貌似是无用功），可以成功。

 

 

 

 

 

 SQL SERVER 中的用户函数，用于通过baseb64编码字符串转换为二进制字符串

 

 

目标更新表的SP如下