I have a C++ program that inserts data into a table with columns defined as
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:
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
Is this a bug in the 8.0.5 OCI Library and has anyone encountered simialar
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 de* off the shelf right about now).
Why does this work and have I bypassed something important?
Any comments appreciated.