• EN
Question

Oracle to SQL: Some records won't write due to Foreign Constraint of NULL values into a nullable field.

  • 11 January 2013
  • 8 replies
  • 11 views
Z

The question is pretty simple...

 

 

I am reading an Oracle table and Writing to a SQL table. One of the fields in the target SQL table has a Foreign Key stating any value that enters that field must be in the referenced code list table. This field is also a nullable field; it allows null values.

 

 

When I enter a record into this table, with the value of the field in question being null from WITHIN SQL Server, the records inserts successfully and no problems.

 

 

When I run my translation I get the following:

 

 

*****      |MS SQL Server Writer: Failed to write a feature of type `dbo.COUNTY_BOUNDARY' to the database. Provider error `(-2147217873) The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SOURCE_G__COUNTY_B_SOURCE_G". The conflict occurred in database "CrestwoodPROD_11282012", table "dbo.SOURCE_GCL", column 'CODE'.'. SQL Command `INSERT INTO dbo.[COUNTY_BOUNDARY] ([EVENT_ID], [EVENT_GUID], [DESCRIPTION], [COMMENTS], [STATE_GCL], [FIPS_CL], [SOURCE_GCL]) VALUES (?, ?, ?, ?, ?, ?, ?)'

 

2013-01-11 16:24:38|   1.0|  0.0|WARN  |+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

2013-01-11 16:24:38|   1.0|  0.0|WARN  |Feature Type: `dbo.COUNTY_BOUNDARY'

 

 

2013-01-11 16:24:38|   1.0|  0.0|WARN  |Attribute(string)                 : `SOURCE_GCL' has value `'

 

 

*******

 

 

 

That contraint I am "Violating" is the Source_GCL field needs to be referencing the Source_GCL table's Code field.

 

 

The inserted value that is causing the contraint violation is a null value, and NULL values are allowed for this field ( and this was proven by manually writing to the table within SQL Server). 

 

 

The question is why is FME not understanding that the value being inserted is a NULL value and not a different value?  The warning log above even shows the value as :  **      : `SOURCE_GCL' has value `'       **.  

 

 

 

I do have a Value Mapper in the translation changing 4 values of the same Attribute Type. Based upon how the Value Mapper changes all values that were not listed in the list to the Default Value, I have the Default Value blank. Per FME sources, a blank Default Value sets the remaining values to NULL.  So the Source NULL values SHOULD stay NULL through this transformer.

 

 

Also, when I write to inspection and look at the value, it is blank, with no spaces.   This to me suggests it is still NULL.

 

 

 

Any Thoughts?

 

 

 

Thanks,

 

 

 

Zachary

8 replies

S
If the records u r trying to upload has NULL value for that particular attribute, perhaps the quick and simple option could be that you can just remove that field mapping from the target dataset (i.e. in FME workbench)

 

 

Or you can use attributesetter transformer and set the field to NULL

 

Z
Vv
Z
Thanks for your thoughts. 

 

 

However I need to map this attribute.  There are 109 records and 100 of the records have an actual code in their value that is valid. The other nine have a NULL value. 

 

 

Also, using a null setter would seem redundant because a. They are already null and b. the blank default value in my value mapper is making them null again anyways when I am mapping the other 100 records (4 distinct codes)

 

 

 

My issue seems more like a bug rather than a "make them null" 

 

 

These are the ones that are frustrating. 

 

 

Thanks!

 

 

Zach

 

 

 

T
Badge
In SQL Server NULL is not the same as '' (an empty string).

 

 

For me, Oracle is much more logical regarding this. SQL Server is more of a programmers object oriented view. I don't know what the SQL standard contains. Probably both...

 

 

try this in SQL Server:

 

 

create table aaa (sometext varchar (5))

 

 

insert into aaa values ('12ab')

 

insert into aaa values ('ab12')

 

insert into aaa values ('') -- this string is empty

 

insert into aaa values (' ') -- this is a space

 

 

select * from aaa

 

 

select sometext,'"'+sometext+'"',LEN(sometext),DATALENGTH(sometext) from aaa

 

So you might need the NULL setting after all.

 

 

/Tim

 

 

p.s. this continues to confuse me in ArcGIS when it inserts en empty string into a not null field/column...
Z
Tim,

 

 

Thanks for your response.

 

 

I am doing the excercise you suggested now.  What exactly am I look for though? What are the results/non-result I need to watch for that will give me insight into my issue.

 

 

Thanks.
Z
sometext    (No column name)    (No column name)    (No column name)

 

12ab                     "12ab"                                  4                                   4

 

ab12                     "ab12"                                  4                                   4

 

                              ""                                            0                                   0

 

                              " "                                           0                                  1

 

 

 

Those are the results of the query ---  select sometext,'"'+sometext+'"',LEN(sometext),DATALENGTH(sometext) from aaa

 

 

 

I kept all the dummy names the same for communication purposes.

 

 

 

Are you saying when you insert a null value ( blank string) into SQL it is handling it as a blank string and NOT as a NULL value?

 

 

 

Zach
Z
For those interested in the results:

 

 

The "work around" that was tested and successfully worked is as follows:

 

 

 From Robyn Rennie - SAFE

 

"The only other alternative is to use the NullAttributeReplacer to replace the incoming null values with some crazy value like ZZZZ and then replace these in the AttributeValueMapper to Null."

 

 

(Tested with FME Beta 2013, Valuemapper using Value 'Set to Null' Functionality)

 

Thanks for getting this working Robyn. However, the process seems redundant to set a NULL value to another Value and then back to NULL to make it go into the datebase. The NULL value should go in the first time without going through this process.

 

 

Hopefully development can debug this issue and find out what causes it.  Thanks again everyone.

 

 

 

T
Badge
"...Are you saying when you insert a null value ( blank string) into SQL it is handling it as a blank string and NOT as a NULL value?..."

 

 

Yes!

 

There is a difference between '' (empty string) and the keyword NULL in SQL Server.

 

 

I support your wish. It should default to insert NULL, not empty strings. If you really need empty strings, that is where you should do something special.

 

 

/Tim

Reply


Community Stats

28,824
Posts
109,865
Replies
36,639
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.