Topic
1 reply Latest Post - ‏2013-01-17T08:55:53Z by SystemAdmin
rmussim73
rmussim73
1 Post
ACCEPTED ANSWER

Pinned topic Incorrect Sql generated with IBM.Data.DB2.dll V10.1 FP1 and Informix 11.5 ?

‏2012-10-08T18:29:34Z |
Hi:
I am seeing the following sql generation syntax error when we develop wcf read only data services (ODATA) with "$expand" query operator on "one to many" relational data. However, we do not see any issues with "Many to one" relational data.

The version we have installed for Informix Data Sever Client Package is V10.1 FP1. (Latest) and .Net Entity framework 5.0.

I have simple model with 2 tables. State and City table. City can have many state id's.

We have captured the sql it generated at the Informix database level and we found that the sql it generated has the syntax error. For the empty strings, the data type size it included as nvarchar(''). I believe it supposed to include nvarchar(1) instaed of nvarchar('').

Below is the query url and error. (Here, all I am doing is - trying to expand the city navigation property to view the more cities for the given state identifier 1. (One to many relational data)

Odata Url with "$expand" option (one to many : State >> to City):
/TestOdataFeatures/TestInformixDataService.svc/state(1)?$expand=city

ERROR:
<internalexception>
<message>
ERROR 42000 IBMIDS/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.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
</stacktrace>

.
Incorrect Sql that the client package generated is:

It generating the sql statements with CAST('' AS nvarchar(0)) AS C2, for the empty strings. It supposed to be CAST('' AS nvarchar(1)) AS C2.

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
Thanks,
Ramesh
Updated on 2013-01-17T08:55:53Z at 2013-01-17T08:55:53Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: Incorrect Sql generated with IBM.Data.DB2.dll V10.1 FP1 and Informix 11.5 ?

    ‏2013-01-17T08:55:53Z  in response to rmussim73
    Hi Ramesh,
    I got a similar Problem with the odata tools (two at the moment)
    See one of my posts which seems to be most similar to your problem

    https://www.ibm.com/developerworks/forums/thread.jspa?threadID=467252&tstart=0