passing array to stored procedure with oledb 
Author Message
 passing array to stored procedure with oledb

Hi,
We are currently working with a 3 Tired Architecture ,the front end is
delphi 4.0 and Middle ware is VC++ 6.0 and back end is Oracle .
We are facing a problem regarding passing data to stored procedures.

The program requires to pass Array of DATA to Oracle Stored Procedure
and get a modified data in the same array. We are using OLEDB provided
consumer to connect to the Data Base and it builds the corresponding
Consumer class for that stored procedure, the variables of the Stored
procedure that accept data are mapped to IUnknown pointers . As Oracle
doesn't understand VARIANT , Are there any Macros present in OLEDB to
pass Array Variables to the stored procedure.
I'm also attaching the OLEDB generated file for ur reference, could u
suggest a solution ,how to go about with this problem.

Any help would be gratefully received.

Thanks and Regards,
Shankar

------------------------------------- Code from the OLE DB Consumer
----------------

// Country.H : Declaration of the CCountry class

#ifndef __COUNTRY_H_
#define __COUNTRY_H_

class CCountryAccessor
{
public:
        double m_TPID;
        double m_DOMCODE;
        IUnknown* m_COUNTRYNAME;    These are two parameters to be used
for array input.....
        IUnknown* m_COUNTRYFLAG;

BEGIN_PARAM_MAP(CCountryAccessor)
        COLUMN_ENTRY(1, m_TPID)
        COLUMN_ENTRY(2, m_DOMCODE)
        COLUMN_ENTRY(3, m_COUNTRYNAME)
        COLUMN_ENTRY(4, m_COUNTRYFLAG)
END_PARAM_MAP()

DEFINE_COMMAND(CCountryAccessor, _T("{ CALL IFB.MAIN.GET_COUNTRYS
(?,?,?,?) }"))

        // You may wish to call this function if you are inserting a
record and wish to
        // initialize all the fields, if you are not going to explicitly
set all of them.
        void ClearRecord()
        {
                memset(this, 0, sizeof(*this));
        }

Quote:
};

class CCountry : public CCommand<CAccessor<CCountryAccessor> >
{
public:
        HRESULT Open()
        {
                HRESULT         hr;

                hr = OpenDataSource();
                if (FAILED(hr))
                        return hr;

                return OpenRowset();
        }
        HRESULT OpenDataSource()
        {
                HRESULT         hr;
                CDataSource db;
                CDBPropSet      dbinit(DBPROPSET_DBINIT);

                dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("ifb"));
                dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("ifb"));
                dbinit.AddProperty(DBPROP_INIT_DATASOURCE,
OLESTR("ifb"));
                dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
                dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
                hr = db.Open(_T("MSDAORA.1"), &dbinit);
                if (FAILED(hr))
                        return hr;

                return m_session.Open(db);
        }
        HRESULT OpenRowset()
        {
                return CCommand<CAccessor<CCountryAccessor>

Quote:
>::Open(m_session);

        }
        CSession        m_session;

Quote:
};

#endif // __COUNTRY_H_

------------------------------- Stored proceedure -------------------

declare
    country main.charArrayTyp;
    flag main.char1ArrayTyp;
BEGIN
  country(1) := 'ASIA';
  country(2) := 'AMERICA';
  country(3) := 'EUROPE';
  flag(1) := 'G';                                 // Actual Input for
the program
  flag(2) := 'C';
  flag(3) := 'G';
  main.get_countrys(1,1,country,flag);
END;
/

create or replace package main as
    type charArrayTyp is table of varchar2(20)
        index by binary_integer;
    type char1ArrayTyp is table of char(1)
        index by binary_integer;
    procedure get_countrys(
        tp_id   in     integer,
        dom_code in     integer,
        country_name in    charArrayTyp,
        country_flag in    char1ArrayTyp);
end main;
/
create or replace package body main as
    cou_name varchar2(20);
    cursor get_countrys_in_group (param1 varchar2) is
        select b.country_name from country_group a, country b
           where (a.country_group_name = param1) and
(a.country_id=b.country_id);
    procedure get_countrys(
        tp_id   in     integer,
        dom_code in     integer,
        country_name in    charArrayTyp,
        country_flag in    char1ArrayTyp) is
    begin
        for i in 1..3 loop
          if country_flag(i) = 'G' then
            if not get_countrys_in_group%isopen then
            open get_countrys_in_group(country_name(i));
            end if;
          else
            insert into temptable values(cou_name);
          end if;
        fetch get_countrys_in_group into cou_name;
        insert into temptable values(cou_name);
            if get_countrys_in_group%notfound then
                close get_countrys_in_group;
            end if;
        end loop;
       /*for i in 1..3 loop
          insert into tp_domain_Country
          values(tp_id,dom_code,country_name(i),country_flag(i));
        end loop; */
    end get_countrys;
end main;
/



Fri, 19 Jul 2002 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Problems passing parameters to ORACLE stored procedure with ATL/OLEDB

2. how to pass array to oracle stored procedure?

3. Calling stored procedure with OLEDB?

4. how to using OLEDB access the SQL store Procedure

5. Help with VC++ / OLEDB / Stored procedures....

6. Execute stored procedure using OLEDB

7. OleDb and Oracle Stored Procedures

8. Problems calling Oracle stored procedures from VC++ over ADO(OLEDB)

9. Calling stored procedure with OLEDB?

10. Passing NULL values to a stored procedure????

11. ODBC: Passing embedded quotes to stored procedures

12. Passing Record Type from VC ADO to Stored Procedure

 

 
Powered by phpBB® Forum Software