| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> RDO 2.0: here is sample for read/write BLOBS
Hi RDO/ODBC/Oracle experts!
Reading and Writing Binary Large Objects (BLOBS) to an SQL Server seems to
be
difficult in the end 90's. Here is my working framework for MS-Access,
MS-SQL 6.5
and Oracle 7.3. This project needs VB5 (SP2) and the ODBC Drivers listed in
the
program. My problem is not the functionality, my problem is the time needed
for
accessing a BLOB.
My MS-SQL Server reads a 1 MByte File in about 1.2s,
Ms-Access reads a 1 MByte File in about 5s Oracle 7.3 reads a 1 MByte File in about 80s (!)
or
MS-SQL Server reads a 8 MByte File in about 9s, Ms-Access reads a 8 MByte File in about 12s Oracle 7.3 reads a 8 MByte File in >> 20 min (I do not wait)
(and you never see 100 MByte before year 2000)
My biggest questions:
How can I retrieve an LONG RAW Column in Oracle with VB5 in an acceptable
time?
Why is Oracle Call Interface (OCI73 demo program CDEMO3.c) so slow in
reading a BLOB? Does somebody have read a BLOB (1..xx MB) from Oracle in
normal time?
Do I have to config ORACE 7.3 for better performance in BLOBs?
Constantin Olbrich
microTOOL GmbH
Constantin.Olbrich_at_microtool.de
' ***************************************************
' test suite for read/write an BLOB using RDO 2.0
' ***************************************************
Option Base 1
Option Explicit
Const IsOracle = 1 ' Microsoft ODBC Driver for Oracle 2.00.00.6327 Const IsMSSQL = 2 ' Microsoft ODBC Driver for SQL Server 2.65.0240 Const IsAccess = 3 ' Microsoft Access Driver 3.50.360200
Const ChunkSize = 32768
Public MyDb As Integer
Private Sub Command1_Click()
MousePointer = vbHourglass
' ******** change to your DB **********
MyDb = IsOracle ' IsOracle | IsMSSQL | IsAccess
' *************************************
Dim env As rdoEnvironment
Dim cn As rdoConnection
Dim i As Integer
Dim mytime As Long
Set env = rdoEngine.rdoEnvironments(0)
rdoEnvironments(0).CursorDriver = rdUseClientBatch
Select Case MyDb
Case IsOracle ' **** use your DSN ****
Set cn = env.OpenConnection(dsname:="world", _
Connect:="UID=INLRDO;PWD=INLRDO;", _
prompt:=rdDriverNoPrompt)
Debug.Print "Oracle"
Case IsMSSQL ' **** use your DSN ****
Set cn = env.OpenConnection(dsname:="INLRDO", _
Connect:="UID=CO;PWD=;", _
prompt:=rdDriverNoPrompt)
Debug.Print "MS SQL 6.5"
Case IsAccess ' **** use your DSN ****
Set cn = env.OpenConnection(dsname:="access", _
Connect:="UID=;PWD=;", _
prompt:=rdDriverNoPrompt)
Debug.Print "Access 97"
End Select
On Error Resume Next
Debug.Print "Drop table chunktable..."
cn.Execute "drop table chunktable"
On Error GoTo 0
Debug.Print "Creating new table..."
Select Case MyDb
Case IsOracle
cn.Execute "Create table chunktable(int1 int , " & _
"char1 char(30), text1 LONG RAW)"
cn.Execute "create unique index int1index on
chunktable(int1)"
Case IsMSSQL
cn.Execute "Create table chunktable(int1 int identity, " & _
"char1 char(30), text1 image)"
cn.Execute "create unique index int1index on
chunktable(int1)"
Case IsAccess
cn.Execute "Create table chunktable(int1 int , " & _
"char1 char(30), text1 image)"
cn.Execute "create unique index int1index on
chunktable(int1)"
End Select
On Error GoTo 0
Debug.Print "Begin insert."
' put a file some times in chunktable : ********* change for path/file
****
For i = 1 To 10
mytime = Timer
InsertFile "c:\download\sample.txt", i, cn
Debug.Print "insert duration : ", Timer - mytime
Next
Debug.Print "Begin read."
' get the file : ********* change path/filename ************
For i = 1 To 10
mytime = Timer
GetFile "c:\download\smpldnld.txt", i, cn
Debug.Print "read duration : ", Timer - mytime
Next
Debug.Print "done."
MousePointer = vbNormal
Sub InsertFile(Filename As String, FileNr As Integer, cn As rdoConnection)
Dim rs As rdoResultset
Dim currec As Integer
Dim sqlstr As String
sqlstr = "Select int1, char1, text1 from chunktable"
Set rs = cn.OpenResultset(Name:=sqlstr, _
Type:=rdOpenKeyset, _
LockType:=rdConcurRowVer)
rs.AddNew
rs("int1") = FileNr
rs("char1") = Now
rs.Update
currec = rs("int1")
rs.Edit
FileToColumn rs.rdoColumns("text1"), Filename, ChunkSize
rs("char1") = Now 'need to update at least one non-BLOB column
rs.Update
rs.Close
Dim rs As rdoResultset
Dim sqlstr As String
' open cursor again for read a file
If MyDb = IsAccess Then
sqlstr = "Select * from chunktable"
Else
sqlstr = "Select int1, char1, text1 from chunktable"
End If
sqlstr = sqlstr & " where int1 = " + Str$(FileNr)
Set rs = cn.OpenResultset(Name:=sqlstr, _
Type:=rdOpenForwardOnly, _
LockType:=rdConcurReadOnly, _
Options:=rdFetchLongColumns)
If rs!Int1 <> FileNr Then
Debug.Print "Error getting text tuple"
End If
Dim text1_len As Long, image1_len As Long
If rs("text1").ColumnSize = -1 Then
Debug.Print "RDO PROBLEM : Your ODBC Driver can't get size of an
BLOB"
Else
text1_len = rs("text1").ColumnSize
End If
If text1_len = 0 Then
Debug.Print "RDO PROBLEM : File Length is 0."
End If
ColumnToFile rs.rdoColumns("text1"), Filename, _
ChunkSize, text1_len
rs.Close
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
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(LeftOver)
Get SourceFile, , byteData()
Col.AppendChunk byteData()
ReDim byteData(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
 |
 |