OCI string bound to VARCHAR2 - FK constraint failure

OCI string bound to VARCHAR2 - FK constraint failure

Post by Brian Harmo » Sun, 31 Dec 1899 09:00:00



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 de* off the shelf right about now).

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

Any comments appreciated.

Thanks
BH

 
 
 

OCI string bound to VARCHAR2 - FK constraint failure

Post by Jan-Marten Spi » Sun, 31 Dec 1899 09:00:00


Have you tried using indicator variables?

This because the Oracle error you get simply means that the data that you
insert does not exist in the parent table. Try using indicator variables,
and use them to specify that you are inserting data, not nulls.

Specify the indicator variable(s) in OCIBindByPos and optionally in
OCIBindArrayOfStruct (in the case you are doing an array insert). Initialize
the indicator varibles to 0, indicating that you are inserting values. If
the indicator is set to -1, you tell the OCI to insert o NULL, regardless of
the value you assign to the program variable.

By the way, there ARE some bugs in 805, i moved to 815. Cant remember this
one however, and i used the same method as you did. But i used indicator
variables.

Hope this helps.

Jan-Marten Spit

 
 
 

1. OCI array of strings bind

Hi,
    I'm attempting to bind an array of strings of variable length.

text *textval = "Entry1Entry2"
ub2 alen[2];
alen[0] = 6;
alen[1] = 6;

status = OCIBindByPos (stmtp, &bindpp, errpp, 1, textval, 6,
    SQL_CHR, inds, alen, 0, 0, 0, OCI_DEFAULT);

This works fine, however when I try to insert two strings of different
lengths things go awry.

Is it possible bind an array of variable length strings, or do they have
to fixed length padded with spaces?

Thanks S.

2. Portal Finds

3. OCI: Binding collections (varray), Ora 1008: not all variables bound

4. Anyone able to run the CACHEDB Demo ?

5. FK constraints between 2 dbs

6. PICK PROGRAMMERS - Multiple positions - Mult Locations - Recruiter

7. FK Constraints in DTS

8. data loss due to improper handling of postmaster ....

9. 2 fk constraint in 1 field

10. how to do inserts with circular fk constraints?

11. Recreate FK Constraints using Transact SQL

12. FK constraints

13. Identity Number Lost on failed Insert w/ FK Constraint in SQL7