Re: OCI: Trailing spaces of strings cut off via input bind

"Ronald" <devnull_at_ronr.nl> schrieb im Newsbeitrag news:67ce88e7.0202120342.672f7871_at_posting.google.com... > "Andr?Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<3c68b6b9$1_at_MAIL.mhogaming.com>...
> > "André Hartmann" <andrehartmann_at_hotmail.com> schrieb im Newsbeitrag
> > news:a48vcm$bi3$1_at_crusher.de.colt.net...
> > > Hi there,
> > >
> > > I have the following strange behaviour when I try to insert
CHR-values
> > > into a NOT NULL chr column:
> > >
> > > * created table with
> > >
> > > create table abc (test char not null)
> > >
> > > ... works fine.
> > > * inserted something with:
> > >
> > > insert into abc values(' ')
> > >
> > > ... works fine.
> > >
> > > * then executed
> > >
> > > insert into abc values(?)
> > >
> > > .. and bind ? to ' ':
> > >
> > > char* pValue = new char;
> > > *pValue = ' ';
> > >
> > > swdReturnCode = OCIBindByPos (
> > > (OCIStmt*) pOCIStatement,
> > > (OCIBind**) &pBindHandle,
> > > (OCIError*) pConnection->getOCIError(),
> > > (ub4) 1,
> > > (dvoid*) pValue,
> > > (sb4) (pValue == NULL) ? 0 : 1,
> > > (ub2) SQLT_CHR,
> > > (dvoid*) NULL,
> > > (ub2*) NULL,
> > > (ub2*) NULL,
> > > (ub4) NULL,
> > > (ub4*) NULL,
> > > (ub4) OCI_DEFAULT
> > > );
> > >
> > >
> > > ... now when I OCIExecute, i get:
> > >
> > > ORA-01400: cannot insert NULL into ("<user>"."ABC"."TEST")
> > >
> > > .. why is this ??? why is my ' ' converted to an empty string which
equals
> > > NULL ?????? Any help ? Is there some magic removing blanks in OCI ????
> > >
> > > Andre
> > > :(
> > >
> > >
> > >

> >
> > ... after further testing, let me re-formulate the problem. When I
insert a
> > CHAR or VARCHAR value into a column with an input bind variable through
OCI,
> > trailing spaces (blanks, ASCII 32) are being cut off. leading spaces are
not
> > cut off, training line breaks, tabs and so on are not cut off as well.
> > Please help me, this is nasty.

> >
>
> André,
> I don't use OCI but prefer to use pro*c whenever possible. How about
> setting the length of the string you try to insert ? In pro*c there
> are several possibilities to set the datatype concerning strings use 5
> for null terminated strings or 9 for varchar2.
>
> hope it helps,
> Ronald
> -----------------------
> http://ronr.nl/unix-dba

YES !!!!   thanks for the hint. I dont really have an option as to what interface to use. But instead of SQLT_CHR i am now using SQLT_STR to bind which stands for a NULL terminated string... and this works finw, i get my spaces ! As a lesson one can only keep in mind to avoid SQLT_CHR then !!?!!

André
:) Received on Tue Feb 12 2002 - 06:43:40 CST