Calling Oracle stored procedure from VC++ 
Author Message
 Calling Oracle stored procedure from VC++

Hello,

I'm using VC++ 6.0 with MFC ODBC classes to access data in Oracle 8.1.6.

The problem is calling stored procedures in Oracle that returns data. From
what I have seen on previous postings, Oracle stored procedures can't return
recordsets (like stored procedures in SQL Server can), but for now I'd be
happy to just be able to get some return parameters back to VC++.

The MSDN article "Recordset: Declaring a Class for a Predefined Query
(ODBC)" gives an example on how to build the recordset with the needed
parameters, but the article says nothing if this is meant to work with
Oracle, or if it is SQL Server only. Also, there are no examples on how the
stored procedure would look.

An example: If I have the Oracle stored procedure (PL/SQL code):

CREATE OR REPLACE PROCEDURE sp_get_zonename
  (v_zone_id   IN zone.z_id%TYPE,
   v_zone_name OUT zone."ZoneName"%TYPE)
IS
BEGIN
   SELECT "ZoneName"
   INTO v_zone_name
   FROM zone
   WHERE Z_ID = v_zone_id;
END;
/

how can I call this procedure from VC++ and get the variable v_zone_name
back?

In advance, thanks for your help.

Regards,

Knut
--

  PTI Scandpower AS
  P.O.Box 206 / Billingstadsletta 30
  1396 Billingstad, Norway
  Phone +47 66 85 03 21



Tue, 17 Aug 2004 23:06:28 GMT  
 Calling Oracle stored procedure from VC++
Knut,

Oracle can return a recordset - it must be declared as ref cursor in the
stored procedure, and you must set the PLSQLRecset Property of the Command
Object to TRUE (I am assuming that you would use the Command object with
Parameters to pass to the SP)..  Have a look at the Oracle OLEDB Client
documentation - it should be on the PC when/where Oracle client is
installed.

Reply back here if you don't have it - and I can give you some sample code.

best regards,
Roy Fine


Quote:
> Hello,

> I'm using VC++ 6.0 with MFC ODBC classes to access data in Oracle 8.1.6.

> The problem is calling stored procedures in Oracle that returns data. From
> what I have seen on previous postings, Oracle stored procedures can't
return
> recordsets (like stored procedures in SQL Server can), but for now I'd be
> happy to just be able to get some return parameters back to VC++.

> The MSDN article "Recordset: Declaring a Class for a Predefined Query
> (ODBC)" gives an example on how to build the recordset with the needed
> parameters, but the article says nothing if this is meant to work with
> Oracle, or if it is SQL Server only. Also, there are no examples on how
the
> stored procedure would look.

> An example: If I have the Oracle stored procedure (PL/SQL code):

> CREATE OR REPLACE PROCEDURE sp_get_zonename
>   (v_zone_id   IN zone.z_id%TYPE,
>    v_zone_name OUT zone."ZoneName"%TYPE)
> IS
> BEGIN
>    SELECT "ZoneName"
>    INTO v_zone_name
>    FROM zone
>    WHERE Z_ID = v_zone_id;
> END;
> /

> how can I call this procedure from VC++ and get the variable v_zone_name
> back?

> In advance, thanks for your help.

> Regards,

> Knut
> --

>   PTI Scandpower AS
>   P.O.Box 206 / Billingstadsletta 30
>   1396 Billingstad, Norway
>   Phone +47 66 85 03 21



Wed, 18 Aug 2004 04:23:53 GMT  
 Calling Oracle stored procedure from VC++
The following link might be helpful:

http://osi.oracle.com/~tkyte/ResultSets/index.html

HTH Dieter



Quote:
> Hello,

> I'm using VC++ 6.0 with MFC ODBC classes to access data in Oracle 8.1.6.

> The problem is calling stored procedures in Oracle that returns data. From
> what I have seen on previous postings, Oracle stored procedures can't
return
> recordsets (like stored procedures in SQL Server can), but for now I'd be
> happy to just be able to get some return parameters back to VC++.

> The MSDN article "Recordset: Declaring a Class for a Predefined Query
> (ODBC)" gives an example on how to build the recordset with the needed
> parameters, but the article says nothing if this is meant to work with
> Oracle, or if it is SQL Server only. Also, there are no examples on how
the
> stored procedure would look.

> An example: If I have the Oracle stored procedure (PL/SQL code):

> CREATE OR REPLACE PROCEDURE sp_get_zonename
>   (v_zone_id   IN zone.z_id%TYPE,
>    v_zone_name OUT zone."ZoneName"%TYPE)
> IS
> BEGIN
>    SELECT "ZoneName"
>    INTO v_zone_name
>    FROM zone
>    WHERE Z_ID = v_zone_id;
> END;
> /

> how can I call this procedure from VC++ and get the variable v_zone_name
> back?

> In advance, thanks for your help.

> Regards,

> Knut
> --

>   PTI Scandpower AS
>   P.O.Box 206 / Billingstadsletta 30
>   1396 Billingstad, Norway
>   Phone +47 66 85 03 21



Fri, 20 Aug 2004 06:19:29 GMT  
 Calling Oracle stored procedure from VC++
Hello,

Thank you very much to both of you for your replies. (Roy: Thanks, but I'm
using MFC ODBC, not OLE DB, so I don't think I have a PLSQLRecset Property
available.)

Anyway, I have been testing the approach as described in the document
http://www.*-*-*.com/ ~tkyte/ResultSets/index.html, and found that very
helpful. Now it works, I can call a stored procedure and have a recordset
returned. Example code, VC++ side:

 m_pDBOb{*filter*}3->m_BlockIDParam = 1441;
 CString strSQL = CString("{CALL reftest2.sp_GetPermHor (?)}");
    try {
       if (m_pDBOb{*filter*}3->Open(CRecordset::forwardOnly, strSQL,
CRecordset::readOnly))

          while (!m_pDBOb{*filter*}3->IsEOF()) {
             double dPermHor = m_pDBOb{*filter*}3->m_PermHor;
             m_pDBOb{*filter*}3->MoveNext();
           }
          m_pDBOb{*filter*}3->Close();
      }
    } catch( CDBException* e )

       AfxMessageBox( e->m_strError, MB_ICONEXCLAMATION );
       e->Delete();
    }

class CDBOb{*filter*}3 : public CRecordset

The PL/SQL side is very similar to what's described in the document. So the
document is absolutely correct. Thanks!

However, a few questions still remains:

1. Say I have a stored procedure that just returns a single value. What's
the best way to get this value back to VC++? I could always use a recordset
like in the above example, but I was wondering is there is an even simpler
way? Any pointers to relevant examples?

2. I would expect stored procedure to give a performance benefit. However,
when I compare the above example to code where I just pass the SQL string
along with the recordset (meaning the SQL-code must be parsed on the server
side), I find stored procedures to be up to three times slower than not
using them. True, the SQL-code in the example I'm using is very simple, and
only involves one table:

 CString strSQL4 = CString("SELECT \"PermHor\" from Ob{*filter*} ");
    m_pDBOb{*filter*}3->m_strFilter = "\"SelectPerm\" = 1 AND \"BorehID\" > 0 AND
\"BlockID\" = ?";
    m_pDBOb{*filter*}3->m_BlockIDParam = 1441;

And also true, we have a slow database server. But still, has anyone of you
seen the performance benefit stored procedures should give with this
approach? Can I expect the difference to be in favor of stored procedures on
more complex SQL?

The reason for asking: I currently don't have any stored procedures in the
application, but would like to test if rewriting parts of the application
using stored procedures can increase performance.

In advance, thanks for your help.

Regards,

Knut
--

  PTI Scandpower AS
  P.O.Box 206 / Billingstadsletta 30
  1396 Billingstad, Norway
  Phone +47 66 85 03 21


Quote:
> The following link might be helpful:

> http://www.*-*-*.com/ ~tkyte/ResultSets/index.html

> HTH Dieter



Sun, 22 Aug 2004 00:17:44 GMT  
 
 [ 4 post ] 

 Relevant Pages 

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

2. Oracle/C++ stored procedure call

3. How to call a stored procedure of Oracle in MFC

4. Calling Oracle stored procedures using MFC's classes

5. Help with VC++ to Oracle using Stored Procedures

6. ADO stored procedures from VC++ using ORACLE

7. calling a stored procedure from VC++

8. How do I call a stored procedure in VC++

9. Newbie: Looking for simplest code to call a sybase stored procedure from VC++

10. How to call stored procedure from VC++?

11. calling stored procedures in VC

12. Calling Stored Procedure in VC++

 

 
Powered by phpBB® Forum Software