drop PROCEDURE BYS_READ_FILE
go
CREATE PROCEDURE BYS_READ_FILE
@fileName varchar(8000), @message varchar(8000) OUTPUT AS
BEGIN
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varbinary(8000)
DECLARE @src varchar(255), @desc varchar(255)
-- SET TEXTSIZE 99999999
-- Create an object.
EXEC @hr = sp_OACreate 'ADODB.Stream', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
-- Set a property.
EXEC @hr = sp_OASetProperty @object, 'type', 1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OASetProperty @object, 'Mode', 3
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OAMethod @object, 'open'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OAMethod @object, 'LoadFromFile', NULL, @fileName
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
-- EXEC @hr = sp_OAMethod @object, 'Size', @return OUT
-- IF @hr <> 0
-- BEGIN
-- EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
-- SET @message = @src+':'+@desc
-- RETURN
-- END
PRINT cast(@return as int)
CREATE TABLE #READ_BUFFER(
DATA image
)
INSERT INTO #READ_BUFFER VALUES ('')
set @return = cast('' as varbinary)
DECLARE @pBinary varbinary(16)
DECLARE @pos int
SELECT @pBinary = TEXTPTR(DATA) FROM #READ_BUFFER
SET @pos = 0
UPDATETEXT #READ_BUFFER.DATA @pBinary 0 NULL
WHILE @return IS NOT NULL
BEGIN
EXEC @hr = sp_OAMethod @object, 'Read', @return OUT, 8000
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
IF @return IS NOT NULL
BEGIN
DECLARE @readData varbinary(8000)
set @readData = cast(@return as varbinary(8000))
UPDATETEXT #READ_BUFFER.DATA @pBinary NULL 0 @readData
set @pos = @pos + DATALENGTH(@return)
END
END
EXEC @hr = sp_OAMethod @object, 'close'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
insert into a(s,ll) select DATA,datalength(DATA) as aaa from #READ_BUFFER
-- SELECT DATA as s into a FROM #READ_BUFFER
END
GO
------红色的代表是插入表
CREATE PROCEDURE BYS_WRITE_FILE
@fileName varchar(255), @datBinary image, @message varchar(1024) OUTPUT AS
BEGIN
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return sql_variant
DECLARE @src varchar(255), @desc varchar(255)
-- Create an object.
EXEC @hr = sp_OACreate 'ADODB.Stream', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
-- Set a property.
EXEC @hr = sp_OASetProperty @object, 'type', 1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OASetProperty @object, 'Mode', 3
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OAMethod @object, 'open'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OAMethod @object, 'Write', NULL, @datBinary
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OAMethod @object, 'SaveToFile', NULL, @fileName,2
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
EXEC @hr = sp_OAMethod @object, 'close'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @message = @src+':'+@desc
RETURN
END
END
GO
declare @fileName varchar(255)
declare @message varchar(1024)
set @filename='E:/14407081917170600.jpg'
exec BYS_READ_FILE @filename,@message
--insert into a(s) values(@message)
--print @message
create proc pro_GETIMAGE
@id int
as
select * from a where id=@id
go
drop table a
go
create table a
(
id int identity(1,1),
s image,
ll int
)
go
create proc pro_Ja
@id int
as
select s from a where id=@id
开启sql配置服务
--
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;
--GO
--sp_configure 'Ole Automation Procedures', 1;
--GO
--RECONFIGURE;
--GO
----------打印图片
int imgId = Convert.ToInt32(Request.QueryString["id"].ToString());//imgId为图片的id
string connString = "server=192.168.0.128;database=EastHH_User;uid=sa";
SqlConnection conn = new SqlConnection(connString);
//数据库操作语句
SqlCommand comm = new SqlCommand("pro_GETIMAGE", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4));
comm.Parameters["@id"].Value = imgId;
conn.Open();//打开数据库连接
SqlDataReader dr = comm.ExecuteReader();//读出数据
dr.Read();//读一行
//设定输出文件的类型
//Response.ContentType = "bmp";
//输出图片文件二进制数据
Response.OutputStream.Write((byte[])dr["s"], 0, (int)dr["ll"]);
Response.End();
dr.Close();
conn.Close();
接受方式
<img src="readimage.aspx?id=1">