Re: VARCHAR behaviour

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/11/25
Message-ID: <365b4851.1608823_at_192.86.155.100>#1/1


A copy of this was sent to Programming <sysdev_at_mb.sympatico.ca> (if that email address didn't require changing) On Tue, 24 Nov 1998 15:03:10 -0600, you wrote:

>I have a couple of questions regarding the use of Oracle 7.2 VARCHAR
>host variables in a C program on AIX/RS6000.
>
>1. In a cursor fetch, I must use varchar and not char in my C program.
> Char works sometimes, but sometimes contains garbage values. Why is
>this?
>

post examples please...

for example, this below shows that the C char type will be blank padded and null terminated. In this example, the strlen(c_ename) is always 29 and the 30'th byte is a null terminator.

The varchar on the other hand is NOT null terminated, only the .len and .arr fields are set and we can only use .len number of bytes..

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
char c_ename[30];
VARCHAR vc_ename[30];
EXEC SQL END DECLARE SECTION;     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL DECLARE C CURSOR FOR SELECT ENAME, ENAME FROM EMP;     EXEC SQL OPEN C;
    for( ;; )
    {

        EXEC SQL WHENEVER NOT FOUND DO break;
        EXEC SQL FETCH C INTO :c_ename, vc_ename;

        printf( "'%s', '%.*s'\n", c_ename, vc_ename.len, vc_ename.arr );
    }

    EXEC SQL CLOSE C;
    EXEC SQL COMMIT WORK;
}

'SMITH                        ', 'SMITH'
'ALLEN                        ', 'ALLEN'
'WARD                         ', 'WARD'
'JONES                        ', 'JONES'

>2. I used strcpy as follows: STRCPY((CHAR *)VAR1.ARR,(CHAR *)VAR2.ARR);
> VAR2 was cursor fetched from Oracle, was null terminated, and has
>it's .LEN set by Oracle. VAR2 had it's .LEN set by me explicitally. In a
>subsequent STRCPY identical to the one above, or sometimes just for use
>in a "WHERE X=:VAR1" clause, I found that must explicitally set the
>.LEN again, even though it's value has not changed since the last set!
>The Oracle error I got was ORA - 01458.
>

you say "..., was null terminated, and ...". Did you expect Oracle to null terminate it (because it does not) and you might be doing an overrun on the strcpy -- nuking storage elsewhere. A safe copy might be:

   strncpy( var1.arr, var2.arr, var2.len );    var1.len = var2.len;

you could automate this with a macro like:

#define vstrcpy( a, b ) strncpy( a.arr, b.arr, b.len ), a.len = b.len

If you are getting a 1458 and believe the value hasn't changed but the simple act of setting the length again (to the same value) fixes it -- that means something, somewhere changed the length -- it sounds like a memory overrun somewhere.

>Any help would be greatly appreciated. Thanx.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.
Received on Wed Nov 25 1998 - 00:00:00 CET

Original text of this message