Board index » delphi » Example of connecting C++ Builder to Oracle Stored Procs.

Example of connecting C++ Builder to Oracle Stored Procs.

The following notes describe trials, tribulations and results of using ODBC
and SQL*LINKS
drivers to connect C++ Builder to Oracle Stored Procedures via the BDE.

============================================================================
===============

Version Information.

BDE + BDE ORACLE Native Driver  4.
Microsoft ODBC Driver  2.573.
Oracle ODBC Driver  8.01.05.

C++ Builder   5

============================================================================
=============

OVERVIEW.

Two oracle stored procedures were called, an 'action' procedure which
inserted a table row
and a query procedure which returned a number of rows from a table. Both
were called from
a c++builder app using the bde to connect a tdatabase component to a
tstoredproc.

============================================================================
=============

RESULTS.

1) Where the schema / owner name is included in the call e.g.
TSBNAdminUser.TestCursor.GetVendorDetails
to either procedure all three drivers tested failed in the following ways:

A) Oracle Driver  - Reports 'Missing Identifier'.
B) Microsoft Driver  - Reports 'Operation not applicable'.
C) Native Driver - Reports 'Parameter not found'.
   - Periodically GPFS with 'Access Violation In OraClient8.Dll...'.
2) Where the schema is not used in the call (regardless of upper / lower
case)
e.g. TestCursor.GetVendorDetails the following results were noted:

Microsoft Driver fails for both Action and Query procedure with 'Operation
Not Applicable'.

Native driver works for action procedure but fails for query procedure with
'Wrong No. or
types of parameters'. Native Driver much faster than ODBC drivers.

Oracle Driver - Works for both Action and Query procedure.

============================================================================
=====

SUMMARY.

NOTE. ONLY THE ORACLE DRIVER WORKED FOR BOTH PROCEDURE TYPES.

NOTE. RETURNED CURSORS MUST BE AN INPUT OUTPUT PARAMETER IN THE PROCEDURAL
DEFINITION
      THEY CANNOT BE THE RETURN VALUE.

NOTE. THE SCHEMA OWNER CANNOT BE PART OF THE PROCEDURE NAME CALLED
(REGARDLESS OF CASE).
      THIS MEANS THAT THE BDE BASED APP CAN ONLY CALL PROCEDURES OWNED BY
THE
      LOGGED ON USER - THIS IS A SERIOUS LIMITATION WHICH MAY BE ADDRESSED
BY
      ADO STORED PROCS BASED ON NEWSGROUP READINGS BUT I HAVE YET TO CONFIRM
THIS.

NOTE. TSTOREDPROC->EXECPROC MUST BE USED FOR THE ACTION PROCEDURE AND
TSTOREDPROC->OPEN
      OR SET TSTOREDPROC->ACTIVE FOR THE QUERY PROCEDURE.

============================================================================
=

CALLING CODE IN C++BUILDER.

Action Call.

  try {
    StoredProc1->StoredProcName = "APPERROR.LOG";
    StoredProc1->Active = false;
    StoredProc1->Prepare();
    StoredProc1->ParamByName("PREPENDMESSAGE")->AsString = "Builder
Goodbye";
    StoredProc1->ParamByName("LOGLEVEL")->AsInteger = 0;
    StoredProc1->ExecProc();
  }

Query Call.

  try {
    StoredProc2->StoredProcName = "TESTCURSOR.GETVENDORDETAILS";
    StoredProc2->Active = false;
    StoredProc2->Prepare();
    StoredProc2->ParamByName("VendorDetails")->AsString = 0;
    StoredProc2->Active = true;
  }

Params were defined for the calls as follows:
Action Procedure - 1) PREPENDMESSAGE FTSTRING 2) INPUT LOGLEVEL FTINT INPUT
Query Procedure  - 1) VENDORDETAILS FTCURSOR INPUT / OUTPUT VALUE 0.

=======================================================================

The following Oracle Packaged procedure was used for the action procedure.

CREATE OR REPLACE PACKAGE TSBNAdminUser.AppError
  AUTHID DEFINER IS

  PROCEDURE Log(PrependMessage IN VARCHAR2 :=  NULL, LogLevel IN NUMBER :=
2);

END AppError;
/
SHOW ERRORS;

Implementation body was trivial.

========================================================================

The following Oracle Packaged procedure was used for the query procedure.

SPECIFICATION FOR PACKAGED PROCEDURE USING A CURSOR TO RETURN DATA ROWS.

CREATE OR REPLACE PACKAGE TSBNAdminUser.TestCursor
  AUTHID DEFINER IS

  /*---------------------- PUBLIC DECLARATIONS -----------------------*/

  TYPE tTSBNCURSOR IS REF CURSOR;

  /*
      Returns Vendor Key Details being VendorRightsURL, VendorPLK and
TZNBasePath.
      The Vendor Details are returned as a single row cursor which the
caller should close.
      SiteID defaults to single site - site 1.
  */

  PROCEDURE GetVendorDetails(VendorDetails IN OUT tTSBNCURSOR);

END TestCursor;
/
SHOW ERRORS;

BODY FOR PACKAGED PROCEDURE USING A CURSOR TO RETURN DATA ROWS.

CREATE OR REPLACE PACKAGE BODY TSBNAdminUser.TestCursor IS

  PROCEDURE GetVendorDetails(VendorDetails IN OUT tTSBNCURSOR)
  IS

  --ReturnValue SMALLINT;

  BEGIN

    OPEN VendorDetails FOR
      SELECT  Site.sVendorRightsURL VendorRightsURL,
              Site.sVendorPLK       VendorPLK,
              Site.sTZNBasePath     TZNBasePath,
              Site.sMediaPassXSLURL MediaPassXSLURL
        FROM  TSBNAdminUser.Site
        WHERE Site.SiteID = 1;

    --RETURN ReturnValue;

  END GetVendorDetails;

END TestCursor;
/
SHOW ERRORS;

 

Re:Example of connecting C++ Builder to Oracle Stored Procs.


Quote
Pat Martin wrote:

> NOTE. THE SCHEMA OWNER CANNOT BE PART OF THE PROCEDURE NAME CALLED
> (REGARDLESS OF CASE).
>       THIS MEANS THAT THE BDE BASED APP CAN ONLY CALL PROCEDURES OWNED BY
> THE
>       LOGGED ON USER - THIS IS A SERIOUS LIMITATION WHICH MAY BE ADDRESSED
> BY
>       ADO STORED PROCS BASED ON NEWSGROUP READINGS BUT I HAVE YET TO CONFIRM
> THIS.

Not quite right.  You can create a public synonym for the procedure and then
call it without the owner name;  but maybe this doesn't work with the named
drivers -- I haven't tested it.

John Pierce

Re:Example of connecting C++ Builder to Oracle Stored Procs.


Using a Public Synonym works fine for action procedures but as soon as a
parameter of type REF CURSOR is used this technique fails in the same way as
it does where the synonym is not present i.e. can't find the stored proc.
Params of base types e.g. NUMBER work fine.  Very irritating, still no full
solution.  Interestingly the JDBC Thin Driver has a similar bug with respect
to Cursor Variables not being in scope during execution even where Authid
Definer is declared at the package level.

Pat.

Quote
John Pierce <jpie...@healthplanning.com> wrote in message

news:39C240D1.FAEC14AB@healthplanning.com...
Quote

> Pat Martin wrote:

> > NOTE. THE SCHEMA OWNER CANNOT BE PART OF THE PROCEDURE NAME CALLED
> > (REGARDLESS OF CASE).
> >       THIS MEANS THAT THE BDE BASED APP CAN ONLY CALL PROCEDURES OWNED
BY
> > THE
> >       LOGGED ON USER - THIS IS A SERIOUS LIMITATION WHICH MAY BE
ADDRESSED
> > BY
> >       ADO STORED PROCS BASED ON NEWSGROUP READINGS BUT I HAVE YET TO
CONFIRM
> > THIS.

> Not quite right.  You can create a public synonym for the procedure and
then
> call it without the owner name;  but maybe this doesn't work with the
named
> drivers -- I haven't tested it.

> John Pierce

Other Threads