Re: PRO*C FETCH, is this expected behavior?

In article <1992Mar2.225320.18922_at_aio.jsc.nasa.gov> hall_at_orion.jsc.nasa.gov (Philip Hall 283-4031) writes:
>In article <gsrhpqbtssmith_at_netcom.com>, tssmith_at_netcom.com (Tim Smith) writes:
>
>|>
>|> I missed the original posting that Philip is responding to, but be
>|> aware that in the version 1.4 (available very soon) Pro*C and
>|> Pro*Pascal precompilers, you have a new command EXEC SQL TYPE ...
>|> This allows you to set a defined type to correspond to any ORACLE
>|> external datatype, such as STRING (datatype code 5). So in Pro*C you
>|> could do:
>|>
>|> typedef char asciz;
>|>
>|> EXEC SQL BEGIN DECLARE SECTION;
>|> /* User-defined type for null-terminated strings */
>|> EXEC SQL TYPE asciz IS STRING(20);
>|> asciz emp_name[20];
>|> ...
>|> EXEC SQL END DECLARE SECTION;
>|>
>|> and then do
>|>
>|> EXEC SQL SELECT ename INTO :emp_name FROM emp WHERE empno = 7499;
>|>
>|> and get a null-terminated C string in the emp_name host variable.
>|>
>|> There is also an EXEC SQL VAR ... command that let's you do external
>|> type equivalencing on a variable-by-variable basis. The VAR command is
>|> also available for the COBOL, FORTRAN, and PL/I precompilers.
>|>
>|>
>|> --Tim (tssmith_at_netcom.com)
>|> (tssmith_at_oracle.com)
>
> I'm not clear on exactly what emp_name is after those two declarations.
> Is it a single character array or an array of twenty strings?

It's a single character array of twenty elements, just as it would be in a straight C program. The TYPE ... STRING(20) host variable "typedef" just lets the DB kernel know that the output to the variable should be null-terminated, or that the input from it must have a null terminator character, and that the maximum length is 19+'\0' characters. If you SELECT a 26 character name into emp_name, all you get is 19 characters plus a null terminator (and a truncation error, in V6, if you didn't use an indicator variable along with the output host variable).

The TYPE and VAR commands can also be used to "coerce" output/input away from the default external datatypes. For example, if you do:

typedef char date_type;

EXEC SQL BEGIN DECLARE SECTION;
    EXEC SQL TYPE date_type IS DATE;
    date_type my_date[7];
EXEC SQL END DECLARE SECTION;
    ...
    EXEC SQL SELECT sysdate INTO :my_date FROM dual;

you will get Oracle's internal representation of the current date and time in your host variable, rather than a character string like "02-MAR-92". (The internal representation is binary in 7 bytes, representing century, year, month, day, hour, minute, second. You can see the DATE internal format using SQL*Plus if you do something like

SELECT DUMP(sysdate) FROM dual; )

Previously, you could only do things like this using the much more complicated full dynamic SQL with SQLDA (descriptor struct)--what Oracle calls "Dynamic SQL Method 4."

In the ...STRING(20) "typedef", 20 is the maximum length of the string. With DATE, unlike STRING, you don't need to specify a length parameter, since it must be seven. STRING(20) says the maximum length is 20, but with DATE the DB knows that the length must be seven.

> Is this part of the proposed ANSI SQL standard?

It's not part of the current ANSI SQL standard (89 version). I'm not sure whether it's in SQL2 (don't have my copy with me right now). But it _is_ a useful feature, particularly for C programmers.

> Philip Hall
 

--Tim Smith (tssmith_at_netcom.com)

            (tssmith_at_oracle.com) Received on Tue Mar 03 1992 - 08:11:44 CET