Re: Does Pro*C++ do the same stuff as OCI???

From: Jeffery Cann <jcann_at_fairway.com>
Date: 1998/02/02
Message-ID: <34D65991.19FF4319_at_fairway.com>

dave_at_digidem.com wrote:
>
> I've been architecting a data access approach for a large systems
> development project. We are executing read-only stored procedures on our
> Oracle server and the architecutre behind it abstracts the lower level
> Oracle "stuff". So basically the programmers pass in an input and output
> structure and loop to fetch all the rows in the result set.
>
> So far i've been assuming that the architecture functions that will be
> written will use OCI to interact with Oracle. Today i found out that we
> have a standard around here that says we need to use Pro* C. For some
> reason i think OCI is probably better in this case, but i need to
> articulate this.
>
> What are the main reasons for using OCI over Pro*C? I will be managing
> multiple persistent connections in the application, can i do that with
> ProC*. Are there any perfomance issues? Is there a different approach in
> ProC* for retrieving multple results into a cursor (from within a stored
> procedure) that OCI?
>
> Any thoughs??
>
> Thanks in advance!
>
> -david m rosner
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

I use PRO*C for 2 reasons. First, the application I inherited at my current job
was written using the PRO*C precompiler. At this point, there hasn't been a
justifiable reason to conver it to OCI calls. Second, it is simpler to let the
precompiler write the database executable calls because OCI takes some practice
and if your programmers have not worked with embedded SQL, they may find it easier
to use PRO*C if they only know how to write SQL statements.

In addition, maintenance of ESQL statements could be performed by programmers that
don't know how to code in C. This is because ESQL syntax is exactly the same for
select statements, and other standard SQL statements.

Some people suggest that you use PRO*C to keep your application portable between
databases. Thus, if you write ansi-compliant Embedded SQL, porting to another
database would be no problem.

Others suggest that the precompile step (required by Pro*C) takes too long if you
have a really big application. I must say that for my application that contains
70,000 lines of C code, the precompile step is definitely the slowest.

As far as persistent, my application is an IVR--interactive voice response. For
each phone line that the IVR can use, there is a separate application process. Thus,
my application is synchronous. At this point, we have clients running up to 48 phone
lines on a modest pentium 133 MHz server (64 MB RAM). Each process maintains a persistent
connection with the database. At this point, performance issues are taken care of
by new hardware, so there seems to be no problem using Pro*C for this type of application.

When you say multiple results, I suppose you mean multiple rows? Typically, I have used
loop processing to handle multiple rows. For example, in Pro*C

---

EXEC SQL OPEN cursor_name;

EXEC WHENEVER NOT FOUND DO break;

while (TRUE)
{

	EXEC SQL FETCH cursor_name
		INTO :local_variables;

	{ do something with the data }

}

EXEC CLOSE cursor_name;

---

This is the same section of code after Pro*c Compilation:

         /* EXEC SQL OPEN cursor_name; */  {

  sqlstm.stmt = sq0001;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )2;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)0;
  sqlstm.sqhstv[0] = (unsigned char  *)&s_req_id;
  sqlstm.sqhstl[0] = (unsigned int  )4;
  sqlstm.sqindv[0] = (         short *)0;
  sqlstm.sqharm[0] = (unsigned int  )0;
  sqlstm.sqphsv = sqlstm.sqhstv;
  sqlstm.sqphsl = sqlstm.sqhstl;
  sqlstm.sqpind = sqlstm.sqindv;
  sqlstm.sqparm = sqlstm.sqharm;
  sqlstm.sqparc = sqlstm.sqharc;

  sqlcex(&sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode < 0) goto sql_err;
 }

while (TRUE)
{
 {

	/* EXEC SQL FETCH cursor_name
		INTO :local_variables; */

   sqlstm.iters = (unsigned int  )1;
   sqlstm.offset = (unsigned int  )20;
   sqlstm.cud = sqlcud0;
   sqlstm.sqlest = (unsigned char  *)&sqlca;
   sqlstm.sqlety = (unsigned short)0;
   sqlstm.sqhstv[0] = (unsigned char  *)&s_priority;
   sqlstm.sqhstl[0] = (unsigned int  )4;
   sqlstm.sqindv[0] = (         short *)&indicator1;
   sqlstm.sqharm[0] = (unsigned int  )0;
   sqlstm.sqhstv[1] = (unsigned char  *)&s_sel_flag;
   sqlstm.sqhstl[1] = (unsigned int  )3;
   sqlstm.sqindv[1] = (         short *)&indicator2;
   sqlstm.sqharm[1] = (unsigned int  )0;
   sqlstm.sqhstv[2] = (unsigned char  *)&s_pm_id;
   sqlstm.sqhstl[2] = (unsigned int  )4;
   sqlstm.sqindv[2] = (         short *)&indicator3;
   sqlstm.sqharm[2] = (unsigned int  )0;
   sqlstm.sqphsv = sqlstm.sqhstv;
   sqlstm.sqphsl = sqlstm.sqhstl;
   sqlstm.sqpind = sqlstm.sqindv;
   sqlstm.sqparm = sqlstm.sqharm;
   sqlstm.sqparc = sqlstm.sqharc;

   sqlstm.sqparm = sqlstm.sqharm;
   sqlstm.sqparc = sqlstm.sqharc;
   sqlcex(&sqlctx, &sqlstm, &sqlfpn);
   if (sqlca.sqlcode == 1403) break;
   if (sqlca.sqlcode < 0) goto sql_err;
 }

        /* do something with fetched data */

}

/* EXEC SQL CLOSE cursor_name */

{

   sqlstm.iters = (unsigned int  )1;
   sqlstm.offset = (unsigned int  )86;
   sqlstm.cud = sqlcud0;
   sqlstm.sqlest = (unsigned char  *)&sqlca;
   sqlstm.sqlety = (unsigned short)0;

   sqlcex(&sqlctx, &sqlstm, &sqlfpn);
   if (sqlca.sqlcode < 0) goto sql_err;
}

---

Finally, others have suggested that because OCI is a 'native' interface, it will
be much faster in terms of execution time. This probably is true because the Pro*C
compiler generates more executable lines to open, fetch, and close a cursor than
would be written in the OCI calls. (see above code)

Cheers.

-- 
Jeffery Cann
Senior Software Engineer
Fairway Systems, Inc.
jcann_at_fairway.com

Received on Mon Feb 02 1998 - 00:00:00 CET