基于Microsoft SQL Server的BLOB数据的存取方法研究

摘要：主要探讨几种在SQL Server数据库中存取BLOB数据的方法，并结合实例介绍不利用开发工具，在SQL Server数据库中对BLOB数据进行直接存取的方法。 　　关键词：BLOB数据；SQL Server；存取 　　中图分类号：TP392 文献标识码：A 文章编号：1009-3044(2007)06-11500-02 　　 　　1 引言 　　目前，人们对于文本、声音、图片、视频等数据的存取，需求越来越频繁。这些特殊的数据称为大数据块，又称为二进制大对象（Binary Large Object, BLOB）。在实际的系统中由于应用目标的不同，采用的存取以及处理方式也不尽相同。一般来说，BLOB数据在数据库中的存储方式有以下两种：一是在数据 库的表中只存放BLOB数据文件的存储路径，而将BLOB数据以文件的形式存放于指定的磁盘目录下。这种方式适用于对BLOB数据的安全性要求不高，而且 不用经常维护。二是将BLOB数据直接存入数据库表中的某一个字段中。这种方式适用于BLOB数据量不是非常巨大，而且对BLOB数据的安全性要求比较高 的情况。 　　在SQL Server数据库中，系统提供了ＢLOB数据类型， 该种数据类型可以存储巨大的不定长度的二进制或者字符型数据，它分为Text、Ntext和Image三种格式，Image数据类型可以最大限度地存放 ２Ｇ数据量的文件。对于文件大小小于８Ｋ文件数据类型一般使用Varchar，Varbinary数据类型即可，大于８Ｋ的文件就得使用BLOB数据类 型。 　　BLOB数据在SQL Server系统中的存储方式不同于普通的数据类型，普通类型的数据直接存储在用户定义的字段上， 而BLOB数据由于数据量太大，因此不是作为数据记录(行)的一部分被存储在数据记录页中，而是存储在它所属的一组专用页中。存储在数据记录页上的只是一 个16字节指针，该指针指向存放该条记录的BLOB数据的页面。 　　本文具体论述了不使用开发工具而在SQL Server环境中存取BLOB数据的几种方法，并给出了主要的代码。同时还简单介绍了利用开发工具在SQL Server环境中存取BLOB数据的方法。 　　 　　2 直接操作SQL Server数据库 　　当不希望涉及到开发工具而需要把BLOB数据存入到SQL表中的相应列，或者是把SQL表中的BLOB数据导出到目的地时，就可以利用以下几种方式来实现。 　　2.1 用SQL Server提供的函数 　　由于与普通类型的数据的存储方式的不同，所以在SQL Server中还专门给出了一组函数：READTEXT、WRITETEXT、UPDATETEXT和TEXTPTR。要利用这组函数存取图片、文本或是 声音等数据，需要先进行类型转换，通过C、Java等专门的工具先将图片或声音等数据转换成十六进制数据后进行存取。 　　下面就用一个实例来说明如何向数据库中插入一个图片。首先在数据库中建立一个名为tb_picture的表，有两个字段picid和picture，分别存储图片编号和图片。其代码如下： 　　CREATE TABLE tb_picture 　　(picid int NOT NULL, picture image NULL , PRIMARY KEY(picid)) 　　其次往tb_picture表中插入一条记录，并初始化picture字段。其代码如下： 　　INSERT tb_picture(picid,picture) values(1,0xffff) 　　最后往tb_picture表中写入图片信息。其代码如下： 　　DECLARE @@ptrval varbinary(16) 　　SELECT @@ptrval=TEXTPTR(picture) FROM tb_picture 　　WHERE picid=1 　　WRITETEXT tb_picture.picture 　　@@ptrval 0x424D66000… 　　其中0x424D66000…为插入图片的十六进制数据。注意在写入图形信息前先将此数据库的select into/bulkcopy属性设置为TRUE，作用是允许使用 SELECT INTO 语句和快速大容量复制。其代码如下： 　　sp_dboption 'exam', 'select into/bulkcopy','True' 　　2.2 运行textcopy命令 　　在SQL Server安装目录下有一个MS-DOS模式下的带有多个参数的可执行文件textcopy.exe，可实现将文本或图像存储在SQL Server数据表中。该命令在C:/Program Files/Microsoft SQL Server/MSSQL/BINN>提示符下。 　　Textcopy.exe 的命令格式为：textcopy [/S sqlservername] [/U login] [/P password] [/D dbname] [/T tablename] [/C colunmname] [/W whereclause] [/F filename] [/ direction]，该命令需说明服务器名称、登陆用户、密码、数据库名称、数据表名称、列名、条件、导入或导出的文件名以及导入或导出方向标识。 　　要注意的是使用textcopy命令将外部文件的内容导入SQL表中时所做的是一种覆盖，也就是说此时SQL表中的相应列的内容不能为空。 此方法也在前面的例子的基础上运行成功。相对于下面要介绍的方法，运行textcopy命令的方法效率要低一些，不适宜操作大批量数据。 　　2.3 调用存储过程 　　所谓存储过程是经编译和优化的一组Transact-SQL语句，它存储在数据库服务器中，可被其他程序调用执行[1]。下面就介绍一下在 SQL Server环境中调用前面介绍的textcopy。调用textcopy时必须借助系统扩展存储过程master下的xp_cmdshell。 　　下面仍然用前面使用的名为tb_picture的表。先建立一个名为sp_textcopy的存储过程，然后调用执行。关键代码如下： 　　2.3.1 建表和初始化数据 　　create table tb_picture(picid int,picture image) 　　insert tb_picture values(1,0x)-- 此步骤是必须的，且image项不是null Go 　　2.3.2 定义存储过程 　　CREATE PROCEDURE sp_textcopy ( 　　@srvnamevarchar (30), 　　@loginvarchar (30), 　　@passwordvarchar (30), 　　@dbnamevarchar (30), 　　@tbnamevarchar (30), 　　@colnamevarchar (30), 　　@filenamevarchar (30), 　　@whereclause varchar (40), 　　@directionchar(1)) 　　AS 　　DECLARE @exec_str varchar (255) 　　 本文原文 　　SELECT @exec_str = 　　 'textcopy /S ' + @srvname + 　　 ' /U ' + @login + 　　 ' /P ' + @password + 　　 ' /D ' + @dbname + 　　 ' /T ' + @tbname + 　　 ' /C ' + @colname + 　　 ' /W "' + @whereclause + 　　 '" /F ' + @filename + 　　 ' /' + @direction 　　EXEC master..xp_cmdshell @exec_str 　　Go 　　2.3.3读入 　　sp_textcopy @srvname = 'abc', --abc为服务器的名称 　　 @login = 'sa', 　　 @password = '***', 　　 @dbname = 'exam', --exam为数据库的名称 　　 @tbname = 'tb_picture', 　　 @colname = 'picture', 　　 @filename = 'c:/cript/1.bmp',--此处插入的是一个图片 　　 @whereclause = " WHERE picid=1", 　　 @direction = 'i'--读入 　　Go 　　2.3.4 读出 　　sp_textcopy @srvname = 'abc', 　　 @login = 'sa', 　　 @password = '***', 　　 @dbname = 'exam', 　　 @tbname = 'tb_picture', 　　 @colname = 'picture', 　　 @filename = 'c:/1.bmp', 　　 @whereclause = " WHERE picid=1", 　　@direction = 'o' --读出 　　上述代码已在查询分析器中成功执行。需要注意的是，如果报textcopy不是可执行文件的话，就到C:/Program Files/Microsoft SQL Server/MSSQL/Binn目录下拷贝textcopy.exe到C:/Program Files/Microsoft SQL Server/80/Tools/Binn下。 　　 　　3 利用应用系统开发工具实现BLOB数据的存取 　　结合各种开发工具的不同特点和应用的具体需求，可采用下述几种不同的方法。 　　ADO(ActiveX Data Objects)是一个数据存取接口，提供了编程语言可利用的对象。ADO应用程序可用VC++、VB、VBScripts、JAVA等语言实现，ADO Field 对象提供了AppendChunk和GetChunk方法来存取BLOB数据。AppendChunk方法可将二进制或字符数据填写到域对象中； GetChunk方法可检索域对象部分或全部二进制或字符数据。在文献[2]中使用这种方式成功实现了对数据库图像数据的存取。也可以使用ADODB Stream对象，利用临时文件来实现二进制数据的存取。前提是要安装Microsoft ActiveX Data Objects 2.6 Library或高于2.6的版本。在文献[3]中作者用Stream方法以图片、WORD文档、AVI影像文件进行实践均取得了成功。在文献[4]中作 者比较了图像字段的Stream和Chunk存取方法。 　　VC++的MFC提供的ClongBinary类可以方便地实现存取BLOB字段。在文献[5]中详细论述了这种方法的具体实现。 　　 　　4 结束语 　　本文主要是结合一个实例介绍了不利用开发工具，在SQL Server数据库中对BLOB数据进行直接存取的几种方法，同时也简单介绍了几种利用开发工具直接存取BLOB数据的方法。随着人们对于图像数据、大文 本数据、音视频数据存取的需求越来越大。对BLOB数据类型的存取方法也日渐增多，它的应用前景将是非常广阔的。 　　参考文献： 　　[1]刘独玉,罗彬.基于MS SQL Server的存储过程的研究与应用[J].四川轻化工学院学报, 2001,14(2):25-32. 　　[2]张满意.利用VB处理SQL Server 2000中的图像数据[J].电脑知识与技术,2004(23):17-19. 　　[3]魏文胜.在VB中存取SQL SERVER 2000二进制数据的方法[J].电脑知识与技术, 2006(20)34-35. 　　[4]陈天星,梁萍,冯芳.两种数据库图像存取方法的透析[J].计算机应用,2003,23:204-206. 　　[5]朱春花,张子彪,路新春. BLOB数据类型存取方法的研究及应用[J].计算机应用与软件，2002(10):52-54. 　　本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。