Using the .Net Entity Framework with the IBM Data Server .Net
provider against an Informix server fails due to a syntax error
with the SQL statement generated. It fails with the following
exception:
<internalexception>
<message>ERROR [42000] [IBM][IDS/UNIX64] A syntax error has
occurred.</message>
<type>IBM.Data.DB2.DB2Exception</type>
<stacktrace>
at
IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method, DB2CursorType reqCursorType, Boolean
abortOnOptValueChg, Boolean skipDeleted, Boolean isResultSet,
Int32 maxRows, Boolean skipInitialValidation)
at
IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method)
at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior
behavior)
at
IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior
behavior)
at
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCom
mands(EntityCommand entityCommand, CommandBehavior behavior)
</stacktrace>
</internalexception>
The .Net provider generated the following SQL statements with
CAST('' AS nvarchar(0)) AS C2, for the empty strings. That
seems to cause the syntax error:
SELECT
Project1.id AS id,
Project1.name AS name,
Project1.C1 AS C1,
Project1.C2 AS C2,
Project1.C3 AS C3,
Project1.id1 AS id1,
Project1.name1 AS name1,
Project1.state_id AS state_id
FROM (
SELECT
Extent1.id AS id,
Extent1.name AS name,
CAST('city' AS nvarchar(4)) AS C1,
CAST('' AS nvarchar(0)) AS C2,
Extent2.id AS id1,
Extent2.name AS name1,
Extent2.state_id AS state_id,
CASE
WHEN (Extent2.id IS NULL)
THEN CAST(NULL AS int)
ELSE CAST(1 AS int)
END AS C3
FROM "informix".state AS Extent1
LEFT OUTER JOIN "informix".city AS Extent2 ON Extent1.id
= Extent2.state_id
WHERE CAST(1 AS int) = Extent1.id ) AS Project1
ORDER BY Project1.id ASC, Project1.C3 ASC
Problem can be recreated using the following select statement
against the stores_demo database:
SELECT CAST('' AS nvarchar(0)) AS C2 FROM customer;
> SELECT CAST('' AS nvarchar(0)) AS C2 FROM customer;
201: A syntax error has occurred.
Error in line 1
Near character position 28
It worked fine if I use:
SELECT CAST('' AS nvarchar(1)) AS C2 FROM customer; |
