Error When Selecting Data from Blob

hello everybody

i am trying to insert an image into oracle database but i am getting an unspecified error.here is my code fot the same

VB Code:

'Public Function SaveImage()
'
'Dim PictBmp As String
'Dim ByteData() As Byte
'Dim SourceFile As Integer
'
'PictBmp = "C:\z123456.jpg"
'
'With cn
'    .Provider = "MSDASQL.1"
'    .cnectionString = "Password=test;Persist Security Info=True;User ID=test;Data Source=test123"
'    .Open
'End With
'
'
'
''With rs
''    .Activecnection = cn
''    .CursorType = adOpenKeyset
''    .LockType = adLockOptimistic
''    .Source = "SELECT IMG FROM CAMERA_IMG WHERE SRNO = 1"
''    .Open
''End With
'
'SourceFile = FreeFile
'Open PictBmp For Binary Access Read As SourceFile
'FileLength = LOF(SourceFile)
'Dim FileMgr As New FileManager
'
'If FileLength = 0 Then
'    Close SourceFile
'
'Else
'    Numblocks = FileLength / BlockSize
'    LeftOver = FileLength Mod BlockSize
'
'    ReDim ByteData(LeftOver)
'     Get SourceFile, , ByteData()
'     rs(1).AppendChunk ByteData()
'
'     ReDim ByteData(BlockSize)
'     For i = 1 To Numblocks
'         Get SourceFile, , ByteData()
'         rs(1).AppendChunk ByteData()
'     Next i
'
'     rs.Update   'Commit the new data.
'
'    Close SourceFile
'End If
'
'
'End Function

i am getting the unspecified error(-2147467259) on the Select statement.I have oracle 9i as my database and thre is one BLOB field called IMG in the table.I am using ADO 2.6 library.

parth

Check the cn.Errors collection as well.
I think they will be more descriptive.

Why is all your code commented out? (and not in VBCode tags so we can read it easily?)


Anyway.. you can't use a BLOB field in the same way as other fields, for an example see the thread How can I store images (or other files) in a database? from the Database Development FAQ.

i tried cn.errors but it did not work out.i have read somewhere that ADO cannot insert data into BLOB but only in LONG RAW data type.Is this true??anyways here is my code without comments.

Public Function SaveImage()

Dim PictBmp As String
Dim ByteData() As Byte
Dim SourceFile As Integer

PictBmp = "C:\z123456_2006-03-14.jpg"

With cn
.Provider = "MSDASQL.1"
.ConnectionString = "Password=test;Persist Security Info=True;User ID=test;Data Source=test123"
.Open
End With


strSQL = "INSERT INTO CAMERA_IMG(SRNO,IMG) " & _
"VALUES (1,EMPTY_BLOB())"

With cn
.BeginTrans
.Execute strSQL
.CommitTrans
End With

strSQL = "SELECT SRNO,IMG FROM CAMERA_IMG WHERE SRNO = 1"
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open strSQL, cn
MsgBox cn.Errors

SourceFile = FreeFile
Open PictBmp For Binary Access Read As SourceFile
FileLength = LOF(SourceFile)

If FileLength = 0 Then
Close SourceFile

Else
Numblocks = FileLength / BlockSize
LeftOver = FileLength Mod BlockSize

ReDim ByteData(LeftOver)
Get SourceFile, , ByteData()
rs(1).AppendChunk ByteData()

ReDim ByteData(BlockSize)
For i = 1 To Numblocks
Get SourceFile, , ByteData()
rs(1).AppendChunk ByteData()
Next i

rs.Update 'Commit the new data.

Close SourceFile
End If


End Function

For BLOB try using the AddNew method of the recordset object instead of "Insert Into..."

I'm afraid I have no idea what you mean about cn.Errors.

One thing tho, why are you using "EMPTY_BLOB()" in your SQL, rather than "Null"?

Also, why does the transaction only cover the Insert Into, as opposed to the entire routine? (transactions are designed to encompass a whole process, and are irrelevant for a single statement).

If ADO generated the error, and that is very likely, the Errors collection of the connection object must contain at least one error.

If you try something like this in the immediate window, when the error occurs, you can get the error description ADO received from the database:

VB Code:

?cn.Errors(0).Description

You can try index 1 as well, if there are more errors.