原帖地址：http://bbs.51cto.com/thread-1130840-1.html
问题描述：
我准备在MS-SQL服务器端建立一个存储过程，需要用纯SQL语句存取文件。
求老师指导一下!!
解决方案：
有些方法用于导入和导出图片、照片、视频、文档等BLOB类型数据。
SQL Server 2000：
在binn目录下的未公开工具可用，提示你提供相应的参数：
textcopy
使用textcopy对sqlserver的Image字段进行读取和写入
用存储过程方法：
1、建立过程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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、建表和初始化数据
|
1
2
3
4
5
|
create
table
表名 (编号
int
,image列名 image)
go
insert
表名
values
(1,0x)
-- 必须的，且不是null
insert
表名
values
(2,0x)
-- 必须的，且不是null
go
|
3、读入
|
1
2
3
|
sp_textcopy
'你的服务器名'
,
'sa'
,
'你的密码'
,
'库名'
,
'表名'
,
'image列名'
,
'c:\图片.bmp'
,
'where 编号=1'
,
'I'
--注意条件是 编号=1
sp_textcopy
'你的服务器名'
,
'sa'
,
'你的密码'
,
'库名'
,
'表名'
,
'image列名'
,
'c:\bb.doc'
,
'where 编号=2'
,
'I'
--注意条件是 编号=2
go
|
4、读出成文件
|
1
2
3
|
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方法：
|
1
2
3
4
5
|
--启用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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
-- 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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
------------
-- 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
，如需转载请自行联系原作者
