datetime vs datetime2 parameter issues

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dam54
Posts: 10
Joined: Thu 23 Jun 2011 19:26

datetime vs datetime2 parameter issues

Post by dam54 » Fri 30 Sep 2011 22:15

Our testing has shown that if we have a datetime field in our table, a datetime2 parameter is created in the update stored procedure. This causes problems because of the difference in precision between the two types. When the parameter type is chosen for the update, shoudn't it be based on the actual datatype of the field in the table?

Also, we have found that if a datetime field is left NULL, and we generate the UpdateSQL, all subsequent updates fail. The difference between creating the UpdateSQL ourselves and letting SDAC create it, is that the the resulting SQL Server statments generated are different. The SDAC created version uses IS NULL in the SELECT, but when we fill in the UpdateSQL property on the query, it is trying to evaluate DATE=NULL in the WHERE clause.

Please let me know if you are aware of these issues, and if they will be fixed in the next release.

Thanks!

AndreyZ

Post by AndreyZ » Tue 04 Oct 2011 10:31

SDAC doesn't use stored procedures for update operations. If you use your stored procedure for update operation, check that its parameters have the same type as in the table that this stored procedure updates.
SDAC includes all fields in the UPDATE statement only if a table doesn't have Primary Key or the UpdateAllFields option is set to True. Please check that your tables have Primary Key. You cannot use the statement FieldName=NULL in SQL Server, it doesn't work. That's why SDAC uses the IS NULL statement. You should use it in the SQL statement that you assign to the UpdateSQL property.

Post Reply