Metamodel timestamp issue for MSSQL

[Balendra Singh <ba...@gmail.com>]

Hi,

I am using the following query in my application. This application is using
DB2, Postgres, Oracle and MSSQL databases.
But the following is not being executed in MSSQL saying* Incorrect syntax
near '2014-05-26 16:02:45'.*

*UPDATE <Table name> SET <column name>=? WHERE (<timestamp column> <
TIMESTAMP '2014-05-26 16:02:45')*

I am creating this query using FilterItem.
After modifying the query with addition of CAST in the query, I am able to
execute the following query in MSSQL client -

*UPDATE <Table name> SET <column name>=? WHERE (<timestamp
column> < CAST('2014-05-26 16:02:45' AS DATETIME))*

*I debugged the code and found that
in org.eobjects.metamodel.util.FormatHelper.formatSqlTime(ColumnType, Date,
boolean, String, String), *we are appending this extra TIMESTAMP
as typePrefix in the query.

Am I missing something or the Metamodel is not generating query correctly
compatible for MSSQL.
Please provide your input.



Thanks,
Balendra

Re: Metamodel timestamp issue for MSSQL

[Kasper Sørensen <i....@gmail.com>]

Hi Balendra,

I think you may have catched a bug that we should look into. Looking
at SQLServerQueryRewriter I don't see any special handling of date/time
literals in MS SQL, but I do see in the docs for MS SQL server [1] that a
number of literals are possible, but none of them complies with the
standard format of MetaModel.

In addition I looked to one of my favourite SQL dialect comparison papers
[2] and it says that timestamp in MS SQL is deprecated and that DATETIME
type is preferred. I doubt it has any impact on the query literals, but
just wondering which type you use?

Best regards,
Kasper

[1] http://msdn.microsoft.com/en-us/library/ms710282(v=vs.85).aspx and
http://msdn.microsoft.com/en-us/library/ms187819.aspx

[2] http://troels.arvin.dk/db/rdbms/#data_types-date_and_time-timestamp


2014-05-27 12:21 GMT+02:00 Balendra Singh <ba...@gmail.com>:

> Hi,
>
> I am using the following query in my application. This application is using
> DB2, Postgres, Oracle and MSSQL databases.
> But the following is not being executed in MSSQL saying* Incorrect syntax
> near '2014-05-26 16:02:45'.*
>
> *UPDATE <Table name> SET <column name>=? WHERE (<timestamp column> <
> TIMESTAMP '2014-05-26 16:02:45')*
>
> I am creating this query using FilterItem.
> After modifying the query with addition of CAST in the query, I am able to
> execute the following query in MSSQL client -
>
> *UPDATE <Table name> SET <column name>=? WHERE (<timestamp
> column> < CAST('2014-05-26 16:02:45' AS DATETIME))*
>
> *I debugged the code and found that
> in org.eobjects.metamodel.util.FormatHelper.formatSqlTime(ColumnType, Date,
> boolean, String, String), *we are appending this extra TIMESTAMP
> as typePrefix in the query.
>
> Am I missing something or the Metamodel is not generating query correctly
> compatible for MSSQL.
> Please provide your input.
>
>
>
> Thanks,
> Balendra
>