Error - expecting Datetime Field received widestring

[smelchi]

Hi

I have this strange behavior

On windows 2008 server, with SQL EXPRESS 2012 installed, works fine.
On windows 7 terminal accessing the server 2008 I got the error - expecting for Dt_inclusao Datetime Field received widestring.

Table defintion is
CREATE TABLE [dbo].[sac] (
[numero] bigint NOT NULL ,
[cod_unidade] smallint NOT NULL ,
[tipocontato] varchar(20) COLLATE Latin1_General_CI_AS NOT NULL ,
[ncnpj] bigint NULL ,
[empresa] varchar(40) COLLATE Latin1_General_CI_AS NOT NULL ,
[cnpj] varchar(18) COLLATE Latin1_General_CI_AS NULL ,
[contato] varchar(40) COLLATE Latin1_General_CI_AS NOT NULL ,
[historico] ntext COLLATE Latin1_General_CI_AS NULL ,
[e_mail] varchar(60) COLLATE Latin1_General_CI_AS NULL ,
[telefone] varchar(30) COLLATE Latin1_General_CI_AS NULL ,
[celular] varchar(30) COLLATE Latin1_General_CI_AS NULL ,
[fax] varchar(15) COLLATE Latin1_General_CI_AS NULL ,
[dt_inclusao] datetime2 NULL ,
[dt_alteracao] datetime2 NULL ,
[digitador] varchar(16) COLLATE Latin1_General_CI_AS NULL ,
[resp_decisao] smallint NULL ,
[com_resp_decisao] ntext COLLATE Latin1_General_CI_AS NULL ,
[freq_transp] varchar(20) COLLATE Latin1_General_CI_AS NULL ,
[com_freq_transp] ntext COLLATE Latin1_General_CI_AS NULL ,
[cif] smallint NULL ,
[fob] smallint NULL ,
[com_frete] ntext COLLATE Latin1_General_CI_AS NULL ,
[transp_atual] varchar(40) COLLATE Latin1_General_CI_AS NULL ,
[com_transp_atual] ntext COLLATE Latin1_General_CI_AS NULL ,
[avaliacao] ntext COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK__sac] PRIMARY KEY ([numero], [cod_unidade])
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

TuniQuery with "SELECT * FROM SAC ORDER BY numero DESC".

After I changed table DEFINITION, I didn´t get error.
New table definition with datetime2 changed for datetime.
CREATE TABLE [dbo].[sac] (
[numero] bigint NOT NULL ,
[cod_unidade] smallint NOT NULL ,
[tipocontato] varchar(20) COLLATE Latin1_General_CI_AS NOT NULL ,
[ncnpj] bigint NULL ,
[empresa] varchar(40) COLLATE Latin1_General_CI_AS NOT NULL ,
[cnpj] varchar(18) COLLATE Latin1_General_CI_AS NULL ,
[contato] varchar(40) COLLATE Latin1_General_CI_AS NOT NULL ,
[historico] text COLLATE Latin1_General_CI_AS NULL ,
[e_mail] varchar(60) COLLATE Latin1_General_CI_AS NULL ,
[telefone] varchar(30) COLLATE Latin1_General_CI_AS NULL ,
[celular] varchar(30) COLLATE Latin1_General_CI_AS NULL ,
[fax] varchar(15) COLLATE Latin1_General_CI_AS NULL ,
[dt_inclusao] datetime NULL ,
[dt_alteracao] datetime NULL ,
[digitador] varchar(16) COLLATE Latin1_General_CI_AS NULL ,
[resp_decisao] smallint NULL ,
[com_resp_decisao] text COLLATE Latin1_General_CI_AS NULL ,
[freq_transp] varchar(20) COLLATE Latin1_General_CI_AS NULL ,
[com_freq_transp] text COLLATE Latin1_General_CI_AS NULL ,
[cif] smallint NULL ,
[fob] smallint NULL ,
[com_frete] text COLLATE Latin1_General_CI_AS NULL ,
[transp_atual] varchar(40) COLLATE Latin1_General_CI_AS NULL ,
[com_transp_atual] text COLLATE Latin1_General_CI_AS NULL ,
[avaliacao] ntext COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK__sac] PRIMARY KEY ([numero], [cod_unidade])
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

I think the problem is with datetime2 field and date field.
Computers that have SQL Client installed the error didn´t occur.

Delphi XE with Devart Unidac 4.6.11

[AndreyZ]

Hello,

This problem is caused by the OLEDB provider. OLEDB provider returns datetime2 columns in the string format, and this causes the problem with date format. When there is no Native Client provider, UniDAC uses OLEDB provider. To solve the problem, you should install Native Client provider on all computers where you encountered the problem with the datetime2 format.

[smelchi]

Thanks for your answer.

As MSDN recommends

"Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications."

Is there any any chance in future release of UNIDAC we have this problem solved or without installing sqlclient in each terminal?

[AndreyZ]

You can avoid this problem by using the Data Type Mapping feature that is available in UniDAC since version 4.2.7. Data Type Mapping allows mapping database field types to the appropriate for you Delphi types. For example, to solve the problem with the datetime2 SQL Server type, you should map datetime2 fields to the TDateTimeField Delphi type. Here is a code example:

Code: Select all

UniConnection.DataTypeMap.AddFieldNameRule('dt_inclusao', ftDateTime);

[smelchi]

With UNIDAC 6 and over, this problem is eliminated?

[azyk]

The point is that the standard OLEDB provider processes date fields as string fields, and we can't affect this behavior. If you need to map the date type as ftDdate using the standard OLEDB provider, you can use Data Type Mapping. More details about Data Type Mapping can be found in our documentation: http://www.devart.com/unidac/docs/index ... apping.htm .