Working with BLOBs with ADO (VB)

Connectivity
zac
Fan

Working with BLOBs with ADO (VB)

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
3 REPLIES
Teradata Employee

Re: Working with BLOBs with ADO (VB)


Correct your connection string to below.

Driver=Teradata;DBCName=SERVER;Uid=USER;Pwd=PASS;Extended Properties=""USENATIVELOBSUPPORT=Yes;"";")
zac
Fan

Re: Working with BLOBs with ADO (VB)

Thanks Vhari! That was way easier than I was expecting :-P
Enthusiast

Re: Working with BLOBs with ADO (VB)

Hi everyone,

I want to use ADO to connect to Terradata Server. I have Teradata.Net and ODBC installed.

I have been using ODBC and SQLA but now I need to be able to work with VB 6.

Machine Data Source = Data Source Name

User DSN = Data Source Name "Driver Teradata"

Teradata  Server Info = IP Address

Username=Known

Password=Masked

System DSN is same as User DSN.

Drivers = Teradata 13.10.00.06  TDATA32.DLL 5/25/2011

--------------------------------------------------------------------------

If I use this connection string

Driver=Teradata;DBCName=SERVER;Uid=USER;Pwd=PASS;Extended Properties=""USENATIVELOBSUPPORT=Yes;"";")

OR 

CONNECT TO TERADATA

    cn.Open "Data Source=WML; Database=WMLAD; Persist Security Info=True; User ID=******; Password=******; Session Mode=ANSI;"

Set cmdSQLData.ActiveConnection = cn

How do I use the connection string properly?

I do not have password that I can type in my VBA Syntax, so could I use Password = ******* or does it really matter?

Thanks for help

MBS