From: tssmith@netcom.com (Tim Smith)
Newsgroups: comp.databases.oracle
Subject: Re: PRO*C FETCH, is this expected behavior?
Message-ID: <nzsh!b-tssmith@netcom.com>
Date: 3 Mar 92 07:11:44 GMT
References: <1992Mar1.162231.9265@aio.jsc.nasa.gov> <gsrhpqbtssmith@netcom.com> <1992Mar2.225320.18922@aio.jsc.nasa.gov>
Organization: Netcom - Online Communication Services  (408 241-9760 guest)
Lines: 91


In article <1992Mar2.225320.18922@aio.jsc.nasa.gov> hall@orion.jsc.nasa.gov (Philip Hall 283-4031) writes:
>In article <gsrhpqbtssmith@netcom.com>, tssmith@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@netcom.com)
>|>       (tssmith@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@netcom.com)
            (tssmith@oracle.com)