Re: Oracle 8.1 OCI array inserts of strings

A copy of this was sent to Arien Malec <amalec_at_slip.net> (if that email address didn't require changing) On Sat, 21 Aug 1999 10:44:07 -0700, you wrote:

>Thomas --
>
>Thanks for the quick response. This did get me a bit forward: I can now pass the
>array itself, without getting garbage. I still only get the first string in the
>table, though, not the second.
>
>my declaration is now:
>
> char messages[][30] = {"Hello, world","The British are coming" };
> int mlena[] = {strlen(messages[0]), strlen(messages[1])};
>
>I tried sizeof(messages[0]), but the length of the value in the database is then 30
>(the database column is varchar2, and I don't want the string to be padded).
>
>I also tried to call OCIArrayOfStruct with a pvskip of 30, also to no success.
>
>Again, what is happening is:
>
>1) I am also passing an array of ints into an integer column, and that is working
>fine
>2) The first value in the string array above is getting in the database, with
>length of 12 (or 30 if I use sizeof)
>3) The second value is null (I've tried passing an array of 2 indicator variables,
>both 0)
>
>Even more bizarre:
>
>I added a new string to my array:
> char messages[][30] = {"Hello, world", "The British are coming", "Bonjour,
>monde"};
> int mlena[] = {strlen(messages[0]), strlen(messages[1]), strlen(messages[2])};
>
>I also changed the iter count in OCIStmtExecute to 3
>
>The result:
>
>select * from hello;
>
>MESSAGE_ID MESSAGE
>---------- ----------------------------------------
> 1 Hello, world
> 2
> 3 Bonjour, monde
>
>select message_id, length(message) from hello;
>
>MESSAGE_ID LENGTH(MESSAGE)
>---------- ---------------
> 1 12
> 2
> 3 22
>
>22 is the length of "The British are coming", not "Bonjour, monde"
>

thats the Key. the array of lengths are to be sb2's (short binary 2 bytes). You are sending ints. I'll betcha an int is 4 bytes on your platform (most are). the first 2 bytes are OK and used for Hello World. The next 2 are 0 (the other half of the length of Hello World) and thats why the 2'cnd row is null -- zero length string. The third pair of bytes is really the first 2 bytes of the second length and is used for the third string (if you select dump(message) from hello, you'll see trailing NULLS on "bonjour, monde" upto 22 bytes).

make mlena be an array of short. get rid of the CAST's in the Oci call so the compiler can warn you of them in the future.

>in gdb:
>
> print mlena
>$1 = {12, 22, 14}
>(gdb) print messages
>$2 = {"Hello, world", '\000' <repeats 17 times>,
> "The British are coming\000\000\000\000\000\000\000",
> "Bonjour, monde", '\000' <repeats 15 times>}
>
>
>Help!
>
>Thanks,
>Arien
>
>Thomas Kyte wrote:
>
>> A copy of this was sent to Arien Malec <amalec_at_slip.net>
>> (if that email address didn't require changing)
>> On Fri, 20 Aug 1999 21:45:35 -0700, you wrote:
>>
>> >What is the correct way to do array inserts of strings?
>> >
>>
>> you don't have an array of strings below, you have an array of pointers to
>> strings. It'll be more like
>>
>> char messages[2][25] = {"Hello, world","The British are coming" };
>> int mlena[2] = ( sizeof(messages[0]), sizeof(messages[1]) };
>>
>> >What I've been trying to do can be summarized like this:
>> >
>> > char * messages[2] = {"Hello, world","The British are coming" };
>> > int mlena[2] = {strlen(messages[0]), strlen(messages[1])};
>> >
>> > /*...*/
>> >
>> > status = OCIBindByName (stmhp,
>> > (OCIBind **) &bnd2hp,
>> > errhp,
>> > (text *) ":MESSAGE",
>> > strlen (":MESSAGE"),
>> > (ub1 *) *messages,
>> > (sword) 30,
>> > SQLT_CHR,
>> > (dvoid *) 0,
>> > (ub2 *) mlena,
>> > (ub2 *) 0,
>> > (ub4) 0,
>> > (ub4 *) 0,
>> > OCI_DEFAULT);
--
See http://govt.us.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 Received on Sat Aug 21 1999 - 14:10:22 CDT