Update iSeries DATE/TIME/TIMESTAMP columns from .Net

Question

I have a .Net application using EF4 and both DB2 LUW and iSeries.

When we run a command on the DB2, we clone the original command into a new iDB2Command, copy the parameters, and run. It all works great, until we get to DATE columns. At that point the AS400 or the driver seems to fall down: it gets a parameter that says it is a DbTypes.DateTime, and contains a DateTime object, but the column is DATE.

The error returned (on LUW) is below. The AS400 (v6r1m0) returns a slightly different wording

ERROR [22008] [IBM] CLI0114E Datetime field overflow. SQLSTATE=22008

The code looks something like this (and is totally iSeries/DB2-LUW agnostic)

// all generic System.Data classes, no iDB2Connection datatype.  The driver isn't even
// installed on dev/build machines at this point.  We rely on .Net reading the connection
// string from App.config to create the proper DB Driver (db2-luw or iSeries)

DbConnection c = ... get connection from somewhere...
DbCommand  cmd = c.CreateCommand();

var p = cmd.CreateParameter();
p.ParamterName = "V_XXX_XXX";
p.DbType = DbTypes.DateTime;
p.Value  = DateTime.Now;
cmd.AddParamter(p);
...

So...

Is there anything we are doing wrong here? ~~For LUW sending the parameter as a DbTypes.DateTime works just fine.~~ EDIT: It worked just fine on LUW because we were sending a truncated date in local test code (eg, Now.Date). Normal DateTime.Now fails with truncation error just like on the AS400)

Also, we have complete metadata on the type, so in theory it is possible to tell, at conversion time, what System.DbTypes to convert to. We are hoping that is all that needs to be done (or hacky convert-to-string stuff), rather than some underlying issue.

** Solution **

Thanks to @mike-willis, we just check column before creating the command and do a manual truncation when required.

// get the db2 column type from our model metadata, because in .net it is all just DateTime
cmd.AddParamter("@MyDateColumn", FixParam( dateObject, metatdata.ColumnType);

// fix up different types of parameters.  real version does a lot more validation
public object FixParam(object value, string db2columnType) {
    if (db2columnType == "date") return ((DateTime)value).Date;
    ...
    return value;
}

Thanks, all you DB2 folks.

Mike Wills Mike Wills · Accepted Answer · 2013-03-14T14:52:52

Coming from the i, you can just assign from a DATE field to a DateTime field.

DateTime EmployeeSignedDateTime = i.Field<DateTime>("EMP_SIGNED_DATE").Add(i.Field<DateTime>("EMP_SIGNED_TIME").TimeOfDay)

In order to sent to the i, you can do that following:

p.Add("@EMPLOYEE_SIGNED_DATE", iDB2DbType.iDB2Date).Value = DateTime.Now.Date;
p.Add("@EMPLOYEE_SIGNED_TIME", iDB2DbType.iDB2Time).Value = DateTime.Now.ToString("HH.mm.ss");

Note that I am using the IBM.Data.DB2.iSeries.dll.

Update iSeries DATE/TIME/TIMESTAMP columns from .Net

1 Answers