LOB AND VISUAL BASIC

[LinaRaj]

Hi,

I want to access pictures stored in oracle database in any
of the following formats. (BLOB, BFILE) through Visual Basic .
Can some body help me ?

Thank you.


LinaRaj

 

[muesli]

have a look at Oracle Objects for OLE: this goves the most comprehensive access to Oracle datatypes from a VB client, and the concepts are similar to Jet's way of handling data.

 

[cbsm]

Hi,
I have a problem accessing CLOBs.
Error message is a data type problem.
How can I select CLOBs through VB.
(Where can I find help about Oracle Object for OLE mentionned by muesli)
Thanks

 

[sunaj]

Hi,

In ADO you use the .GetChunk, .ActualSize and .Appendchuck methods of Recordset.fields(Index)

See

http://msdn.microsoft.com/library/d...s/ado270/htm/mdmthappendchunkx.asp?frame=true

The example uses a sql server database, but except for the connection string it should be the same with Oracle. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[cbsm]

Thanks sunaj ...but ...

I get an error message when I run the select statement :
"Data type is not supported"

Here is my code :
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn1
cmd.CommandText = strSQL
Set rs = New ADODB.Recordset
rs.Open cmd 'error occurs here !!
Set RunSQLReturnRS1 = rs
Set cmd = Nothing
Set rs = Nothing

strSQL is : "select * from table1 where field1='hfkhg'"

If i run the same statement without the CLOB fields it is working fine.

If this can help, that's my connection cnn1 :

Set cnn1 = New ADODB.Connection
strConnectionString = "Provider=MSDAORA.1;User ID=" & strLogin & "; password = " & Password & ";Data Source=test;Persist Security Info=False"
cnn1.Open strConnectionString

Thanks !

 

[sunaj]

Hmm I don't know anything about Oracle...

Try and keep things simple for starters:
-----------------------------------------------
Set cnn1 = New ADODB.Connection
strConnectionString = "Provider=MSDAORA.1;User ID=" & strLogin & "; password = " & Password & ";Data Source=test;Persist Security Info=False"
cnn1.Open strConnectionString
Set Rst = new ADODB.Recordset
Rst.open "SELECT MyBlobField FROM table1 WHERE field1='hfkhg'",conn
------------------------------------------------
Does that work? Does it work if you select another field?


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[cbsm]

I tried ... but still get the same error.

 

[sunaj]

Hmmm.
Take a look at

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q185958&FR=0

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[cbsm]

Thanks again for your help, but it is still not working !

I changed the provider from MSDAORA to {Microsoft ODBC for Oracle}, the CursorLocation to adUseClient ... I tried severa combination of cursor & lock types I can't see what I am doing wrong ...
This time the error message is "unspecified error" - source provider.

(By the way I am using Oracle 8i with VB6.
The Oracle datatype is CLOB.)

 

[cbsm]

I finaly managed to make it work !!!!

Here is the secret ...
I had to use the dbms_lob package in my query :

strSQL is : "select
dbms_lob.substr(clobfield1,dbms_lob.getlength(clobfield1),1) as clobfield1
from table1 where field1='hfkhg'"

Thanks again again for your help,sunaj !