OCI string bound to VARCHAR2 - FK constraint failure

I have a C++ program that inserts data into a table with columns defined as type VARCHAR2.

i have an array of zero terminated strings that are a always 2 characters in length. These values can be up to 3 characters in length. I have allocated 4 bytes for each and initialized the array with zerofills prior to populating it with data.

assume that tableA is defined as follows:

column1 VARCHAR2(10),
column2 VARCHAR2(3),
etc..

INSERT INTO tableA (column1, column2, etc..)

                         values ( :value1, :value2, etc..) ;

In my OCIBindByPosition call for position 2, I have specified SQLT_STR as the data type code specification.
The length parameter is passed as 4 (FIELDWIDTH + 1).

This works without a problem until I add a foreign key constraint to tableA (column2), referencing tableB

where tableB's primary key is defined as VARCHAR2(3).

with the addition of this foreign key constraint the bound INSERT OCIStmtExecute call fails . The error message is "FK_TABLE_COLUMN2 constraint violated - Key not found in parent table"

on inspection.... all values that I am trying to insert are valid values based on the primary key values populated in tableB.

The values being read are from a DBASE file (field length is 3) so I am assuming that it is possible that I am inserting a space appended to the end of the 2 characters for the typical value read from this field, however;

If I change the BindByPositon typecode indicator to OCI_TYPECODE_VARCHAR2, The INSERT statement fails to execute because the length of the data bound is less than the required 2 byte
length for VARCHAR2. This is the indication from the error message returned, but on the contrary my values
are always exactly 2 bytes long (or are they 3). In any case I have to abandon the idea that my values are possibly 3 characters long and therefore failing a comparison against VARCHAR2 values that are 2 characters in length.

Question #1
Is this a bug in the 8.0.5 OCI Library and has anyone encountered simialar anomalies?

Finally, I was able to resolve the error conditions by changing the typecode indicator in the BindByPosition call
to OCI_TYPECODE_VARCHAR and reducing the data length parameter by 1 (now 3) which eliminates the null
zero from the length (or does it ... the 3rd byte may be a zero if it is not a space... Im feeling the urge to pull
the debugger off the shelf right about now).

Question #2
Why does this work and have I bypassed something important?

Any comments appreciated.

Thanks
BH Received on Sat Jan 15 2000 - 05:02:35 CST