Passing LOB Parameters From And To A Stored Procedure


         Contents of this document:

         Overview of the sample
         Required Software
         Assumptions
         Description of  files in the sample
         Prepare and Run the sample
         Steps in the sample

 Overview of the Sample

         This sample shows how to pass LOB parameters to  and from  Oracle stored procedure using Oracle Provider for OLEDB and ActiveX Data
         Objects (ADO) with Visual CSharp Beta2 version .  It also demonstrates that Oracle provider for OLEDB bypasses PL/SQL limitation of
         parameter size greater than 32K  to a  database  stored  procedure.
         With OraOLEDB, for passing the LOB variables as parameter to a SQL statement or stored procedure, the property 'SPPrmsLOB' is to set to
         true to tell the provider that a LOB parameter is being passed .

        The property can be set as  ADO command object property just before executing the procedure. This way the extra network trip is performed for
        executionof that command only. So it is a better practice to set the  property to 'True' just before execution of the procedure  and then setting it to
        'False'.

        The sample shows how to set the 'SPPrmsLOB' property with command object.

        Note : The maximum size of image that can be passed as  OUT parameter to stored procedure with OraOLEDB is 64K. Images greater than this
                   size may not be  retrieved properly from database.
 

 Required Software

        Oracle 8x database .
        Visual CSharp.Net Beta 2 version
        Oracle Provider for OLEDB Release 8.1.7
 

 Assumptions

        1. A database user e.g.. Oraole with password Oraole has already been created.
        2. The tables with data used by the sample have already been created using the SQL scripts. Referdb8setup.html for more information about
            setting up the database tables and populating data.
 

 Description of  files in the sample

 

Sr No

File Name

Type

File Description
1  ReadMe.html HTML File ReadMe File for the application
2 ADODBHandler.cs CSharp class file Declares and defines the class for handling database functions.
3 StartForm.cs CSharp class file This file contains the definition of class for the form used by application..
4 AssemblyInfo.cs CSharp class file Generated by compiler, this file contains assembly information for project.
5 ConnectionParams.h C++ header file This file contains connection parameters to connect to particular database. 
6 StoredProcedure.sql SQL script file This file contains the sql script to create the  stored procedures  required by the  sample.
7 StartForm.resx Resource file Resource template used by StartForm form
8 licenses.licx licx file Used by resource file
9 LOBSample.csproj CSharp  Project file CSharp Project file
10 LOBSample.sln CSharp solution  file CSharp Solution  file
11 imageG30.jpg Image file Used by ReadMe.html
12 imageHHP.jpg Image file Used by ReadMe.html
13 imageJ3S.jpg Image file Used by ReadMe.html

 

 Prepare and Run the Sample

      1.  Extract the Sample files. Unzip CSharpLOBSample.zip using Winzip or command utility

         2.  Connect to Oracle Sql*plus using <username>/<password>@<connectstring> .
              Here <username> refers to the user in which all the tables have been created and <password> refers to the password given for the above user
              <connectstring> refers to the  hostname.

        3.  Make sure you have created the database tables required by this sample. Refer db8setup.html for more information about setting up the
             database tables and populating data.

        4.  Create Database Stored Procedure by running StoredProcedure.sql script.
               SQL> @<path>StoredProcedure.sql

        5.  Open LOBSample.sln using VCSharp.Net Beta2..

        6.  Edit ConnectionParams.csfile to change the database connection parameters i.e. database name, user name and password
             to database you want to use . Save the file.

        7.  Build and Run the sample.
 

 Steps in the sample

        1.  Select product from the list. Its corresponding image should appear in the Existing Image frame.

                                                                                                                            fig 1

         2.  To select another image for update, click on "Browse " button, a File Open Common Dialog appears.

      3.  Once the image from the File Open Common Dialog Box is selected it appears in the new image frame.

        4. "Update Image" button can be pressed to update the product image in the database

Please enter your comments about this sample in the OTN Sample code discussion forum.