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

The second method as described appears to use "dynamic SQL" techniques. You can do it this way, but there may be some performance drawbacks. If subsequent SQL statements do not have the same character signature, it will get reparsed/prepared by Oracle. The use of bind variables is recommended. This _could_ be accomplished using a generic "execute" function, but would require variable length argument list processing, with hints for argument data types.

Handling of generic select statements through dynamic SQL tends to be complicated, but sometimes required for generic programs, i.e. within SQL*Plus. For most mainstream work, I would shy away from it.

For some other products (i.e. Informix), the explicit use/reuse of prepared statements speeds things up. I don't think this is as much the case with Oracle Pro*C.

Peter

Clint Eastwood 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 ;
> int G_PAY_PRD_NR;
> int G_REC_TYP;
> int G_EMP_NR;
> varchar G_ADTNL_DATA_FLD1[8];
> varchar G_ADTNL_DATA_FLD2[8];
> varchar G_FREE_TEXT[60];
> EXEC SQL END DECLARE SECTION ;
>
> and to declare a cursor:
>
> EXEC SQL DECLARE SUPER_LINE CURSOR FOR
> select PAY_PRD_NR,
> REC_TYP,
> EMP_NR,
> EMP_IDN,
> EMP_SRNM,
> EMP_INTLS,
> SLRY,
> LCTN,
> EMP_CNTRB,
> PRE_TAX_CNTRB,
> EMP_VLNTR_CNTRB,
> CMPY_CNTRB,
> EXPT_CD,
> ADTNL_DATA_FLD1,
> ADTNL_DATA_FLD2,
> FREE_TEXT
> from FN_SUPER_DTL
> where PAY_PRD_NR = :G_PAY_PRD_NR
> order by REC_TYP;
>
> then we could directly use this in the code like:
> EXEC SQL FETCH SUPER_LINE into
> :G_PAY_PRD_NR,
> :G_REC_TYP,
> :G_EMP_NR,
> :G_EMP_IDN,
> :G_EMP_SRNM,
> :G_EMP_INTLS,
> :G_SLRY,
> :G_LCTN,
> :G_EMP_CNTRB,
> :G_PRE_TAX_CNTRB,
> :G_EMP_VLNTR_CNTRB,
> :G_CMPY_CNTRB,
> :G_EXPT_CD,
> :G_ADTNL_DATA_FLD1,
> :G_ADTNL_DATA_FLD2,
> :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:
>
> 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))
>
> 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
Received on Fri Dec 03 1999 - 13:10:38 CET