2005. 1. 14. 09:00ㆍ일 이야기
Doc ID: Note:126125.1
Subject: ADO Streaming BLOB & CLOB Example Using ODBC and OLEDB in VB (SCR 1388)
Type: SAMPLE CODE
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 01-DEC-2000
Last Revision Date: 09-DEC-2002
Abstract
The following Visual Basic code takes a binary file and text file and uses the ADO stream object to store and retrieve data from an Oracle database using both Oracle's ODBC Driver and OLE DB Provider.
Product Name, Product Version Oracle ODBC Driver, versions 8.1.7, 9.0, and 9.2
Oracle Provider for OLE DB, versions 8.1.7, 9.0, and 9.2
Platform Windows 95, 98, NT, 2000, and XP Professional
Date Created 01-DEC-2000
Instructions
Execution Environment:
Visual Basic 6.0
SQL*Plus
Access Privileges:
Requires access to the EMP table in the SCOTT demo schema.
Usage:
Run the sample from within the Visual Basic development environment.
Instructions:
1. Open a new Standard EXE project in Visual Basic.
2. Choose Project | References... and check the following:
* Microsoft ActiveX Data Objects 2.6 (or 2.7) Library
* OraOLEDB 1.0 Type Library
3. On an empty VB form add four command buttons with the following properties:
CommandButton1
Name = cmdSaveBLOBToDB
Caption = 'Store BLOB in DB'
CommandButton2
Name = cmdSaveBLOBToFile
Caption = 'Save BLOB to File'
CommandButton3
Name = cmdSaveCLOBToDB
Caption = 'Store CLOB in DB'
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 .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 OLE DB.
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.
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
Description
Prerequisites:
* Microsoft Visual Basic 6.0 service pack 5
* Microsoft Data Access Components (MDAC) version 2.6 or 2.7
* Oracle client software version 8.1.7, 9.0, or 9.2
* Oracle ODBC Driver version 8.1.7, 9.0, or 9.2
* Oracle Provider for OLE DB version 8.1.7, 9.0, or 9.2
References
This sample was taken from Sample Code Repository (SCR) Entry 1388.
Sample Code
'
' ********************************
' *** 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 LOB_TABLE in the db
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
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 LOB_TABLE in the db
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
Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE DONE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.
ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.
Limitation of Liability
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.
SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.