Re: Pro*C bind variables to Insert Blanks
Date: 1997/04/10
Message-ID: <5ij7jq$d5o_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <860634738.13397_at_dejanews.com>, John W. Bito <jwb_at_megasoft.com> writes:
|> I have encountered a behavior that I don't expect using Pro*C dynamic
|> embedded SQL. When I bind a string buffer to a variable in the VALUES
|> clause of
|> INSERT INTO x VALUES (:var, :othervar)
|> where the table looks like
|> CREATE TABLE x (col1 CHAR(15), col2 CHAR(15))
|> I populate the sqlda like this
|>
|> char var[15], othervar[15];
|> memset(var, ' ', 15);
|> memset(othervar, ' ', 15);
|>
|> sqlda.V[0] = &var;
|> sqlda.T[0] = 1 /* CHAR */
|> sqlda.L[0] = 15;
|> sqlda.I[0] = NULL;
|> sqlda.V[1] = &othervar;
|> sqlda.T[1] = 1 /* CHAR */
|> sqlda.L[1] = 15;
|> sqlda.I[1] = NULL;
You are using external datatype 1, VARCHAR2. All trailing blanks will be stripped [Quoted] from variables of this type before inserting them into the database. In your case, all the blanks are stripped, resulting in a zero length string, which is inserted as NULL. Try using datatype 96, CHAR, instead.
The Pro*C manual documents this behavior in the chapter on datatypes (which I think is Chapter 3).
|>
|> I want to have a row with two columns of fifteen blanks. Instead, I get a
|> row with two null columns. This is a trivial example of a problem that is
|> rather painfull in that I have many columns that have <blank> (spaces) as
|> a valid, non-nul value. I know I can get Oracle to insert these rows
|> using
|> INSERT INTO x VALUES (' ', ' ')
|> but this is impractical, since all my embedded SQL would have to be coded
|> N times, where N is the number of colums that are allowed to be <blank>.
|>
|> Thanks for your assistance,
|> John Bito
|> DataFocus, Inc.
|> jwb_at_DataFocus.com
|>
|> -------------------==== Posted via Deja News ====-----------------------
|> http://www.dejanews.com/ Search, Read, Post to Usenet
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Thu Apr 10 1997 - 00:00:00 CEST