程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> MS SQL SERVER 圖像或大文本的輸入輸出

MS SQL SERVER 圖像或大文本的輸入輸出

編輯:關於SqlServer

在MS SQL Server 安裝目錄下有個可執行文件叫 TEXTCOPY.EXE
可對 MS SQL Server 中的文本或圖像數據進行輸入輸出.
不過你可以在MS-DOS方式下執行textcopy /? 得到它的描述。
下面是這個工具的描述:
CopIEs a single text or image value into or out of SQL Server. The val
ue
is a specified text or image 'column' of a single row (specifIEd by th
e
"where clause") of the specifIEd 'table'.

If the direction is IN (/I) then the data from the specifIEd 'file' is

copIEd into SQL Server, replacing the existing text or image value. If
the
direction is OUT (/O) then the text or image value is copIEd from
SQL Server into the specifIEd 'file', replacing any existing file.

TEXTCOPY [/S ][sqlserver]] [/U [login]] [/P ][passWord]]
[/D ][database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is n
ot
specifIEd, the local SQL Server is used.
/U login The login to connect with. If 'login' is not spec
ifIEd,
a trusted connection will be used.
/P password The password for 'login'. If 'passWord' is not
specifIEd, a NULL passWord will be used.
/D database The database that contains the table with the tex
t or
image data. If 'database' is not specifIEd, the d
efault
database of 'login' is used.
/T table The table that contains the text or image value.

/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE keyw
ord)
that specifIEs a single row of 'table'.
/F fi

le The file name.
/I Copy text or image value into SQL Server from 'fi
le'.
/O Copy text or image value out of SQL Server into '
file'.
/K chunksize Size of the data transfer buffer in bytes. Minimu
m
value is 1024 bytes, default value is 4096 bytes.

/Z Display debug information while running.
/? Display this usage information and exit.

You will be prompted for any required options you did not specify.

為此, 可寫一個存儲過程,調用這個命令
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

下面是一個拷貝圖像到SQL Server的pubs數據庫的例子, 表名pub_info, 字段名
logo,圖像文件名picture.bmp,保存到pub_id='0736'記錄 sp_textcopy @srvn
ame = 'ServerName',
@login = 'Login',
@password = 'PassWord',
@dbname = 'pubs',
@tbname = 'pub_info',
@colname = 'logo',
@filename = 'c:\picture.bmp',
@whereclause = " WHERE pub_id='0736' ",
@direction = 'I'

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved