Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: OCIBind and whitespace trimming

Re: OCIBind and whitespace trimming

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 17 Feb 2004 22:54:27 GMT
Message-ID: <c0u633$1cgar4$1@ID-82536.news.uni-berlin.de>

> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<c0sic1$1b5jo4$1_at_ID-82536.news.uni-berlin.de>...

>> > Hi everyone. I've got a customer that is using some older code we
>> > wrote to update some tables (code based on OCI from 8.1.7). One
>> > particular table has a VARCHAR2 field and he wants to put the
>> > following text into it: 'this is my test '. Note the space at the
>> > end. Our little helper function uses OCIBindByPos when building the
>> > Update clause and this, it turns out, causes the trailing spaces to
>> > get stripped:
>> >
>> > "http://sales.esicom.com/sales/oracle/appdev.816/a76975/oci03typ.htm
>> >
>> > VARCHAR2:
>> > The value_sz parameter determines the length in the OCIBindByName() or
>> > OCIBindByPos() call. If the value_sz parameter is greater than zero,
>> > Oracle obtains the bind variable value by reading exactly that many
>> > bytes, starting at the buffer address in your program. Trailing blanks
>> > are stripped, and the resulting value is used in the SQL statement or
>> > PL/SQL block. "
>> >
>> > The customer doesn't want to have to build the update statement with
>> > actual data in it (and he shouldn't have to) but I've been unable to
>> > find a workaround besides that. I'm about as far from being an Oracle
>> > whiz as you can get and yet still use OCI so I'm really hoping someone
>> > out here might have a suggestion.
>>
>>
>> Sean,
>>
>> I am a little confused. Basically, as it at least seems to me, you are
>> stating the answer: value_sz must be adjusted. In your case, it must
>> be 16. Use strlen('this is my test ') or any similar function to
>> determine the size.
>>
>> hth
>> Rene
> 
> values_sz is set correct Rene.  What happens is that it reads the 16
> bytes THEN strips any spaces which results in in 'this is my test'.  I
> haven't had any luck finding a workaround yet except to not use bound
> parameters (ie update table x set column y = 'this is my test '..).

What datatype did you state in the OCIBindByPos function call?

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch
Received on Tue Feb 17 2004 - 16:54:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US