Start a new project in Visual Basic. Form1 is created by default.
Add a Command button, Command1, to Form1.
Paste the following code into the General Declarations section of Form1:
Private Sub Command1_Click()
MousePointer = vbHourglass
Dim cn As rdoConnection
Dim rs As rdoResultset, TempRs As rdoResultset
Dim cnstr As String, sqlstr As String
cnstr = "Driver={SQLServer};Server=myserver;Database=pubs;Uid=sa;Pwd="
sqlstr = "Select int1, char1, text1, image1 from chunktable" rdoEnvironments(0).CursorDriver = rdUseServer
Set cn = rdoEnvironments(0).OpenConnection( _
"", rdDriverNoPrompt, False, cnstr)
On Error Resume Next
If cn.rdoTables("chunktable").Updatable Then
'table exists
End If
If Err > 0 Then
On Error GoTo 0
Debug.Print "Creating new table..."
cn.Execute "Create table chunktable(int1 int identity, " & _
"char1 char(30), text1 text, image1 image)"
cn.Execute "create unique index int1index on chunktable(int1)"
End If
On Error GoTo 0
Set rs = cn.OpenResultset(Name:=sqlstr, _
Type:=rdOpenDynamic, _
LockType:=rdConcurRowver)
If rs.EOF Then
rs.AddNew
rs("char1") = Now
rs.Update
rs.Requery
End If
Dim currec As Integer
currec = rs("int1")
rs.Edit
FileToColumn rs.rdoColumns("text1"), App.Path & "\README.TXT", 102400
FileToColumn rs.rdoColumns("image1"), App.Path & "\SETUP.BMP", 102400
rs("char1") = Now 'need to update at least one non-BLOB column
rs.Update 'this code gets the columnsize of each column
Dim text1_len As Long, image1_len As Long
If rs("text1").ColumnSize = -1 Then
'the function Datalength is SQL Server specific
'so you may have to change this for your database
sqlstr = "Select Datalength(text1) As text1_len, " & _
"Datalength(image1) As image1_len from chunktable " & _
"Where int1=" & currec
Set TempRs = cn.OpenResultset(Name:=sqlstr, _
Type:=rdOpenStatic, _
LockType:=rdConcurReadOnly)
text1_len = TempRs("text1_len")
image1_len = TempRs("image1_len")
TempRs.Close
Else
text1_len = rs("text1").ColumnSize
image1_len = rs("image1").ColumnSize
End If ColumnToFile rs.rdoColumns("text1"), App.Path & "\text1.txt", _
102400, text1_len
ColumnToFile rs.rdoColumns("image1"), App.Path & "\image1.bmp", _
102400, image1_len
MousePointer = vbNormal
End Sub Sub ColumnToFile(Col As rdoColumn, ByVal DiskFile As String, _
BlockSize As Long, ColSize As Long)
Dim NumBlocks As Integer
Dim LeftOver As Long
Dim byteData() As Byte 'Byte array for LongVarBinary
Dim strData As String 'String for LongVarChar
Dim DestFileNum As Integer, i As Integer ' Remove any existing destination file
If Len(Dir$(DiskFile)) > 0 Then
Kill DiskFile
End If DestFileNum = FreeFile
Open DiskFile For Binary As DestFileNum NumBlocks = ColSize \ BlockSize
LeftOver = ColSize Mod BlockSize
Select Case Col.Type
Case rdTypeLONGVARBINARY
byteData() = Col.GetChunk(LeftOver)
Put DestFileNum, , byteData()
For i = 1 To NumBlocks
byteData() = Col.GetChunk(BlockSize)
Put DestFileNum, , byteData()
Next i
Case rdTypeLONGVARCHAR
For i = 1 To NumBlocks
strData = String(BlockSize, 32)
strData = Col.GetChunk(BlockSize)
Put DestFileNum, , strData
Next i
strData = String(LeftOver, 32)
strData = Col.GetChunk(LeftOver)
Put DestFileNum, , strData
Case Else
MsgBox "Not a ChunkRequired column."
End Select
Close DestFileNum End Sub Sub FileToColumn(Col As rdoColumn, DiskFile As String, _
BlockSize As Long)
'moves a disk file to a ChunkRequired column in the table
'A Byte array is used to avoid a UNICODE string
Dim byteData() As Byte 'Byte array for LongVarBinary
Dim strData As String 'String for LongVarChar
Dim NumBlocks As Integer
Dim filelength As Long
Dim LeftOver As Long
Dim SourceFile As Integer
Dim i As Integer
SourceFile = FreeFile
Open DiskFile For Binary Access Read As SourceFile
filelength = LOF(SourceFile) ' Get the length of the file
If filelength = 0 Then
Close SourceFile
MsgBox DiskFile & " empty or not found."
Else
' Calculate number of blocks to read and left over bytes
NumBlocks = filelength \ BlockSize
LeftOver = filelength Mod BlockSize
Col.AppendChunk Null Select Case Col.Type
Case rdTypeLONGVARCHAR
' Read the 'left over' amount of LONGVARCHAR data
strData = String(LeftOver, " ")
Get SourceFile, , strData
Col.AppendChunk strData
strData = String(BlockSize, " ")
For i = 1 To NumBlocks
Get SourceFile, , strData
Col.AppendChunk strData
Next i
Close SourceFile
Case rdTypeLONGVARBINARY
' Read the left over amount of LONGVARBINARY data
ReDim byteData(0, LeftOver)
Get SourceFile, , byteData()
Col.AppendChunk byteData()
ReDim byteData(0, BlockSize)
For i = 1 To NumBlocks
Get SourceFile, , byteData()
Col.AppendChunk byteData()
Next i
Close SourceFile
Case Else
MsgBox "not a chunkrequired column."
End Select
End If End Sub You will need to change the Server, Database, UID, and PWD values in the cnstr variable in order to connect to your database.
The code in the Command1_Click event expects to find two files named README.TXT and SETUP.BMP in the current directory. These files are usually found in the Windows directory. You can either move these files to your current directory or change the path to match another bitmap and text file on your hard drive.
Press the F5 key to start the program.
Click the Command1 button to execute the RDO code. The code will automatically create a table named chunktable, if it does not already exist, and move the text and bitmap files into and out of the BLOB columns.
Add a Command button, Command1, to Form1.
Paste the following code into the General Declarations section of Form1:
Private Sub Command1_Click()
MousePointer = vbHourglass
Dim cn As rdoConnection
Dim rs As rdoResultset, TempRs As rdoResultset
Dim cnstr As String, sqlstr As String
cnstr = "Driver={SQLServer};Server=myserver;Database=pubs;Uid=sa;Pwd="
sqlstr = "Select int1, char1, text1, image1 from chunktable" rdoEnvironments(0).CursorDriver = rdUseServer
Set cn = rdoEnvironments(0).OpenConnection( _
"", rdDriverNoPrompt, False, cnstr)
On Error Resume Next
If cn.rdoTables("chunktable").Updatable Then
'table exists
End If
If Err > 0 Then
On Error GoTo 0
Debug.Print "Creating new table..."
cn.Execute "Create table chunktable(int1 int identity, " & _
"char1 char(30), text1 text, image1 image)"
cn.Execute "create unique index int1index on chunktable(int1)"
End If
On Error GoTo 0
Set rs = cn.OpenResultset(Name:=sqlstr, _
Type:=rdOpenDynamic, _
LockType:=rdConcurRowver)
If rs.EOF Then
rs.AddNew
rs("char1") = Now
rs.Update
rs.Requery
End If
Dim currec As Integer
currec = rs("int1")
rs.Edit
FileToColumn rs.rdoColumns("text1"), App.Path & "\README.TXT", 102400
FileToColumn rs.rdoColumns("image1"), App.Path & "\SETUP.BMP", 102400
rs("char1") = Now 'need to update at least one non-BLOB column
rs.Update 'this code gets the columnsize of each column
Dim text1_len As Long, image1_len As Long
If rs("text1").ColumnSize = -1 Then
'the function Datalength is SQL Server specific
'so you may have to change this for your database
sqlstr = "Select Datalength(text1) As text1_len, " & _
"Datalength(image1) As image1_len from chunktable " & _
"Where int1=" & currec
Set TempRs = cn.OpenResultset(Name:=sqlstr, _
Type:=rdOpenStatic, _
LockType:=rdConcurReadOnly)
text1_len = TempRs("text1_len")
image1_len = TempRs("image1_len")
TempRs.Close
Else
text1_len = rs("text1").ColumnSize
image1_len = rs("image1").ColumnSize
End If ColumnToFile rs.rdoColumns("text1"), App.Path & "\text1.txt", _
102400, text1_len
ColumnToFile rs.rdoColumns("image1"), App.Path & "\image1.bmp", _
102400, image1_len
MousePointer = vbNormal
End Sub Sub ColumnToFile(Col As rdoColumn, ByVal DiskFile As String, _
BlockSize As Long, ColSize As Long)
Dim NumBlocks As Integer
Dim LeftOver As Long
Dim byteData() As Byte 'Byte array for LongVarBinary
Dim strData As String 'String for LongVarChar
Dim DestFileNum As Integer, i As Integer ' Remove any existing destination file
If Len(Dir$(DiskFile)) > 0 Then
Kill DiskFile
End If DestFileNum = FreeFile
Open DiskFile For Binary As DestFileNum NumBlocks = ColSize \ BlockSize
LeftOver = ColSize Mod BlockSize
Select Case Col.Type
Case rdTypeLONGVARBINARY
byteData() = Col.GetChunk(LeftOver)
Put DestFileNum, , byteData()
For i = 1 To NumBlocks
byteData() = Col.GetChunk(BlockSize)
Put DestFileNum, , byteData()
Next i
Case rdTypeLONGVARCHAR
For i = 1 To NumBlocks
strData = String(BlockSize, 32)
strData = Col.GetChunk(BlockSize)
Put DestFileNum, , strData
Next i
strData = String(LeftOver, 32)
strData = Col.GetChunk(LeftOver)
Put DestFileNum, , strData
Case Else
MsgBox "Not a ChunkRequired column."
End Select
Close DestFileNum End Sub Sub FileToColumn(Col As rdoColumn, DiskFile As String, _
BlockSize As Long)
'moves a disk file to a ChunkRequired column in the table
'A Byte array is used to avoid a UNICODE string
Dim byteData() As Byte 'Byte array for LongVarBinary
Dim strData As String 'String for LongVarChar
Dim NumBlocks As Integer
Dim filelength As Long
Dim LeftOver As Long
Dim SourceFile As Integer
Dim i As Integer
SourceFile = FreeFile
Open DiskFile For Binary Access Read As SourceFile
filelength = LOF(SourceFile) ' Get the length of the file
If filelength = 0 Then
Close SourceFile
MsgBox DiskFile & " empty or not found."
Else
' Calculate number of blocks to read and left over bytes
NumBlocks = filelength \ BlockSize
LeftOver = filelength Mod BlockSize
Col.AppendChunk Null Select Case Col.Type
Case rdTypeLONGVARCHAR
' Read the 'left over' amount of LONGVARCHAR data
strData = String(LeftOver, " ")
Get SourceFile, , strData
Col.AppendChunk strData
strData = String(BlockSize, " ")
For i = 1 To NumBlocks
Get SourceFile, , strData
Col.AppendChunk strData
Next i
Close SourceFile
Case rdTypeLONGVARBINARY
' Read the left over amount of LONGVARBINARY data
ReDim byteData(0, LeftOver)
Get SourceFile, , byteData()
Col.AppendChunk byteData()
ReDim byteData(0, BlockSize)
For i = 1 To NumBlocks
Get SourceFile, , byteData()
Col.AppendChunk byteData()
Next i
Close SourceFile
Case Else
MsgBox "not a chunkrequired column."
End Select
End If End Sub You will need to change the Server, Database, UID, and PWD values in the cnstr variable in order to connect to your database.
The code in the Command1_Click event expects to find two files named README.TXT and SETUP.BMP in the current directory. These files are usually found in the Windows directory. You can either move these files to your current directory or change the path to match another bitmap and text file on your hard drive.
Press the F5 key to start the program.
Click the Command1 button to execute the RDO code. The code will automatically create a table named chunktable, if it does not already exist, and move the text and bitmap files into and out of the BLOB columns.
解决方案 »
- 点击按钮如何弹出文本文档?
- 水晶报表中,怎么实现根据报表字段的内容动态的插入子报表
- vb如何调用文件建立oracle存储过程
- 为什么说内存不足?
- 三层体系分布式数据库程序开发,如果客户端与服务器不在一台机上怎么办的
- vb+sql+update问题,如何从其他表中提取数据与本表中进行加减运算.
- 有什么办法通过程序发短消息,用过的介绍一下
- 求高手详细解释。。。谢谢
- ADO问题:CONNECT中的SERVER游标和CLIENT游标到底有什么区别
- 为什么我在打开一个表的时候我想按字段的日期来查询的时候出错呢
- vbt自带的visdata数据管理器为什么打表access表第一条记录第一列的数据会被删除呢?
- 如何动态创建MSflexgrid控件?
我在做ASP,我想将字段中的大文本取出来后直接变成字符放到网页上,能实现吗?
请高人指点!多谢!