Hello,

I'm trying to work with BLOB objects using VBA in Excel. I can successfully insert files, but am unable to retrieve them.

To Insert: (works)
[font=Courier New]Dim adCon As ADODB.Connection
Dim adCmd As ADODB.Command
Dim sSQL As String
Dim adPar1 As ADODB.Parameter
Dim adPar2 As ADODB.Parameter
Dim adStream As ADODB.Stream
Dim vFilename

adoConnection.Open ("Driver=Teradata;DBCName=SERVER;Uid=USER;Pwd=PASS; Extended Properties=""EnableLOBSupport=Yes;"";")

vFilename = Application.GetOpenFilename("All files (*.*),*.*", 1, "BLOB Test", , False)
If vFilename = False Then Exit Function

Set adStream = New ADODB.Stream
adStream.Open
adStream.Type = adTypeBinary
adStream.LoadFromFile vFilename

sSQL = "insert into db_container.table_name(id, file) values (?, ?)"

Set adCmd = New ADODB.Command
adCmd.CommandType = adCmdText
adCmd.CommandText = sSQL
adCmd.Prepared = True
adCmd.ActiveConnection = adoConnection

Set adPar1 = adCmd.CreateParameter("id", adSmallInt, adParamInput)
adPar1.Value = 123

Set adPar2 = adCmd.CreateParameter("file", adLongVarBinary, adParamInput, inStream.Size)
adPar2.AppendChunk adStream.Read

adCmd.Parameters.Append adPar1
adCmd.Parameters.Append adPar2
adCmd.Execute[/font]

And here's what I'm trying to retrieve the file back: (unsuccessfully)
[font=Courier New]Dim adRs As ADODB.Recordset
Dim adSt As ADODB.Stream

If Not connectDB Then Exit Function

Set adRs = New ADODB.Recordset
adRs.Open "select file from db_container.table_name", adoConnection, adOpenForwardOnly, adLockReadOnly

Set adSt = New ADODB.Stream
adSt.Type = adTypeBinary
adSt.Open
adSt.Write adRs.Fields("file").Value
adSt.SaveToFile "c:\test.jpg", adSaveCreateOverWrite

adRs.Close
disconnectDB[/font]

It gives me the following error:
Run-time error '-2147467259 (80004005)':
[NCR][ODBC Teradata Driver][Teradata Database] LOBs are not allowed to be selected in Record or Indicator modes.

Which I can't seem to figure out how to get around. I can see the BLOB data using BTEQ but I need to be able to retrieve it using ADO in VBA.

Please let me know if you have any ideas.

Thanks
-Zac