请问,VB中如何向MYSQL数据库中插入图片呀..(BLOB数据类型),最好有代码,请高手看过来

[tgc99]

请问,VB中如何向MYSQL数据库中插入图片呀..(BLOB数据类型),最好有代码,请高手看过来

[cqq_chen]

个人想法：
不用在数据库中存放图片，而采用存放图片路径的方式如何？

[CathySun118]

请参考
Public Sub SaveImage(pImage As Object, pSql As String, pChunkSize As Long,Optional pPath As String = "", Optional pValue As String = "")
On Error GoTo Errhandler
Dim TmpPhoto As Object
Dim lngLogoSize As Long
Dim Fragment As Integer, Chunk() As Byte
Dim Chunks As Integer

Dim msg As String

Dim i As Long
Dim isok As Boolean

Dim FileName As String
Dim DataFile As Integer

FileName = pPath

DataFile = 1
Open FileName For Binary Access Read As DataFile
lngLogoSize = LOF(DataFile)
If lngLogoSize = 0 Then Close DataFile: Exit Sub

Chunks = lngLogoSize \ pChunkSize
Fragment = lngLogoSize Mod pChunkSize
If Chunks > 0 Then
' MsgBox pFace.res.GetString(1070)
pFace.MsgInfoById (1070)
Close DataFile
Exit Sub
End If
' rsTmp!photo.AppendChunk Null
ReDim Chunk(Fragment)
Get DataFile, , Chunk()
' rsTmp!photo.AppendChunk Chunk()
' ReDim Chunk(pChunkSize)
' For i = 1 To Chunks
' Get DataFile, , Chunk()
' rsTmp!photo.AppendChunk Chunk()
' Next i

Set TmpPhoto = CreateObject("hrms.clsPhoto")
TmpPhoto.ConnectDB pServer, pSessionId, GetMacAddress
TmpPhoto.UpdateImage pSql, Chunk, pFlag, pValue
' If pFlag = "0" Then
' pImage.Picture = LoadPicture("")
' End If
Close DataFile

Set TmpPhoto = Nothing
Exit Sub
Errhandler:
Set TmpPhoto = Nothing
End Sub

[cangwu_lee]

very good

[嗷嗷叫的老马]

..........................

[rainstormmaster]

http://topic.csdn.net/t/20050331/14/3897738.html

[tgc99]

TO :cqq_chen(我是谁) ( )
你好,发贴之前我就用的你说的这个办法..MySQL 带的Load_file()函数..

用这个函数有个问题.调用图片的时候,总提示此图NULL.

当MySQL服务与图在同一台机器上的时候,是可以调用的..但当运行的VB程序与要发送的图片是在A机器上,而MySQL数据库是在B机器上,却总提示此图为NULL.

不知道这个问题怎么处理.会不会把图先传到MYSQL库的那台机器上嘛,,有点麻烦..请问这个图的路径怎么写才正确呢???

[VBAdvisor]

路径是UNC Path.先判断一下。再处理。
UNC path like "\\targetcomputer\sharefolder"

Public Declare Function PathIsNetworkPath Lib "SHLWAPI.DLL" Alias "PathIsNetworkPathA" (ByVal pszPath As String) As Boolean
Public Declare Function PathIsUNCServerShare Lib "SHLWAPI.DLL" Alias "PathIsUNCServerShareA" (ByVal pszPath As String) As Boolean
Public Declare Function PathIsUNC Lib "SHLWAPI.DLL" Alias "PathIsUNCA" (ByVal pszPath
Public Declare Function PathIsUNCServer Lib "SHLWAPI.DLL" Alias "PathIsUNCServerA" (ByVal pszPath As String) As Boolean

[VBAdvisor]

我以前回答过多次此类问题，但2006年的贴子不能查到了。

VB把文件存入数据库IMAGE字段

Sub Savepic(FileName As String, IndexNumber As Long)
Dim DcnNWind As New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
DcnNWind.CursorLocation = adUseClient
DcnNWind.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CUSTOM;Data Source=SERVER"
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "CustomInfo", DcnNWind, , adCmdTable
rs.Move (IndexNumber)
Call FileToBlob(rs.Fields("Image"), FileName, FileLen(FileName))
rs.UpdateBatch adAffectCurrent
End Sub

Private Sub FileToBlob(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long )
Dim fnum As Integer, bytesLeft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "Field doesn't support the GetChunk method."
End If
fnum = FreeFile
Open FileName For Binary As fnum
bytesLeft = LOF(fnum)
Do While bytesLeft
bytes = bytesLeft
If bytes > ChunkSize Then bytes = ChunkSize
ReDim tmp(1 To bytes) As Byte
Get #1, , tmp
fld.AppendChunk tmp
bytesLeft = bytesLeft - bytes
Loop
Close #fnum
End Sub

VB把文件从IMAGE字段中读到文件中。

Sub loadpic(IndexNumber As Long)
Dim DcnNWind As New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
DcnNWind.CursorLocation = adUseClient
DcnNWind.Open "Provider=SQLOLEDB.1;Integrated Security=SSI;Persist Security Info=False;Initial Catalog=CUSTOM;Data Source=SERVER"
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "CustomInfo", DcnNWind, , adCmdTable
rs.Move (IndexNumber)
Call BlobToFile(rs.Fields("Image"), "c:\windows\temp\tmp.bmp", rs.Fields("Image").ActualSize)
End Sub

Private Sub BlobToFile(fld As ADODB.Field, FileName As String, Optional ChunkSize As Long )
Dim fnum As Integer, bytesLeft As Long, bytes As Long
Dim tmp() As Byte
If (fld.Attributes And adFldLong) = 0 Then
Err.Raise 1001, , "Field doesn't support the GetChunk method."
End If
If Dir$(FileName) <> "" Then Kill FileName
fnum = FreeFile
Open FileName For Binary As fnum
bytesLeft = fld.ActualSize
Do While bytesLeft
bytes = bytesLeft
If bytes > ChunkSize Then bytes = ChunkSize
tmp = fld.GetChunk(bytes)
Put #fnum, , tmp
bytesLeft = bytesLeft - bytes
Loop
Close #fnum
End Sub

[VBAdvisor]

你自己修改一下.

[tgc99]

TO:rainstormmaster(暴风雨 v2.0)

谢谢你的回复,,你发的这个,我看了,,只是,在rs.Update的时候报错呢...我用的mysql数据库,ODBC连接

strSQL = "select * from table where aa= '" & 11 & "' and bb=" & 22
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
WriteToDB rs("image"), imagefile
rs.Update''在这报错.
rs.Close

报错:
[MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '府\Z?;(靽\"?a?j?{S1砷毺袎?姟??滒陀隯R?\'也磫?馘{a鐲鴯 ?t?伵8c?7? at line 1

[tgc99]

接上面的..WriteToDB rs("image"), imagefile

Public Function WriteToDB(ByRef col As ADODB.Field, ByVal FileName As String) As Boolean
On Error GoTo ErrMsg
Dim mStream As ADODB.Stream
Set mStream = New ADODB.Stream

WriteToDB = False
mStream.Type = adTypeBinary
mStream.Open
mStream.LoadFromFile FileName
col.Value = mStream.Read

mStream.Close
Set mStream = Nothing
WriteToDB = True
Exit Function
ErrMsg:
MsgBox "存储文件到数据库时出现错误．" & vbCrLf & Err.Description, vbExclamation + vbOKOnly, "提示"
End Function

[VBAdvisor]

strSQL = "select * from table where aa= '" & 11 & "' and bb=" & 22
???

[tgc99]

TO:VBAdvisor(Sunlight)
你好,,这是指rainstormmaster(暴风雨 v2.0)给的例子...http://topic.csdn.net/t/20050331/14/3897738.html

strSQL = "select * from table where aa= '" & 11 & "' and bb=" & 22
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
WriteToDB rs("image"), imagefile
rs.Update''在这报错.
rs.Close

[tgc99]

大家的方法,我倒都用了,就是全部报错...

方法1:使用MySQL的Load_file函数..可是却提示图片不存在..

方法2:保存二进制数据流,,也报错,存不进去

发送图的程序与图是在WIN2003上面,MySQL数据库是在一台LINUX系统上..不知道是不是跟这些有关呢?????

[tgc99]

我查到的资料:看来这个不能用了,因为我的图片与MYSQL库不在同一机器上..

load_file()函数:默认所在的目录是mysql下的data目录

这个文件必须在服务器上，必须指定文件完整的路径名，并且你必须有 FILE 权限。文件必须

完全可读，并且小于 max_allowed_packet。 如果该文件不存在，或因为上面的任一原因而不

能被读出，函数返回 NULL