Char column with no value used as foreign key.
Posted: Mon 15 Oct 2012 18:28
Hi, please I need some advice with this strange issue:
I have a table which contains certificates (Oracle DB). The primary key of this table is the following:
PERM_CD CHAR(12),
ETA_CD CHAR(2),
PGM_CD CHAR(4),
CON_CD CHAR(4),
DIP_REP_NO CHAR(1).
This key is used as a foreign key in an associated table called "axeInt". I created a model of the database using "Entity Developer".
The difficulty comes from the "CON_CD" field which can be "null" and in this case, Oracle will store it as 4 blank spaces.
So, when I load a certificate (using LinqConnect) it works fine except that the values of the associated table "axeInt" will not be loaded. In fact, it seems like linqConnect reads my null CON_CD as '' (empty string) instead of ' ' (4 blanks). Then, it tries to find a value in the axeInt table using that key (CON_CD), and of course finds nothing associated as it searches for an empty string instead of 4 blanks.
Here is the SQL LinqConnect generated:
//READS THE CERTIFICATE
SELECT t1.ETA_CD, t1.PERM_CD, t1.PGM_CD, t1.DIP_REP_NO, t1.CON_CD, t1.PGM_OFF_CD, t1.MOY_CUM, t1.DEB_AATRIM, t1.FIN_AATRIM, t1.MIC_NO, t1.DIP_PSTH_IND, t1.PERM_PART_ETA_CD, t1.ora_rowscn AS "ora_rowscn"
FROM PRISME.certificates t1
WHERE (t1.ETA_CD = :p0)
AND (t1.PERM_CD = :p1)
AND (t1.PGM_CD = :p2)
AND (t1.DIP_REP_NO = :p3) AND (ROWNUM <= 1)
-- p0: Input Char (Size = 2; DbType = String) [05]
-- p1: Input Char (Size = 12; DbType = String) [DOUZ11111111]
-- p2: Input Char (Size = 4; DbType = String) [4001]
-- p3: Input Number (Size = 0; DbType = Decimal) [0]
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: an Build: 3.1.43.0
//THEN READS IN THE ASSOCIATED TABLE AXE_INT
SELECT t1.PERM_CD, t1.ETA_CD, t1.PGM_CD, t1.CON_CD, t1.DIP_REP_NO, t1.AXE_INTG_TXT, t1.ora_rowscn AS "ora_rowscn"
FROM PRISME.AXE_INT t1
WHERE ((:np0 = t1.ETA_CD) OR ((:np0 IS NULL) AND (t1.ETA_CD IS NULL)))
AND ((:np1 = t1.PERM_CD) OR ((:np1 IS NULL) AND (t1.PERM_CD IS NULL)))
AND ((:np2 = t1.PGM_CD) OR ((:np2 IS NULL) AND (t1.PGM_CD IS NULL)))
AND ((:np3 = t1.DIP_REP_NO) OR ((:np3 IS NULL) AND (t1.DIP_REP_NO IS NULL)))
AND ((:np4 = t1.CON_CD) OR ((:np4 IS NULL) AND (t1.CON_CD IS NULL)))
-- np0: Input Char (Size = 2; DbType = String) [05]
-- np1: Input Char (Size = 12; DbType = String) [DOUP26048405]
-- np2: Input Char (Size = 4; DbType = String) [4001]
-- np3: Input Number (Size = 0; DbType = Decimal) [0]
-- np4: Input Char (Size = 0; DbType = String) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: an Build: 3.1.43.0
Parameter np4 looks to be the one in problem here.
So, how should I handle these char columns with null values?
Thanks a lot!
I have a table which contains certificates (Oracle DB). The primary key of this table is the following:
PERM_CD CHAR(12),
ETA_CD CHAR(2),
PGM_CD CHAR(4),
CON_CD CHAR(4),
DIP_REP_NO CHAR(1).
This key is used as a foreign key in an associated table called "axeInt". I created a model of the database using "Entity Developer".
The difficulty comes from the "CON_CD" field which can be "null" and in this case, Oracle will store it as 4 blank spaces.
So, when I load a certificate (using LinqConnect) it works fine except that the values of the associated table "axeInt" will not be loaded. In fact, it seems like linqConnect reads my null CON_CD as '' (empty string) instead of ' ' (4 blanks). Then, it tries to find a value in the axeInt table using that key (CON_CD), and of course finds nothing associated as it searches for an empty string instead of 4 blanks.
Here is the SQL LinqConnect generated:
//READS THE CERTIFICATE
SELECT t1.ETA_CD, t1.PERM_CD, t1.PGM_CD, t1.DIP_REP_NO, t1.CON_CD, t1.PGM_OFF_CD, t1.MOY_CUM, t1.DEB_AATRIM, t1.FIN_AATRIM, t1.MIC_NO, t1.DIP_PSTH_IND, t1.PERM_PART_ETA_CD, t1.ora_rowscn AS "ora_rowscn"
FROM PRISME.certificates t1
WHERE (t1.ETA_CD = :p0)
AND (t1.PERM_CD = :p1)
AND (t1.PGM_CD = :p2)
AND (t1.DIP_REP_NO = :p3) AND (ROWNUM <= 1)
-- p0: Input Char (Size = 2; DbType = String) [05]
-- p1: Input Char (Size = 12; DbType = String) [DOUZ11111111]
-- p2: Input Char (Size = 4; DbType = String) [4001]
-- p3: Input Number (Size = 0; DbType = Decimal) [0]
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: an Build: 3.1.43.0
//THEN READS IN THE ASSOCIATED TABLE AXE_INT
SELECT t1.PERM_CD, t1.ETA_CD, t1.PGM_CD, t1.CON_CD, t1.DIP_REP_NO, t1.AXE_INTG_TXT, t1.ora_rowscn AS "ora_rowscn"
FROM PRISME.AXE_INT t1
WHERE ((:np0 = t1.ETA_CD) OR ((:np0 IS NULL) AND (t1.ETA_CD IS NULL)))
AND ((:np1 = t1.PERM_CD) OR ((:np1 IS NULL) AND (t1.PERM_CD IS NULL)))
AND ((:np2 = t1.PGM_CD) OR ((:np2 IS NULL) AND (t1.PGM_CD IS NULL)))
AND ((:np3 = t1.DIP_REP_NO) OR ((:np3 IS NULL) AND (t1.DIP_REP_NO IS NULL)))
AND ((:np4 = t1.CON_CD) OR ((:np4 IS NULL) AND (t1.CON_CD IS NULL)))
-- np0: Input Char (Size = 2; DbType = String) [05]
-- np1: Input Char (Size = 12; DbType = String) [DOUP26048405]
-- np2: Input Char (Size = 4; DbType = String) [4001]
-- np3: Input Number (Size = 0; DbType = Decimal) [0]
-- np4: Input Char (Size = 0; DbType = String) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: an Build: 3.1.43.0
Parameter np4 looks to be the one in problem here.
So, how should I handle these char columns with null values?
Thanks a lot!