Re: Pro*C issues - some of the low level things

A copy of this was sent to cjundieseastwd_at_powerup.com.au (Clint Eastwood) (if that email address didn't require changing) On Fri, 03 Dec 1999 06:00:52 GMT, you wrote:

>HiYa
>
>a few questions
>
>I have worked a little in the past with Pro*C however at that site we
>had a lot of functional things wrapped already, in that role there
>wasn't much development of totally new things, and so I could get by by
>just understanding C and using the code examples that were already
>there. To do things we only had to make calls with code like:
>
>EXEC SQL BEGIN DECLARE SECTION ;
[snip]
>EXEC SQL END DECLARE SECTION ;
>
>and to declare a cursor:
>
>EXEC SQL DECLARE SUPER_LINE CURSOR FOR
> select PAY_PRD_NR,

[snip]
> order by REC_TYP;
>
>then we could directly use this in the code like:
> EXEC SQL FETCH SUPER_LINE into
[snip]

> :G_FREE_TEXT;
>
>is this the normal way that people use this?? I thought that it was
>until I started in this position, where I find that we are using
>straight C to insert a query into a string, then calling a function that
>is in another module like this:
>

yes.

> sprintf(sql_statement, "INSERT INTO System.Accounts VALUES('%s',%s')",
> account_code, user_group);
>
> if ((error_code = oracle_execute()) != 0)
> {
> retries = 0;
> while ((retries < MAX_SQL_INSERT_RETRY) &&
> (error_code != 0) &&
> (strncmp(sqlca.sqlerrm.sqlerrmc,
> "ORA-00001: unique constraint (SYSTEM.SYS_C00370)
>violated\n",
> sqlca.sqlerrm.sqlerrml) == 0))
>
>
>

that whole example is terrible, frightening actually. i shudder as I read it.

Shared SQL is just completely *not* used. No bind variables. You'll find that 90% PLUS of your apps runtime in the database is spent parsing the insert statements -- not executing them. If they would like a little performance, they would use bind variables. (hopefully they are not really using SYSTEM either -- thats just an example right...)

the above technique is a recipe for disaster on any sizable system. you'll find cpu going through the roof (parsing) and concurrency going down the tubes (lots of shared pool work/latching/locking to parse).

doing a strcmp on an error message is a *bad* *bad* *bad* idea. error messages can and will change from release to release. the above example is *particularly* bad because it is refering to a system generated constraint name (SYS_C00370). If you ever rebuild your app -- that constraint name *will change*....

>
>All of this seems like we are doing things the hard way here, but
>not having been up on what was done to set things up at the last place I
>am feeling a little like a flag in a strong wind.
>
>I feel that things can be done differently, I just don't know how to set
>these things up.
>
>help??
>
>Is there a good reference that can help me fill in the holes that are in
>my knowledge??
>
>
>
>thanks
>
>
>
>See Ya
>(when the bandwidth gets better ;-)
>Chris Eastwood Please remove undies for reply
>Photographer, Stunt Programmer
>Motorcyclist and dingbat

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation