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 @srvname = 'ServerName',
@login = 'Login',
@password = 'Password',
@dbname = 'pubs',
@tbname = 'pub_info',
@colname = 'logo',
@filename = 'c:\picture.bmp',
@whereclause = " WHERE pub_id='0736' ",
@direction = 'I'
@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 @srvname = 'ServerName',
@login = 'Login',
@password = 'Password',
@dbname = 'pubs',
@tbname = 'pub_info',
@colname = 'logo',
@filename = 'c:\picture.bmp',
@whereclause = " WHERE pub_id='0736' ",
@direction = 'I'
sp_textcopy @srvname = 'ServerName',
@login = 'Login',
@password = 'Password',
@dbname = 'pubs',
@tbname = 'pub_info',
@colname = 'logo',
@filename = 'c:\picture.bmp',
@whereclause = " WHERE pub_id='0736' ",
@direction = 'I'
<html>
<form method="POST" name="mainForm" action="processimg.asp" enctype="multipart/form-data">
选择图片:
<input type=file name=mefile>
<input type=submit name=ok value="提交图片">
</form>
</html>
2、图片处理文件(就把提交的图片存入数据库)processimg.asp
<%
response.buffer=true
formsize=request.totalbytes
formdata=request.binaryread(formsize)
bncrlf=chrB(13) & chrB(10)
divider=leftB(formdata,clng(instrb(formdata,bncrlf))-1)
datastart=instrb(formdata,bncrlf & bncrlf)+4
dataend=instrb(datastart+1,formdata,divider)-datastart
mydata=midb(formdata,datastart,dataend)
dim msg
msg=""
set conn=server.CreateObject("ADODB.connection")
strconn="连接数据库"
conn.Open strconnset rec=server.Createobject("ADODB.recordset")
sql="SELECT * FROM 表名 where imgID="&request("ID")
rec.Open sql ,conn,1,3
rec.addNew
rec("ImageBit").appendchunk mydata
rec.update
rec.close
set rec=nothing
set conn=nothing
%>
3、显示图象文件showimg.asp
<%
Response.Expires = 0
response.buffer=True
response.clear
set conn=server.CreateObject("ADODB.connection")
strconn="连接数据库"
conn.Open strconn
str_wps=Trim(Request.QueryString("WPS"))
sql="select Img FROM 表名 where imgID="&request("ID")
set rs=server.createobject("ADODB.recordset")
rs.open sql,conn,1,1
if not rs.eof then
Response.ContentType = "image/gif"and"image/jpeg"
Response.BinaryWrite rs("ImageBit").getChunk(7500000)
rs.close
set rs=nothing
Conn.close
set conn=nothing
end if
%>
说明:字段ImageBit的类型为image