Informix driver problem with reading data in .NET enviroment
Using IDS 11.5 with the 3.50.TC3 .NET client, a DataTable.Load() of an OUTER join query failed with "Failed to enable constraints...". The driver reports the outer table's ID column as NOT NULL from schema metadata, but the outer join returns NULLs, so the DataTable constraint check fails. Suggested workarounds were setting DataSet.EnforceConstraints = false before loading, or rewriting the query as a UNION instead of an OUTER join; the poster rejected both (hundreds of existing queries, constraints needed) and was pointed to IBM/developerWorks support to report it as a bug. No fix is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi.
Let me explain the problem. I'm using Informix IDS 11.5 DE and
Informix Client 3.50 TC3DE.
I have created two tables:
create table test1
(
id serial not null ,
partnum char(8),
primary key (id)
);
Data entered is:
1|Part1
2|Part2
create table test2
(
id integer not null ,
partname varchar(80)
);
Data entered is:
1|Part1
3|Part3
In .NET program I have this code:
using (DbConnection conn = new IfxConnection
("Database=argosy_demo;Host=inf11db.du.laus.hr;Server=inf11db_tcp;Service=9088;Protocol=onsoctcp;UID=***;Password=***;Client
locale=cs_cz.1250; DB_LOCALE=cs_cz.8859-2; Timeout=90"))
{
conn.Open();
DbCommand upit = conn.CreateCommand();
upit.CommandText = "select * from test1, outer
test2 where test1.id = test2.id";
DataTable tablica = new DataTable();
tablica.Load(upit.ExecuteReader());
}
When executed this code will return the following error at line:
tablica.Load(upit.ExecuteReader());
Error:
Failed to enable constraints. One or more rows contain values
violating non-null, unique, or foreign-key constraints.
at System.Data.DataTable.EnableConstraints()
at System.Data.DataTable.set_EnforceConstraints(Boolean value)
at System.Data.DataTable.EndLoadData()
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset,
DataTable datatable, String srcTable, DataReaderContainer dataReader,
Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn,
Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[]
dataTables, IDataReader dataReader, Int32 startRecord, Int32
maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption
loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
The problem is in outer keyword. Without it, this query works as
expected. Informix driver reads schema infromation which is used to
create DataTable object with columns types and constraints. Driver
reads for test2 table that id column is not null so he puts that
information into DataTable object but test2 is actually outer query
and that will return some null values for ID column. Schema
information and actual data doesn't match for ID column and the error
is issued.
Any suggestions are appreciated.
Could you modify your testcase as follows and see if that helps?
DbCommand upit = conn.CreateCommand();
upit.CommandText = "select * from test1, outer test2 where
test1.id = test2.id";
DataSet ds = new DataSet();
ds.EnforceConstraints = false; //EnforceCon
ds.Tables.Add();
ds.Tables[0].Load(upit.ExecuteReader());
....
HTH
-Shesh
nezreli@gmail.com
Sent by: informix-list-bounces@iiug.org
03/03/2009 17:11
To
informix-list@iiug.org
cc
Subject
Informix driver problem with reading data in .NET enviroment
Hi.
Let me explain the problem. I'm using Informix IDS 11.5 DE and
Informix Client 3.50 TC3DE.
I have created two tables:
create table test1
(
id serial not null ,
partnum char(8),
primary key (id)
);
Data entered is:
1|Part1
2|Part2
create table test2
(
id integer not null ,
partname varchar(80)
);
Data entered is:
1|Part1
3|Part3
In .NET program I have this code:
using (DbConnection conn = new IfxConnection
("Database=argosy_demo;Host=inf11db.du.laus.hr;Server=inf11db_tcp;Service=9088;Protocol=onsoctcp;UID=***;Password=***;Client
locale=cs_cz.1250; DB_LOCALE=cs_cz.8859-2; Timeout=90"))
{
conn.Open();
DbCommand upit = conn.CreateCommand();
upit.CommandText = "select * from test1, outer
test2 where test1.id = test2.id";
DataTable tablica = new DataTable();
tablica.Load(upit.ExecuteReader());
}
When executed this code will return the following error at line:
tablica.Load(upit.ExecuteReader());
Error:
Failed to enable constraints. One or more rows contain values
violating non-null, unique, or foreign-key constraints.
at System.Data.DataTable.EnableConstraints()
at System.Data.DataTable.set_EnforceConstraints(Boolean value)
at System.Data.DataTable.EndLoadData()
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset,
DataTable datatable, String srcTable, DataReaderContainer dataReader,
Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn,
Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[]
dataTables, IDataReader dataReader, Int32 startRecord, Int32
maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption
loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
The problem is in outer keyword. Without it, this query works as
expected. Informix driver reads schema infromation which is used to
create DataTable object with columns types and constraints. Driver
reads for test2 table that id column is not null so he puts that
information into DataTable object but test2 is actually outer query
and that will return some null values for ID column. Schema
information and actual data doesn't match for ID column and the error
is issued.
Any suggestions are appreciated.
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Another way might be to change your SQL. You can select the "join
rows" with a union to the "null rows".
SELECT *
FROM test1, test2
WHERE test1.id = test2.id
union
SELECT *, "", "", ""
FROM test1 WHERE id NOT IN (SELECT id FROM test2)
The first SELECT gets all the columns from both tables. The second
SELECT only gets the columns from the first table with the ""
constants that replace the second table's columns. Technically ""
will not be NULL but you get the idea.
Thanks for the answers but I'm trying to use existing software to connect to Informix database so changing sql queries to avoid outers is not something that I would recommend. There are hundreds of them. Disabling constraints is not an option also since I use those constraints. Can you point me to right adress so I can inform them officially of this bug. I know that they are making TC4 version of the driver so maybe they can fix it in time. Thanks once again.
On Mar 4, 10:23 am, nezr...@gmail.com wrote: > Thanks for the answers but I'm trying to use existing software to > connect to Informix database so changing sql queries to avoid outers > is not something that I would recommend. There are hundreds of them. > Disabling constraints is not an option also since I use those > constraints. > > Can you point me to right adress so I can inform them officially of > this bug. I know that they are making TC4 version of the driver so > maybe they can fix it in time. > > Thanks once again. As you are using eval software support id provided through developerworks I believe the details would all have been provided when you downloaded the eval software from the wval download site
*Here's the IBM support number: 800-426-7378* Then just go through the menus to software support, data management/database support, and finally Informix support. Art On Wed, Mar 4, 2009 at 9:39 AM, wcottishpoet <dryburghj@yahoo.com> wrote: > On Mar 4, 10:23 am, nezr...@gmail.com wrote: > > Thanks for the answers but I'm trying to use existing software to > > connect to Informix database so changing sql queries to avoid outers > > is not something that I would recommend. There are hundreds of them. > > Disabling constraints is not an option also since I use those > > constraints. > > > > Can you point me to right adress so I can inform them officially of > > this bug. I know that they are making TC4 version of the driver so > > maybe they can fix it in time. > > > > Thanks once again. > > As you are using eval software support id provided through > developerworks > > I believe the details would all have been provided when you downloaded > the eval software from the wval download site > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.