怎么往SQLserver2000表中插入图片数据?
假如我有一张学生信息表,除了基本信息之外怎么实现图片的插入?
create table student(
sno int,
sname varchar(10),
classno char,
...??怎么插入图片呢在这里?
)
如果建立好了又怎么运行是、SQL语句让其插入图片?
[解决办法]
create PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@photoname 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 ' + @photoname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str ,no_output
create table photo(id int identity(1,1),photo image,memo nvarchar(50) default 'my photo')
go
insert photo(photo) values(0x) -- 必须的,且不是null
select * from photo
--truncate table photo
sp_textcopy 'roy','sa','wuxi','test','photo','photo','E:\photo\myphoto\roy3.jpg','where id=5','I' --注意条件是 id=1
3、读入
sp_textcopy 'roy','sa','wuxi','test','photo','photo','E:\photo\photo\21.jpg','where id=1','I' --注意条件是 id=1
sp_textcopy 'roy','sa','wuxi','test','photo','photo','E:\photo\photo\33.jpg','where id=2','I' --注意条件是 id=2
go
4、读出成文件
sp_textcopy 'roy','sa','wuxi','test','photo','photo','E:\photo\roy.jpg','where id=1','O' --注意条件是 id=1
sp_textcopy 'roy','sa','wuxi','test','photo','photo','E:\photo\roy1.jpg','where id=2','O'
sp_textcopy 'roy','sa','wuxi','test','photo','photo','E:\photo\roy2.jpg','where id=3','O'
sp_textcopy 'roy','sa','wuxi','test','photo','photo','E:\photo\roy3.jpg','where id=4','O'
sp_textcopy 'roy','sa','wuxi','test','photo','photo','c:\bb.doc','where id=5','O' --注意条件是 id=2
[解决办法]
数据库里有一个image 数据类型
把图片转成二进制类型用sql语句插入
[解决办法]
agree 楼上
也可以在表中存储图片的地址
[解决办法]
sp_textcopy可能比较麻烦, 用OPENROWSET可能更好一点.
create table student(sno int,sname varchar(10),classno char, photo varbinary(max))INSERT dbo.ProductImage (sno, sname, classno, photo) SELECT 1, '张三', '1', BulkColumn FROM OPENROWSET( BULK 'C:\Program Files\Microsoft SQL Server\80\Tools\HTML\dbicon.gif', SINGLE_BLOB) as photo