CODEWITHSUNDEEP
sql-serverexceptionodbcibm-integration-busextended-sql
Yeldar Kurmangaliyev
Yeldar Kurmangaliyev

Reputation: 34189

ESQL produces incorrect syntax near timestamp

ESQL compute node of production IBM WebSphere integration bus contains the following ESQL query:

SET result[] =
  SELECT p.Id, p.Date, p.Value
  FROM   DatabaseName.dbo.TableName AS p
  WHERE  p.Date >= InputRoot.XMLNSC.ns:RequestType.Request.DateFrom
  AND    p.Date <= InputRoot.XMLNSC.ns:RequestType.Request.DateTo;

DateFrom and DateTo are described in XSD as xsd:dateTime, and therefore are treated as TIMESTAMP.
Integration bus produces the following query:

SELECT p.Id, p.Date, p.Value 
FROM DatabaseName.dbo.TableName as p 
WHERE p.Date < TIMESTAMP'2016-01-02 00:00:00' and p.Date >= TIMESTAMP'2016-01-01'

which is incorrect and results in exception

[IBM][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]
Incorrect syntax near '2016-09-29 00:00:00'

What is much more strange, test IBM WebSphere works good and does not generate this weird "TIMESTAMP" prefix, while production integration bus is a full clone of test integration bus virtual machine.

SQL Server versions of both test and production remote database and its compatibility levels are the same (SQL Server 2008 R2; 80).

Exception details:

Catalog = 'BIPmsgs'
Number = 2322
Text = Child SQL exception
File = '/build/S1000_slot1/S1000_P/src/DataFlowEngine/MessageServices/ImbOdbc.cpp'
Line = 3776
Function = 'ImbOdbcStatement::checkRcInner'

SQL State = HY000
Native Error Code = 102
Error Text = [IBM][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near '2016-09-29 00:00:00'.

Upvotes: 0

Views: 1384

Answers (1)

Yeldar Kurmangaliyev
Yeldar Kurmangaliyev

Reputation: 34189

I have used the obvious workaround - I have converted TIMESTAMP to a string myself, and passed a string to ESQL:

SET OutputLocalEnvironment.DateFromString = 
   CAST(requestNode.DateFrom AS CHARACTER FORMAT 'yyyy-MM-dd HH:mm:ss.SSS');
SET OutputLocalEnvironment.DateToString = 
   CAST(requestNode.DateTo AS CHARACTER FORMAT 'yyyy-MM-dd HH:mm:ss.SSS');

SET result[] =
  SELECT p.Id, p.Date, p.Value
  FROM   DatabaseName.dbo.TableName AS p
  WHERE  p.Date >= OutputLocalEnvironment.DateFromString
  AND    p.Date <= OutputLocalEnvironment.DateToString;

It produces a correct query, but doesn't solve the problem and doesn't explain why it works on test, but does not on production.
The question is open.

Upvotes: 0

Related Questions