From Ingres Community Wiki

Contents 1 Requirements 2 Ingres Enhancement Number 3 Status 4 The Problem 5 Proposal 6 Example 7 Documentation Updates 8 Documentation 8.1 Release Summary 8.2 Connectivity Guide

Requirements

The Ingres .NET Data Provider sends .NET DateTime parameter data to the Data Access Server (DAS) and the DBMS. This project is the addition of a new IngresType.IngresDate parameter type to allow the application to specify that the DateTime parameter data be sent to the DBMS with an Ingresdate data type and format.

Ingres Enhancement Number

SIR 123368 - Ingres .NET Data Provider support for IngresType.IngresDate parameter datatype.

Status

Submitted into the codeline. Ingres.Client.dll file version is 2.1.1000.31.

The Problem

Discussed in detail at Drivers DateTime Data Type Send Format Control Project

In releases prior to Ingres 2006 Release 2, data with a ParameterType of IngresType.DateTime is sent as a data type of ingresdate with a GMT timezone. Beginning with Ingres 2006 Release 2, the date/time data is sent with a data type of ANSI TIMESTAMP_WITH_TIMEZONE (TS_W_TZ) with a local datetime, local timezone, and microsecond data. This flexibility and adaptability to the capabilities of the DBMS is useful, but can have a side-effect when upgrading to new releases of Ingres. When upgrading from a release of Ingres prior to 2006 Release 2 or a later release, the behavior of the program can change under certain circumstances when datetime formerly sent as an INGRESDATE type is now sent as a TS_W_TZ type. Most of the time, there is no problem. The TS_W_TZ data is converted as needed and any components of the data such as microseconds might be discarded depending on the target data type. However, in some cases, the change in data type can change the semantics of processing enough to become an issue. For example, if datetime parameter data is sent to a query containing the the IFNULL( ? , ' ') function, the function may succeed or fail depending on the data type send by the driver. If the driver is sending the data as INGRESDATE data type then IFNULL(<ingresdate>, ' ' ) will execute correctly (returning the date or the empty string date if the parameter is null). Upon upgrade to the newer Ingres releases of Ingres 2006 Release 2 and later, the datetime parameter data is sent by the Ingres .NET Data Provider as TIMESTAMP_WITH_TIMEZONE data type and the IFNULL(<ts_w_tz>, ' ' ) will fail because the empty string date is not permitted in combination with ANSI TIMESTAMP_WITH_TIMEZONE.

Proposal

The suggestion is add another entry to the Ingres.Client.IngresType enumeration of the Ingres .NET Data Provider's base parameter types. A parameter type of the new IngresType.IngresDate directs the data provider to send the parameter data as a Ingresdate data type and format.

This feature should used in an application with caution. It should only be used in applications accessing only ingresdate columns. Applications accessing ANSI Date/Time columns may experience side-effects of this feature due to loss of fractional second information and timezone format differences when the datetime parameter data is sent as ingresdate.

Example

IngresConnection conn = new IngresConnection();
IngresCommand cmd;
cmd = new IngresCommand();
cmd.CommandText = "INSERT INTO my_table VALUES (?)";
cmd.Connection = conn;
cmd.Parameters.Add(new IngresParameter("mydateparm", IngresType.IngresDate));
cmd.Parameters[0].Value = DateTime.Now;

Documentation Updates

The Connectivity Guide will be updated in the Understanding .NET Connectivity chapter to document the IngresType.IngresDate enumeration value.

Documentation

Release Summary

IngresType.IngresDate Parameter Type in .NET Data Provider

The Ingres .NET Data Provider sends .NET DateTime parameter data to the Data Access Server and the DBMS Server. A new IngresType.IngresDate parameter type allows the application to specify that the DateTime parameter data be sent to the DBMS with an INGRESDATE data type and format.

As of Ingres 9.1 (also know as Ingres 2006 Release 2), the Ingres .NET Data Provider sends DateTime data as ANSI TIMESTAMP_WITH_TIMEZONE type, rather than INGRESDATE type. In some cases, the change in data type can produce unwanted effects after upgrading to new releases of Ingres.

The new parameter type directs the data provider to send the parameter data as an INGRESDATE data type and format. This feature should be used only in applications that access only INGRESDATE columns. Applications that access ANSI DateTime columns may experience side effects of this feature due to loss of fractional second information and time zone format differences when the DateTime parameter data is sent as INGRESDATE.

Example:

IngresConnection conn = new IngresConnection();
IngresCommand cmd;
cmd = new IngresCommand();
cmd.CommandText = "INSERT INTO my_table VALUES (?)";
cmd.Connection = conn;
cmd.Parameters.Add(new IngresParameter("mydateparm", IngresType.IngresDate));
cmd.Parameters[0].Value = DateTime.Now;

For details on the IngresType.IngresDate parameter type, see the Connectivity Guide.

Connectivity Guide

The Ingres Connectivity Guide for Ingres 10, chapter Understanding .NET Data Provider Connectivity will be updated to add the IngresType.IngresDate enumeration value to the Data Types Mapping Table.

The following information will be added to the Data Types Mapping Table:

IngresType	IngresData Type	Description	.NET Data Type
IngresDate	ingresdate	Ingres format date	DateTime

IngresType.DateTime parameter data is sent by the data provider depending on the level of support of the ANSI Date/Time data types. You can override the default behavior by specifying the IngresType.IngresDate data type for the parameter data rather than IngresType.DateTime. IngresType.IngresDate parameter data type forces the data provider to send the date/time parameter data as INGRESDATE type and value. For more information, see SendIngresDate Connection Keyword.

---

Note: The SendIngresDate Connection Keyword was added recently as a bug fix to assist with IngresDate version ANSIDate issues. This information is not yet in the online version of the Connectivity Guide but is made available below as a reference point.

SendIngresDate Connection Keyword

The Ingres .NET Data Provider sends .NET DateTime parameter data to the Data Access Server (DAS) and the DBMS. The format of this DateTime parameter data sent by the provider depends on the level of support of the ANSI Date/Time data types.

In releases prior to Ingres 9.1 (also known as 2006 Release 2), the data is sent as a data type of INGRESDATE with a GMT timezone. Beginning with Ingres 9.1, the date/time data is sent with a data type of ANSI TIMESTAMP_WITH_TIMEZONE (TS_W_TZ) with a local datetime, local timezone, and microsecond data. This flexibility and adaptability to the capabilities of the DBMS is useful, but can have a side-effect when upgrading to new releases of Ingres.

When upgrading from a release of Ingres prior to 9.1, the behavior of the program can change under certain circumstances when datetime formerly sent as an INGRESDATE type is now sent as a TS_W_TZ type. Typically, no problem occurs because the TS_W_TZ data is converted as needed and any components of the data such as microseconds are discarded, depending on the target data type. In some cases, however, the change in data type can change the semantics of processing enough to become an issue.

For example, if datetime parameter data is sent to a query containing the the IFNULL( ? , ' ') function, the function may succeed or fail depending on the data type sent by the provider. If the provider sends the data as INGRESDATE type, then IFNULL(<ingresdate>, ' ' ) will execute correctly (returning the date or the empty string date if the parameter is null). Upon upgrade to Ingres 9.1 and later, the datetime parameter data is sent by the drivers as TIMESTAMP_WITH_TIMEZONE data type, and the IFNULL(<ts_w_tz>, ' ' ) will fail because the empty string date is not permitted in combination with ANSI TIMESTAMP_WITH_TIMEZONE.

The work-around for this problem is to wrap the INGRESDATE function around the parameter: IFNULL( INGRESDATE(?) , ' '). Changing every source reference in an application, however, may not be feasible.

Using the SendIngresDate=TRUE connection keyword tells the Ingres .NET Data Provider to send the date/time parameter data as INGRESDATE type and value (as if the server was pre-Ingres 9.1) to force the old behavior.

Caution! SendIngresDate=TRUE is intended to allow older applications to run with newer versions of Ingres. It should be used only in applications that access INGRESDATE columns only. Applications accessing ANSI Date/Time columns may experience other side effects of this feature due to loss of fractional second information and timezone format differences when the datetime parameter data is sent as INGRESDATE to an ANSI date/time column. Rather than using the SendIngresDate option, which is general in scope, we recommend using the IngresType.IngresDate parameter data type when sending .NET DateTime data that requires an INGRESDATE semantic context for the parameter.