在CSDN上有个相关的帖,但我用下面的存储过程好像不行
我的servername是IBM,用户sa,密码123,数据库名lizcreate 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)
insert photo(photo) values(0x)
--select * from photo
--truncate table photo --读入
sp_textcopy 'ibm', 'sa ', '123', 'liz', 'photo', 'photo', 'd:\ip.jpg', 'where id=1', 'I' --输出
sp_textcopy 'ibm', 'sa ', '123', 'liz', 'photo', 'photo', 'e:\ip.jpg', 'where id=1', 'o'
解决方案 »
- 将字符串转换为datetime 数据类型时失败,请高手帮助解决,谢谢!
- 如何把一个表的查询结果分页显示
- 怎么卸载SQL2008 Express
- 查询分析器里脚本关键字的单词对照表
- 关于论坛的数据库的问题?
- MS SQL的连接问题
- 有这样一个查询:select top 6 id,title from [mytable] where id in (34,279,34,44,45,33,46,279,45,46),怎样使结果按条件先后排序?
- 行列转换
- 怎样通过vb代码修改Access2000的数据库的表结构?
- 关于SQL SERVER 的几个初级问题?
- 初学server2000
- SQL插入语句有错?SOS!!!
本范例使用 AppendChunk 和 GetChunk 方法来用其他记录中的数据填充图像字段。Public Sub AppendChunkX()Dim cnn1 As ADODB.Connection
Dim rstPubInfo As ADODB.Recordset
Dim strCnn As String
Dim strPubID As String
Dim strPRInfo As String
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varLogo As Variant
Dim varChunk As VariantConst conChunkSize = 100' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
cnn1.Open strCnn' Open the pub_info table.
Set rstPubInfo = New ADODB.Recordset
rstPubInfo.CursorType = adOpenKeyset
rstPubInfo.LockType = adLockOptimistic
rstPubInfo.Open "pub_info", cnn1, , , adCmdTable' Prompt for a logo to copy.
strMsg = "Available logos are : " & vbCr & vbCr
Do While Not rstPubInfo.EOF
strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & _
vbCr & vbCr
rstPubInfo.MoveNext
Loop
strMsg = strMsg & "Enter the ID of a logo to copy:"
strPubID = InputBox(strMsg)' Copy the logo to a variable in chunks.
rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
lngLogoSize = rstPubInfo!logo.ActualSize
Do While lngOffset < lngLogoSize
varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
varLogo = varLogo & varChunk
lngOffset = lngOffset + conChunkSize
Loop' Get data from the user.
strPubID = Trim(InputBox("Enter a new pub ID" & _
" [must be > 9899 & < 9999]:"))
strPRInfo = Trim(InputBox("Enter descriptive text:"))' Add the new publisher to the publishers table to avoid
' getting an error due to foreign key constraint.
cnn1.Execute "INSERT publishers(pub_id, pub_name) VALUES('" & _
strPubID & "','Your Test Publisher')"' Add a new record, copying the logo in chunks.
rstPubInfo.AddNew
rstPubInfo!pub_id = strPubID
rstPubInfo!pr_info = strPRInfolngOffset = 0 ' Reset offset.
Do While lngOffset < lngLogoSize
varChunk = LeftB(RightB(varLogo, lngLogoSize - lngOffset), _
conChunkSize)
rstPubInfo!logo.AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop
rstPubInfo.Update' Show the newly added data.
MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
"Description: " & rstPubInfo!pr_info & vbCr & _
"Logo size: " & rstPubInfo!logo.ActualSize' Delete new records because this is a demonstration.
rstPubInfo.Requery
cnn1.Execute "DELETE FROM pub_info " & _
"WHERE pub_id = '" & strPubID & "'"cnn1.Execute "DELETE FROM publishers " & _
"WHERE pub_id = '" & strPubID & "'"rstPubInfo.Close
cnn1.CloseEnd Sub
字段类型Image(不同数据库不同,如果没有再查找blob等类型),然后使用如下编程套路:
从数据库中读取图片的脚本如下:
这样的字段不能放在数据窗口的Detail节中,通常用户点击某行数据,获取该行的主键信息,以该信息为条件检索图片信息。比如,主键为id,图片保存在zp字段中:
在dw_1的Clicked事件中编写如下程序:
String ls_id
blob lb_zp
if Row < 1 then return
ls_id = This.GetItemString(Row,'id')
selectblob zp into :lb_zp
from employee where id = :ls_id;
if sqlca.SQLNRows > 0 then //判断是否读取成功
p_1.SetPicture(lb_zp) //显示在picture控件p_1中
end if
向数据库中保存图片的脚本如下:
首先需要明确:
1、事务对象需要设置AutoCommit属性为True
2、使用UpdateBlob语句修改,一次只能修改一条记录
3、在使用UpdateBlob之前数据应该已经存在。
基于上述原因,通常在卡片式的录入界面中(通常使用Freedom类型的数据窗口)让用户输入照片。数据保存后,马上调用以下脚本处理图片:
blob lb_zp
integer li_file
li_file = FileOpen(p_1.filename, StreamMode!)
IF li_file <> -1 THEN
FileRead(li_file,lb_zp)
FileClose(li_file)
UPDATEBLOB Employee SET zp = :lb_zp
WHERE id = :ls_id;
END IF
IF sqlca.SQLNRows > 0 THEN
COMMIT;
END IF