Re: Pro*C, '\0' and LONG columns

A copy of this was sent to jgr_at_cix.compulink.co.uk ("John Ratcliffe") (if that email address didn't require changing) On Wed, 19 May 1999 11:46:01 GMT, you wrote:

>I would like to have a Pro*C routine that inserts and fetches text in and
>out of a LONG column. The text can be longer than the 32k limit on PL/SQL.
>In order to interface with other external systems the requirements are
>that this text must contain embedded ASCII null characters ('\0' in C).
>
>Does anyone know of a way of using Pro*C to do this?
>
>John Ratcliffe
>
>jgr_at_cix.compulink.co.uk
>
>
>BACKGROUND:
>
>Pro*C generates code that uses the C str* functions like strlen(), which
>doesn't help.
>

no, pro*c doesn't insert str* function calls into the code. it packs a structure with pointers and calls internal routines.

I don't think i've ever seen pro*c inject str* calls into code.

>Pro*C data types are built around the idea of C style strings, so any data
>type I tried did not help. I have tried VARCHARS, STRINGS, CHARZ but all

varchar is not. a varchar structure is explicitly *not* null terminated. It has a .len field and a .arr field.

>expect/use C style strings. Insertion meant that the string was inserted
>up to the first null character then space filled up to the specified
>length.
>
>I have tried altering the CHAR_MAP and DBMS pre compiler settings.
>
>I tried coercing data types for example, VARCHAR -> VARCHAR2, but that
>didn't help. It needs more code, and I found that I was getting some
>strange side effects, and life is too short.
>
>I know PL/SQL can do this, so it is not an Oracle restriction. However, I
>can't use PL/SQL because I want to handle strings > 32k.

Consider the following example:

SQL> create table t ( x long );

Table created.

SQL> desc t;

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        LONG

with the pro*c code:

static void process()
{
exec sql begin declare section;
varchar my_string[100000];
exec sql end declare section;
int i;

#define TEXT "How Now \0 Brown Cow"
#define TEXT_LEN 20

    memmove( my_string.arr, TEXT, 20 );
    my_string.len = TEXT_LEN;

    exec sql whenever sqlerror do sqlerror_hard();     exec sql insert into t values ( :my_string );     exec sql commit work;

    memset( my_string.arr, 0, sizeof(my_string.arr) );     my_string.len = 0;

    exec sql select x into :my_string from t;

    printf( "my_string.len = %d\n", my_string.len );

    for( i = 0; i < my_string.len; i++ )

        printf( "--> %3d, %c\n", my_string.arr[i], my_string.arr[i] ); }

the output from that is:

my_string.len = 20

-->   72, H
-->  111, o
-->  119, w
-->   32,  
-->   78, N
-->  111, o
-->  119, w
-->   32,  
-->    0, 
-->   32,  
-->   66, B
-->  114, r
-->  111, o
-->  119, w
-->  110, n
-->   32,  
-->   67, C
-->  111, o
-->  119, w
-->    0, 

After I run that, i use the plsql block

SQL> declare

  2      y    long;
  3      z    varchar2(5000);
  4  begin
  5      select * into y from t;
  6      select dump(y) into z from dual;
  7  
  7      dbms_output.put_line( z );
  8      dbms_output.put_line( length(y) );
  9  
  9      for i in 1 .. length(y)
 10      loop
 11          dbms_output.put_line( '--> ' ||
 12                                to_char(ascii(substr(y,i,1)),'999') ||
 13                                ' ' ||
 14                                substr(y,i,1) );
 15      end loop;

to verify my work independent of C and get:

Typ=1 Len=20:
72,111,119,32,78,111,119,32,0,32,66,114,111,119,110,32,67,111,119,0 20

-->   72 H
-->  111 o
-->  119 w
-->   32
-->   78 N
-->  111 o
-->  119 w
-->   32
-->    0
-->   32
-->   66 B
-->  114 r
-->  111 o
-->  119 w
-->  110 n
-->   32
-->   67 C
-->  111 o
-->  119 w
-->    0

which shows that both the embedded and trailing \0 was inserted for me. Both 'dump' and a manual dump show that.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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