Re: Pro*C - extra space after variables

A copy of this was sent to Kenneth C Stahl <BlueSax_at_Unforgettable.com> (if that email address didn't require changing) On Thu, 09 Dec 1999 11:00:33 -0500, you wrote:

>Thomas Kyte wrote:
>>
>> why? how is that largish amount of code better then:
>>
>> int get_date(void)
>> {
>> static varchar today[8];
>>
>> exec sql whenever sqlerror goto sqlerr;
>> exec sql whenever not_found goto sqlerr;
>> exec sql select to_char(sysdate,'yyyymmdd') into :today from dual;
>>
>> fprintf(stdout,"Today: %.*s",today.len,today.arr);
>>
>> return 0;
>> sqlerr:
>> return 1;
>> }
>
>In the code above, there is automatically two trips to the database even
>though only one row is expected. You absolutely cannot control this when
>you use implicit cursors because it has no way of knowing that only one row
>will ever be returned and therefore it must make the second trip to the
>database in order to determine if it needs to indicate a "too many rows"
>error.

perfect -- it is DUAL -- if it has 2 rows *i want to know*. lots of other stuff is broken. It would be an error if there is more then 1 row and you would be surprised (or not) to find it happens frequently. I love select into for this reason -- if there is supposed to be at least one and at most 1 row returned -- you are doing yourself NO favors by ignoring the error of more then 1 row or less then 1 row.

Also, in Oracle8i, release 8.1 - pro*c auto "prefetches" for you. The above does one round trip to the database as the pre-fetch count defaults to 2 but you can set it to whatever you like at precompile time (you get array fetching for free). The open, fetch, close all take 1 call (as opposed to doing the separate open, fetch, closes your self). You'll find the select into more performant.

>
>Also, are you absolutely sure that you want to use the static storage class
>when an automatic would be sufficient? I am always suspicious of 'static'
>within a function since it normally indicates some type of kluge.
>

Absolutely -- did it on purpose, meant to do it, bad programming practice to not do it as static as you did.

[Quoted] You had it as a global in your sample so yes (borrowing from your example), absolutely I want it static so it does not conflict with other peoples variables. Your code had:

>>It may seem like a lot of code, but in the long run it really isn't all
>>that bad. Here is how I would do it:
>>
>>const int open = 1;
>>const int close = 0;
>>
>>varchar today[8];
>>
>>int open_cur(int op)
>>{
>> exec sql declare cursor c_sysdate as
>> select to_char(sysdate,'YYY

which is *very very* bad as OPEN and CLOSE are C runtime library functions and you just declared duplicate GLOBAL symbols. At least by using static -- I've precluded me from stomping on someone elses variables.

Just recently someone sent me a largish pro*c app. I boiled it down to this:

#include <string.h>

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

static void sqlerror_hard()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\nORACLE error detected:");
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);     EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR stat[5];
EXEC SQL END DECLARE SECTION; main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;
static char * USERID = "tkyte/tkyte_at_ora8i.us.oracle.com";

    strcpy( oracleid.arr, USERID );
    oracleid.len = strlen( oracleid.arr );     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);     EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

So, why do I run this on unix and get:

$ ./t
Illegal Instruction

It is because they declare stat as a variable (global) not static, and stat() is a c runtime function that pro*c calls (to see if a file exists). Without static - your global variable is very *dangerous*. Pro*c tried to execute my 5 byte array as code.

>I've said it before and I'll keep saying it - simple code is not
>necessarily better code. Precise control over what a program does
>(providing it is written properly) will never get you into trouble whereas
>shortcuts and arcane practices are invitations to problems.

I'll stand by mine thanks -- simple is better in my opinion most all of the time. I can write some really obscure code myself but I'd rather have it be understandable, small, straightforward.

Your code is large. It will be harder to maintain.

It ignored errors. For example, if the select from dual returns 0 records, you continue on as if nothing out of the ordinary happened and would print out "Today:" and nothing else. the program would never know it was broke. that is definitely an error.

It is definitely harder to understand that all that work was just to "select sysdate from dual". Using a function "open_cur" to open and close the cursor is either a bad naming convention or bad practice.

-- 
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