【51CTO/BBS】如何完全用SQL语句，将文件存(取)到数据库？-阿里云开发者社区

【51CTO/BBS】如何完全用SQL语句，将文件存(取)到数据库？

2017-11-09 1212

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，独享型 2核4GB

简介：

+关注继续查看

原帖地址：http://bbs.51cto.com/thread-1130840-1.html

问题描述：

我准备在MS-SQL服务器端建立一个存储过程，需要用纯SQL语句存取文件。
求老师指导一下!!

解决方案：

有些方法用于导入和导出图片、照片、视频、文档等BLOB类型数据。

SQL Server 2000：

在binn目录下的未公开工具可用，提示你提供相应的参数：

textcopy

使用textcopy对sqlserver的Image字段进行读取和写入

用存储过程方法：

1、建立过程

CREATE PROCEDURE sp_textcopy (     

  @srvname    varchar (30),     

  @login      varchar (30),     

  @password    varchar (30),     

  @dbname      varchar (30),     

  @tbname      varchar (30),     

  @colname    varchar (30),     

  @filename    varchar (30),     

  @whereclause varchar (40),     

  @direction  char(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、建表和初始化数据

create table 表名 (编号 int,image列名 image)    
go    

insert 表名 values(1,0x)    -- 必须的，且不是null    

insert 表名 values(2,0x)    -- 必须的，且不是null    
go

3、读入

sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\图片.bmp','where 编号=1','I' --注意条件是 编号=1    

sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\bb.doc','where 编号=2','I' --注意条件是 编号=2    
go

4、读出成文件

sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\图片.bmp','where 编号=1','O' --注意条件是 编号=1    

sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\bb.doc','where 编号=2','O' --注意条件是 编号=2    
go

注意：

如果报textcopy不是可执行文件的话，你就到
C:\Program Files\Microsoft SQL Server\MSSQL\Binn
目录下拷备 textcopy.exe到：
C:\Program Files\Microsoft SQL Server\80\Tools\Binn

开启xp_cmdshell方法：

--启用xp_cmdshell   

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;    
--关闭xp_cmdshell    

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;    
--如果提示拒绝访问，一般是目录权限的问题，可新建一个目录，加入Everyone完全控制权限即可。

SQL Server 2005：

通过OPENROWSET和bcp:

示例1：

-- SQL Server import image - SQL Server export image
-- Extract image SQL Server - SQL Server export binary data
USE AdventureWorks;
GO

-- Create image warehouse for importing image into sql database

CREATE TABLE dbo.PhotoLibrary (

  PhotoLibraryID INT IDENTITY ( 1 , 1 ) PRIMARY KEY,

  ImageName        VARCHAR(100),

  Photo            VARBINARY(MAX))
GO

-- SQL Server import image - sql storing images database sql server

INSERT INTO dbo.PhotoLibrary
([ImageName])

VALUES     ('MadisonAVE.JPG')

UPDATE dbo.PhotoLibrary

SET    Photo = (SELECT *

FROM OPENROWSET(BULK 'e:\image\photo\MadisonAVE.JPG',

SINGLE_BLOB) AS x)

WHERE  [ImageName] = 'MadisonAVE.JPG'
GO

-- Check table population

SELECT *

FROM   dbo.PhotoLibrary
GO

-- SQL Server export image

DECLARE  @Command NVARCHAR(4000)

-- Keep the command on ONE LINE - SINGLE LINE!!! - broken here for presentation

SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary"

                   queryout "e:\image\photo\expMadisonAVE.jpg" -T -n -SPROD\SQL2005'

PRINT @Command -- debugging

EXEC xp_cmdshell @Command
GO

示例2：

------------
-- T-SQL Export all images in table to file system folder
-- Source table: Production.ProductPhoto  - Destination: K:\data\images\productphoto\
------------

USE AdventureWorks2008;
GO

DECLARE  @Command       VARCHAR(4000),

         @PhotoID       INT,

         @ImageFileName VARCHAR(128)

DECLARE curPhotoImage CURSOR FOR -- Cursor for each image in table

SELECT ProductPhotoID,

       LargePhotoFileName

FROM   Production.ProductPhoto

WHERE  LargePhotoFileName != 'no_image_available_large.gif'

OPEN curPhotoImage

FETCH NEXT FROM curPhotoImage

INTO @PhotoID,

     @ImageFileName

WHILE (@@FETCH_STATUS = 0) -- Cursor loop 
BEGIN

-- Keep the bcp command on ONE LINE - SINGLE LINE!!! - broken up for presentation

SET @Command = 'bcp "SELECT LargePhoto FROM

    AdventureWorks2008.Production.ProductPhoto WHERE ProductPhotoID = ' +

convert(VARCHAR,@PhotoID) + '" queryout "K:\data\images\productphoto\' +

    @ImageFileName + '" -T -n -SHPESTAR'
PRINT @Command – debugging 

/* bcp "SELECT LargePhoto FROM AdventureWorks2008.Production.ProductPhoto

WHERE ProductPhotoID = 69" queryout

"K:\data\images\productphoto\racer02_black_f_large.gif" -T -n -SHPESTAR
*/

EXEC xp_cmdshell @Command     -- Carry out image export to file from db table

FETCH NEXT FROM curPhotoImage

INTO @PhotoID,

         @ImageFileName

END -- cursor loop

CLOSE curPhotoImage

DEALLOCATE curPhotoImage

/*output
NULL
Starting copy...
NULL

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 16     Average : (62.50 rows per sec.)
NULL
.....
*/

------------

本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1585003 ，如需转载请自行联系原作者