connectivity between V B and oracle using LARGE OBJECTS

Hi! FELLAS....
I AM WORKING ON A PROJECT NAMED DESEASE ANALYSIS.
WHILE CONNECTING V B TO ORACLE THE DATA TYPE SPECIFIED IS CLOB(CHARACTER LARGE OBJECT) WHICH IS NOT BEING SUPPORTED BY V B. THE CONNECTION IS BEING COMPILED BY ADODB. PLEASE SOME ONE HELP ME OUT FROM THIS PROBLEM












[B][U]

Part1:
Oracle has a site called metalink.oracle.com, you need to sign up for it but
it is free. It is nowhere as nice as msdn site but still it is usefull. I
searched on oledb clob. Here is an example from there:

Doc ID: Note:126125.1
Subject: Example: ADO Streaming BLOB & CLOB Example using ODBC
and OLEDB in VB (SCR 1388)
Type: SCRIPT
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 01-DEC-2000
Last Revision Date: 07-MAR-2002

Overview
--------

The following VB code takes a binary file and a text file and uses the ADO
Stream object to store and retrieve data from an Oracle database using both
Oracle's ODBC Driver and OLEDB Provider.

Program Notes
-------------

o The sample has been tested using Microsoft Visual Studio 6.0 SP5
running
on Windows 2000 with 8.1.7 Oracle Software.

o It was tested against Microsoft Data Access Components (MDAC) 2.6.

o Complete the following steps to setup the sample:

1. Open a new Standard EXE project in Visual Basic.

2. Choose Project | References... and check the following:

a. Microsoft ActiveX Data Objects 2.6 Library
b. OraOLEDB 1.0 Type Library

3. On an empty VB form add four command buttons with the following
properties:

a. CommandButton1
Name = cmdSaveBLOBToDB
Caption = 'Store BLOB in DB'
b. CommandButton2
Name = cmdSaveBLOBToFile
Caption = 'Save BLOB to File'
c. CommandButton3
Name = cmdSaveCLOBToDB
Caption = 'Store CLOB in DB'
d. CommandButton4
Name = cmdSaveCLOBToFile
Caption = 'Save CLOB to File'

4. View the code of the VB form and make sure it is blank. You should
be in the object = (General) and procedure = (Declarations) section
of
the code window. Paste the entire sample code below into VB's code
window.

5. Set the BlobFileName constant to the name of a binary file such as
a jpg, bmp, tif, etc and put this file in the 'C:\' folder.

6. Set the ClobFileName constant to the name of a text file and put
this
file into the 'C:\' folder.

7. Set the value of the ODBCConnectString and the OLEDBConnectString
constants to valid parameters for connecting to your database.

8. Uncomment the ConnectivityMethod constant to designate the method
for
accessing the Oracle database: ODBC or OLEDB.

9. Create a table to hold the LOBs by executing the following SQL*Plus
statement:

create table LOB_TABLE (
FIELD VARCHAR2(50),
BLOBFIELD BLOB,
CLOBFIELD CLOB);

10. You are now ready to run the sample code. The LOBs will be
retrieved
and stored into the 'C:\Temp' folder on your computer.

Caution
-------

The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.

Program
-------

- - - - - - - - - - - - - - - - Code begins
here - - - - - - - - - - - - - - - -

'
' ********************************
' *** Streaming LOB Examples ***
' ********************************
' *** Oracle Worldwide Support ***
' *** Created 10 Aug 2000 ***
' ********************************
'
' ADOStreamLOBs
'
' The following code contains examples of inserting and retrieving BLOBs and
' CLOBs with VB using the ADO Object Model with Stream objects.
'

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mStream As ADODB.Stream
Dim ConnStr As String

' Set up you ODBC and OLE DB connect strings here
Const ODBCConnectString = "DSN=oracleu;UID=scott;PWD=tiger;"
Const OLEDBConnectString = "Data Source=geoff;User ID=scott;Password=tiger;"

' Set up the name and locations of the source and destination paths of the
BLOB file
Const BlobFileName = "blobtest.jpg"
Const BlobSourcePath = "C:\"
Const BlobDestPath = "C:\Temp\"

' Set up the name and locations of the source and destination paths of the
CLOB file
Const ClobFileName = "clobtest.txt"
Const ClobSourcePath = "C:\"
Const ClobDestPath = "C:\Temp\"

' Uncomment the connectivity method you wish to use
Const ConnectivityMethod = "ODBC"
'Const ConnectivityMethod = "OLEDB"

Private Sub cmdSaveCLOBToFile_Click()

' ******************************************************************
' *** This event will take a CLOB that has already been inserted ***
' *** into the database and save it as a file. ***
' ******************************************************************

On Error GoTo ErrorHandler

Screen.MousePointer = vbHourglass

Set cn = New ADODB.Connection

If ConnectivityMethod = "ODBC" Then
' ODBC Connection Setup

Set cn = New ADODB.Connection
With cn
.ConnectionString = ODBCConnectString
.Open
End With

Else
' OLEDB Provider Connection Setup

cn.Provider = "OraOLEDB.Oracle"
cn.ConnectionString = OLEDBConnectString
cn.Open

End If

Set rs = New ADODB.Recordset

' 'clobtest.txt' is the name of a TEXT file that has been stored in the
LOB_TABLE in the database
rs.Open "Select * from LOB_TABLE where FIELD = '" & ClobFileName & "'",
cn, adOpenKeyset, adLockOptimistic

' Create ADO stream object
Set mStream = New ADODB.Stream

' Set the character set for you particular type of text data
mStream.Charset = "us-ascii"

' Set it to a text file type
mStream.Type = adTypeText

' Open it
mStream.Open

' This writes the text file from the clob field to the buffer
mStream.WriteText rs.Fields("CLOBFIELD").Value

' This saves the stream to a file on disk
mStream.SaveToFile ClobDestPath & rs.Fields("FIELD").Value

mStream.Close
Set mStream = Nothing

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

Screen.MousePointer = vbDefault

MsgBox "Clob saved to file as " & ClobDestPath & ClobFileName, , "Clob
Saved"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

Select Case Err.Number
Case 3004
MsgBox "Could not write file.", , "File Already Exists"
Case Else
MsgBox Err.Number & " - " & Err.Description, , "Error Msg"
End Select

End Sub

Private Sub cmdSaveCLOBToDB_Click()

' *******************************************************************
' *** This event will insert a CLOB from a file into the database ***
' *******************************************************************

On Error GoTo ErrorHandler

Screen.MousePointer = vbHourglass

Set cn = New ADODB.Connection

If ConnectivityMethod = "ODBC" Then
' ODBC Connection Setup

Set cn = New ADODB.Connection
With cn
.ConnectionString = ODBCConnectString
.Open
End With

Else
' OLEDB Provider Connection Setup

cn.Provider = "OraOLEDB.Oracle"
cn.ConnectionString = OLEDBConnectString
cn.Open

End If

Set rs = New ADODB.Recordset

rs.Open "Select * from LOB_TABLE", cn, adOpenKeyset, adLockOptimistic

' Create the ADO Stream object
Set mStream = New ADODB.Stream

' Set the character set for you particular type of text data
mStream.Charset = "us-ascii"

' Make it a text type
mStream.Type = adTypeText

' Open the stream
mStream.Open

' Read the text file into the stream buffer
mStream.LoadFromFile ClobSourcePath & ClobFileName

' Add the blob to the database
With rs
.AddNew
.Fields("CLOBFIELD").Value = mStream.ReadText
.Fields("FIELD").Value = ClobFileName
.Update
End With

mStream.Close
Set mStream = Nothing

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

Screen.MousePointer = vbDefault

MsgBox "Clob inserted into DB from " & ClobSourcePath & ClobFileName, ,
"Clob Inserted"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

Select Case Err.Number
Case 3002
MsgBox "Could not read file, check the path.", , "File Not Found"
Case Else
MsgBox Err.Number & " - " & Err.Description, , "Error Msg"
End Select

End Sub

Private Sub cmdSaveBLOBToDB_Click()

' *******************************************************************
' *** This event will insert a BLOB from a file into the database ***
' *******************************************************************

On Error GoTo ErrorHandler

Screen.MousePointer = vbHourglass

Set cn = New ADODB.Connection

If ConnectivityMethod = "ODBC" Then
' ODBC Connection Setup

Set cn = New ADODB.Connection
With cn
.ConnectionString = ODBCConnectString
.Open
End With

Else
' OLEDB Provider Connection Setup

cn.Provider = "OraOLEDB.Oracle"
cn.ConnectionString = OLEDBConnectString
cn.Open

End If

Set rs = New ADODB.Recordset

rs.Open "Select * from LOB_TABLE", cn, adOpenKeyset, adLockOptimistic

' Create the ADO Stream object
Set mStream = New ADODB.Stream

' Make it a binary type
mStream.Type = adTypeBinary

' Open the stream
mStream.Open

' Read the binary file into the stream buffer
mStream.LoadFromFile BlobSourcePath & BlobFileName

' Add the blob to the database
With rs
.AddNew
.Fields("BLOBFIELD").Value = mStream.Read
.Fields("FIELD").Value = BlobFileName
.Update
End With

mStream.Close
Set mStream = Nothing

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

Screen.MousePointer = vbDefault

MsgBox "Blob inserted into DB from " & BlobSourcePath & BlobFileName, ,
"Blob Inserted"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

Select Case Err.Number
Case 3002
MsgBox "Could not read file, check the path.", , "File Not Found"
Case Else
MsgBox Err.Number & " - " & Err.Description, , "Error Msg"
End Select

End Sub

Part2:

Private Sub cmdSaveBLOBToFile_Click()

' ******************************************************************
' *** This event will take a BLOB that has already been inserted ***
' *** into the database and save it as a file. ***
' ******************************************************************

On Error GoTo ErrorHandler

Screen.MousePointer = vbHourglass

Set cn = New ADODB.Connection

If ConnectivityMethod = "ODBC" Then
' ODBC Connection Setup

Set cn = New ADODB.Connection
With cn
.ConnectionString = ODBCConnectString
.Open
End With

Else
' OLEDB Provider Connection Setup

cn.Provider = "OraOLEDB.Oracle"
cn.ConnectionString = OLEDBConnectString
cn.Open

End If

Set rs = New ADODB.Recordset

' 'blobtest.txt' is the name of a BINARY file that has been stored in the
LOB_TABLE in the database
rs.Open "Select * from LOB_TABLE where FIELD = '" & BlobFileName & "'",
cn, adOpenKeyset, adLockOptimistic

' Create ADO stream object
Set mStream = New ADODB.Stream

' Set it to a binary file type
mStream.Type = adTypeBinary

' Open it
mStream.Open

' This writes the image from the blob field to the buffer
mStream.Write rs.Fields("BLOBFIELD").Value

' This saves the stream to a file on disk
mStream.SaveToFile BlobDestPath & rs.Fields("FIELD").Value

mStream.Close
Set mStream = Nothing

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

Screen.MousePointer = vbDefault

MsgBox "Blob saved to file as " & BlobDestPath & BlobFileName, , "Blob
Saved"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

Select Case Err.Number
Case 3004
MsgBox "Could not write file.", , "File Already Exists"
Case Else
MsgBox Err.Number & " - " & Err.Description, , "Error Msg"
End Select

End Sub

- - - - - - - - - - - - - - - - Code ends
here - - - - - - - - - - - - - - - -

Sample Output
-------------

Message boxes confirming the upload and retrieval of the LOBs will be
displayed and any LOB's retrieved from the Oracle database will be stored
into the 'C:\Temp' folder on your computer.


It is come from http://www.dotnet247.com/247referen.../21/108842.aspx