When running an Entity Framework application, a -181 error
message may occur when using a DateTime value against a DATE
column. Entity Framework does not have a DATE data type, so
when using a Date only value in a DateTime field, the full value
2012-10-01 00:00:00.000000000 (with trailing zeros) is sent to
the server which causes a -181 error to occur. If the setting
RetryParameterBindingOnError is on (which is the default), .Net
will retry the statement again using the describe information
from the server. Once the describe occurs, the actual Date
column type is known and the value will be tried again without
the trailing zeros.
This fix will avoid the -181 error with limitations.
Limitation details :
-----------------------
1) Suppose the following table : CREATE TABLE TEST(ID INT
PRIMARY KEY NOT NULL, DATE_VALUE DATE, TIMESTAMP_VAL TIMESTAMP)
2) If a column is updated based on the date column like the one
below, it will still throw the same error -181. Microsoft code
is not initializing some of the variables and hence a decision
cannot be made on data type binding.
dbEntities1 st1 = new dbEntities1();
DateTime dtu = new DateTime(2011, 08, 01);
A_DATETIME_TEST upd = (from e1 in st1.A_DATETIME_TEST
where e1.DATE_VAL ==
dtu.Date
select e1).First();
// this will generate a select query with date column parameter
marker in the where clause :
i.e SELECT Extent1.ID AS ID, Extent1.DATE_VAL AS DATE_VAL,
Extent1.TIMESTAMP_VAL AS TIMESTAMP_VAL FROM A_DATETIME_TEST AS
Extent1 WHERE Extent1.DATE_VAL = @p__linq__0 FETCH FIRST 1 ROWS
ONLY
DateTime dt2 = new DateTime(2017, 08, 01);
upd.DATE_VAL = dt2.Date;
st1.SaveChanges(); // this will generate the update query and it
will work fine .
As a work around for this issue, a select query based based on
some other column other than the date column can be used i.e
A_DATETIME_TEST upd = (from e1 in st1.A_DATETIME_TEST
where e1.ID == 123
select e1).First();
and then try to update the date column
DateTime dt2 = new DateTime(2017, 08, 01);
upd.DATE_VAL = dt2.Date;
st1.SaveChanges();
This fix will also work for INSERT, UPDATE and DELETE generated
queries
Insert scenario :
--------------------
DateTime dtu_new = new DateTime(2018, 08, 02);
t1.ID = 252;
//t1.DATE_VAL = dtu_new.Date;
//t1.DATE_VAL = dtu_new;
t1.DATE_VAL = currentDateTime;
//insert
st1.AddToTESTs(t1);
st1.SaveChanges(); // This will generate an
insertquery which will work after this fix.
Update Scenario : Usually in entity framework , a column is
updated/deleted by first fetching the row details(which needs to
be updated).
----------------------
DateTime dtu = new DateTime(2017, 05, 15);
TEST upd = (from e1 in st1.TESTs
where e1.DATE_VAL == dtu.Date
select e1).First(); // fetching the
row details which needs to be updated. This will generate the
select query with date column in the where clause. This will
continue to throw the -181 error which can be retried by setting
the RetryParameterBindingOnError to true in the db2dsdriver.cfg
file.
DateTime dt2 = new DateTime(2008, 05, 15);
upd.DATE_VAL = dt2.Date;
st1.SaveChanges(); // This will generate the
update query which will work fine after the fix.
Delete Scenario:
--------------------
DateTime dt3 = new DateTime(2008, 05, 15);
TEST dlt = (from e1 in st1.TESTs
where e1.DATE_VAL == dt3
select e1).First(); // Fetching the
row details which needs to be deleted. This will generate the
select query with date column in the where clause. This will
continue to throw the -181 error which can be retried by setting
the RetryParameterBindingOnError to true in the db2dsdriver.cfg
file.
st1.DeleteObject(dlt);
st1.SaveChanges(); // This will generate the
delete query which will work fine after the fix. |
