OLE 对象数据类型

[rong007]

怎么样在sql server字段的类型为OLE 对象
来放图片

[online]

编程工具: sql,vb
问题: 如何用SQL存储图片？？？再用VB调用这些图片？？？
水平: 刚入门

回答: 注:写图片文件到数据库
Col为栏位名,ImgFile为要写到数据库的图片文件名,BockSize为每次写多少字节,缺省为每次写8K字节到数据库
Public Sub WriteDB(Col As ADODB.Field, ImgFile As String, Optional BlockSize As Long=8192)
Dim byteData() As Byte, FileLength As Long, NumBlocks As Integer
Dim LeftOver As Long, SourceFileNum As Integer, i As Integer

SourceFileNum = FreeFile
Open ImgFile For Binary As SourceFileNum
FileLength = LOF(SourceFileNum)
If FileLength > 50 Then
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize

ReDim byteData(LeftOver)
Get SourceFileNum, , byteData()
Col.AppendChunk byteData()
ReDim byteData(BlockSize)
For i = 1 To NumBlocks
Get SourceFileNum, , byteData()
Col.AppendChunk byteData()
Next
End If
Close SourceFileNum
End Sub


ImgFile为从数据库读出数据写到磁盘的文件名,BlockSize为每次向文件写多少个字节,缺省为8K字节,当ReadDB=True,得到图片文件後,可以用LoadPicter(图片文件名)显示图片到PictureBox或Image框中.
Public Function ReadDB(Col As ADODB.Field, ImgFile As String,Optional BlockSize As Long=8192) As Boolean
Dim byteData() As Byte, NumBlocks As Integer
Dim LeftOver As Long, DestFileNum As Integer, i As Integer
Dim ColSize As Long

On Error GoTo ErrRead
ReadDB = False

'If Dir(ImgFile) <> "" Then Kill ImgFile

DestFileNum = FreeFile
Open ImgFile For Binary As #DestFileNum

ColSize = Col.ActualSize
NumBlocks = ColSize \ BlockSize
LeftOver = ColSize Mod BlockSize

ReDim byteData(LeftOver)
byteData() = Col.GetChunk(LeftOver)
Put DestFileNum, , byteData()
ReDim byteData(BlockSize)
For i = 1 To NumBlocks
byteData() = Col.GetChunk(BlockSize)
Put #DestFileNum, , byteData()
Next
If LOF(DestFileNum) > 200 Then ReadDB = True
Close #DestFileNum
Exit Function

ErrRead:
MsgBox "READ PICTURE ERR:" & Err.Number
ReadDB = False
Exit Function
End Function//如果ReadDB=False则写文件失败。
还有用此纯代码读取图片时,在代码调试中不会出现错误,在运行时如果快速浏览带有图片的记录时,会出现莫名其妙的程序死机.如上述代码,我都用了错误控制技术,但还是出现。
我用的是VB6.0 ADO方法 + SQL SERVER 7.0(都是英文版)C/S架构,如有能够解决此死机问题的，请在下面继续贴出。

Specner Yang的意见：
较佳方式是使用 Stream object。微软有提供范例，而且使用对象刚好是SQL Server。
微软的Knowledge Base的文章：“Q258038 OWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object”。

xibeilang的意见：
VB是0 BASE的数组,a(1)实际有两个元素，a(0),a(1)，将
ReDim byteData(LeftOver)
改为ReDim byteData(LeftOver-1)
并应该判断leftover是否为零。

唐劲松的意见：
建立连接 Adodc, 用Image 绑定该字段。

fanfan的意见：
可以试试用SQL Server来编写一个存储过程，通过参数传递image数据类型，并且可以在存储过程中将日志关闭，避免日志溢出，最后在vb里调用存储过程，就可以了。具体细节可以看vb和sqlserver的帮助文件。

此问题由徐法

[online]

要是access的 用下面的 适用于 ado
Declare Sub RtlMoveMemory Lib "KERNEL32" (lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)

Sub SavePictureToAdodc(rs As ADODB.Recordset, ByVal FileName As String)
Dim Length As Long, f As Integer
Length = FileLen(FileName)

ReDim bArray(Length + 12) As Byte, bArray2(Length) As Byte
bArray(0) = &H6C: bArray(1) = &H74
RtlMoveMemory bArray(4), Length, 4

f = FreeFile
Open FileName For Binary As #f
Get #f, , bArray2
Close #1

RtlMoveMemory bArray(8), bArray2(0), Length

rs("相片").AppendChunk bArray
End Sub
该模块可以将图片以二进制形式存入mdb数据库

[online]

sqlserver没有ole字段，只有image类型

[rong007]

我的是sql server的可不可以用image类型

[rong007]

Private Sub Command5_Click()
Dim DbName As String
Dim RestoreName As String
Db_Name = "xiaorong"
RestoreName = "D:\"
'restore database 就是sql server的还原语法。
Dim strR As String
Dim stm As ADODB.Stream
Set stm = New ADODB.Stream
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'在这里打开master数据库
'接下来
Set cn = New ADODB.Connection
Dim sDB As String
sDB = "master"
SQL_SRV = "design2"
NewConnString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=design2;Initial Catalog=5555"
cn.Open NewConnString
sql = "select * from aa"
rs.Open sql, cn, , , adCmdText
With stm
.Type = adTypeBinary
.Open

Picture1.Picture = .Write(rs(1))
.Close
End With
cn.Close
End Sub


我这样为什么不可以提出来呀