Char column with no value used as foreign key.

Char column with no value used as foreign key.

Postby alheureu » 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!
alheureu
 
Posts: 11
Joined: Mon 01 Oct 2012 20:03

Re: Char column with no value used as foreign key.

Postby MariiaI » Wed 17 Oct 2012 13:31

Thank you for the report. We have reproduced this issue. The thing is that blank characters of fixed-length strings are trimmed when reading data, and these trimmed strings are used when sending data back (e.g., when reading related entities). We will analyze the possibility of changing this behaviour.

As a workaround, please try setting the 'Trim Fixed Char' parameter of the connection string you are using to 'False'.
For example, you can do it via the Connection Properies dialog: Open Database Explorer, right-click on Database Connection->Edit Connection Properies->Advanced. Find the 'Trim Fixed Char' parameter in the opened dialog window and change it. After this please save your model. This parameter specifies whether to trim trailing spaces when reading data from fixed-length string data types (CHAR, NCHAR).

Please tell us if this helps.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Char column with no value used as foreign key.

Postby alheureu » Thu 18 Oct 2012 20:44

This is exactly the parameter I was looking for. You rock Mariial!
One thing though, I find it a bit annoying that this is a global parameter.
Then all my char fields are treated in the same manner.

It would have been great to be able to set this at the field level!
Because, in fact, I'd rather have all my char fields trimmed, except that one field «CON_CD» because it is part of a primary key.

So the best would have been to set a default «Trim Fixed Char = TRUE» at the connection level. And to set something like «Trim trailing blanks = FALSE» on my problematic property «CON_CD» to override the default behavior.

Please let me know if this could be an option for a future release!
Thanks again!
alheureu
 
Posts: 11
Joined: Mon 01 Oct 2012 20:03

Re: Char column with no value used as foreign key.

Postby MariiaI » Mon 22 Oct 2012 07:24

Thank you for the suggestion, we will consider the possibility of adding this option. We will inform you about the results as soon as possible.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Char column with no value used as foreign key.

Postby MariiaI » Fri 16 Nov 2012 07:26

The bug with using blank fixed-length strings in Oracle is fixed.
The new build of LinqConnect 4.1.127 is available for download now. It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information please refer to http://forums.devart.com/viewtopic.php?f=31&t=25288
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Char column with no value used as foreign key.

Postby alheureu » Tue 19 Feb 2013 15:53

Hi, can you please give me some details about how the bug was fixed?
I just installed the latest version of dotConnect for Oracle (7.5) and I am now having a similar issue.

I have an entity that is a program. It's key is based on two fields:
a) PGM_CD CHAR(4) NOT NULL
b) CON_CD CHAR(2)

As you can see, my entity will always have a pgm_cd but the con_cd is nullable.
Let's say I have a row in the database with pgm_cd value of '4456' and a null con_cd, that will be saved as two blanks (' ').

If I pull that row from the DB with TrimFixedChar = TRUE, I will get an object with PGM_CD='4456' and CON_CD = '' (empty string). Then when I want to save it back to the database I get an error telling the row was not found. That's because the update statement generated by LinqConnect looks like this:

UPDATE PROGRAM SET COL_A = 2
WHERE PGM_CD = '4456'
AND CON_CD = ''; --the problem is here empty string instead of two blanks.

I don't remember having this issue while using the prior version of dotConnect which included LinqConnect 4.1.127.

Thanks again for your help.
alheureu
 
Posts: 11
Joined: Mon 01 Oct 2012 20:03

Re: Char column with no value used as foreign key.

Postby MariiaI » Wed 20 Feb 2013 13:56

Thank you for the report. We have reproduced this error with the latest version of dotConnect for Oracle. We will fix it and inform you when the new build with this fix is available for download.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Char column with no value used as foreign key.

Postby MariiaI » Mon 25 Mar 2013 11:22

We have fixed the bug related to using blank fixed-length strings. The fix will be included in the next build of dotConnect for Oracle. We will inform you when it is available for download.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Char column with no value used as foreign key.

Postby MariiaI » Fri 05 Apr 2013 08:31

The new build of dotConnect for Oracle 7.7.217 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=26313
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)