'out of range for destination' Exception with DateTime.UtcNow in Select clause

[Former Member]

Hi,

Next code throws 'iAnywhere.Data.SQLAnywhere.SAException: 'Value 20171110 out of range for destination'' exception :

SAConnection myConnection = new SAConnection("Server=DBSERVER;DBN=DBNAME;UID=*******;PWD= *****;Links=tcpip(Host=DB-SYBASE);");

myConnection.Open();

SACommand myCommand = myConnection.CreateCommand();

myCommand.CommandText = "SELECT column1, ? FROM TABLE ";

SAParameter parm = new SAParameter();

parm.SADbType = SADbType.DateTime;

parm.Value = DateTime.UtcNow;

myCommand.Parameters.Add(parm); SADataReader myDataReader = myCommand.ExecuteReader();

I tried with SqlAnywhere16 provider but also with previous versions.

Please, can you explain me why it generates this exception?

Thanks,

Davide

[Former Member]

I use iAnywhere.Data.SQLAnywhere.v4.0.dll build 11.0.1.29604. The target framework is .NET 4.0.

Can you reproduce the issue with my test program and this settings?

Thanks

[chris_keating]

SQL Anywhere 11 is no longer actively supported. That said, I am able to see the behavior you reported on supported software but I do not believe that this is a defect.

The statement "SELECT ? FROM DUMMY" will not know the underlying datatype of the ?. It appears that the ? is prepared as a SMALLINT. The

parm.SADbType = SADbType.DateTime;

parm.Value = DateTime.UtcNow;

is not known to the engine until the statement is executed. When the ? is used in the where clause, the prepare can infer its type from the table column of the predicate.

You may need to tell the engine the column type by casting the ? i.e.,

SELECT cast( ? as timestamp ) FROM DUMMY;

or you can use the SQL Anywhere built-in UTC timestamp i.e.

SELECT current utc timestamp FROM DUMMY.

Please note that the current utc timestamp requires a version of SA that supports TIMESTAMP WITH TIMEZONE datatype and is a DateTimeOffset in .NET.

[Former Member]

Hi Chris,

Thank you very much for the quick reply. I tried also with Sap.Data.SQLAnywhere.v4.5.dll build 17.0.0.10624 but I got the same error. It seems that all '?' parameters in SELECT clause are prepared as a SMALLINT.

Is there any planning about fixing this behaviour?

Best Regards,

Davide

[chris_keating]

I do not believe that this is a defect or invalid behavior.

The underlying model of SACommand is a prepare of the statement. When this prepare is processed, the engine must deduce the datatype of the input placeholder( the ?). When the input placeholder is in a where clause, it generally can deduce the type based on the operand i.e. where c=? would use the datatype for the column c. When in a select list or a where predicate like ?=?, the input parameter type cannot be deduced so the engine uses a default.

[Former Member]

I understand what you say, but the parameter type is specified in the SADbType attribute. In a dynamic engine where the user can compose a completely generic query we can't for example run this simple query:

myCommand.CommandText = "SELECT ? FROM DUMMY ";

SAParameter parm = new SAParameter();

parm.SADbType = SADbType.NVarChar;

parm.Value ='TEST';

I think is a severe limitation, with other DB providers such a SQL Server we don't have this type of issue.

[chris_keating]

Please ignore my post indicating I was not able to get the statement working in MSS.

[former_member182948]

1.What build are you using?

Please see the below for details.
2320846 - How to confirm version of SQL Anywhere

2.What version of the .net data provide?
(Sap.Data.SQLAnywhere.v3.5, Sap.Data.SQLAnywhere.v4.5, etc...)

3.What version of the target framework?

thanks