Oracle Blob

[Guest_imported]

Hi everybody,

I use an web-application in ASP with a Oracle database.
I would like to insert in the database files using blob datatype. How can I do that ??

Thanks in advance.

 

[bluewolf]

Hi,
You can use GetChunk and AppendChunk methods of Field object. I don't try with Oracle but I use good with SQL Server and DB2.
After that is 2 subs in VB6 of an my example:

Const conChunkSize = 8192 '2048 increment multiples depending the image sizes

Sub SaveImage(ByVal oImage As Image)
Dim cnImage As Connection
Dim rsImage As Recordset
Dim sFile As String
Dim nHandle As Integer
Dim Chunk() As Byte
Dim lSize As Long
Dim iChunks As Integer
Dim nFragmentOffset As Integer
Dim i As Integer

Screen.MousePointer = vbHourglass
sFile = App.Path & "\outimage.tmp"
SavePicture oImage, sFile

Set cnImage = New Connection
cnImage.Open "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=VBTest;UID=sa;PWD=12345"
Set rsImage = New Recordset
rsImage.Open "Images", cnImage, adOpenForwardOnly, adLockOptimistic

nHandle = FreeFile
Open sFile For Binary Access Read As nHandle
lSize = LOF(nHandle)
If nHandle = 0 Then
Close nHandle
End If
iChunks = lSize \ conChunkSize
nFragmentOffset = lSize Mod conChunkSize
rsImage.AddNew
ReDim Chunk(nFragmentOffset)
Get nHandle, , Chunk()
rsImage("Image&quot.AppendChunk Chunk()
ReDim Chunk(conChunkSize)
For i = 1 To iChunks
Get nHandle, , Chunk()
rsImage("Image&quot.AppendChunk Chunk()
Next
rsImage.Update
Close nHandle
Kill sFile

Screen.MousePointer = vbDefault

End Sub

Function LoadImage(ByVal lKey As Long) As String
Dim cnImage As Connection
Dim rsImage As Recordset
Dim sFile As String
Dim nHandle As Integer
Dim Chunk() As Byte
Dim iChunks As Integer
Dim lSize As Long
Dim nFragmentOffset As Integer
Dim oImage As Image
Dim i As Integer

Screen.MousePointer = vbHourglass
Set cnImage = New Connection
cnImage.Open "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=VBTest;UID=sa;PWD=12345"
Set rsImage = New Recordset
rsImage.Open "SELECT * FROM Images WHERE id=" & lKey, cnImage, adOpenForwardOnly, adLockReadOnly

If Not rsImage.EOF Then
nHandle = FreeFile
sFile = App.Path & "\outimage.tmp"
Open sFile For Binary Access Write As nHandle

lSize = rsImage("Image&quot.ActualSize
iChunks = lSize \ conChunkSize
nFragmentOffset = lSize Mod conChunkSize

ReDim Chunk(nFragmentOffset) As Byte
Chunk() = rsImage("Image&quot.GetChunk(nFragmentOffset)
Put nHandle, , Chunk()
ReDim Chunk(conChunkSize) As Byte
For i = 1 To iChunks
Chunk() = rsImage("Image&quot.GetChunk(conChunkSize)
Put nHandle, , Chunk()
Next
Close nHandle
LoadImage = sFile
End If

Screen.MousePointer = vbDefault

End Function

Good luck!
MCSD BW.
BW.

 

[jcote]

You could also use SQL Loader to load your BLOB in the database. There is a good exemple in the utilities book.
I try SQL Loader and it works fine.