Topic
11 replies Latest Post - ‏2013-02-01T14:25:10Z by SystemAdmin
SystemAdmin
SystemAdmin
2826 Posts
ACCEPTED ANSWER

Pinned topic Odata query generation problems

‏2013-01-08T07:04:00Z |
Hello,

I got a very strange Problem with an odata Service,

When I put the following Request to my service, the result is OK

http://localhost:53733/WcfDataService1.svc/artikel?$filter=a_nr eq '0101030' & $expand=artgruppe,artinhalt/inhaltstoff

datamodel description

artikel has one artgruppe, and several artinhalt

one artinhalt references one inhaltstoff
when I query in the way above, I get a correct result.

when I query

http://localhost:53733/WcfDataService1.svc/artikel?$filter=a_nr eq '0101030' & $expand=artinhalt/inhaltstoff

I get a Syntax Error From Odata which cannot be really the expected result :
<error
xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

<message
xml:lang="de-DE">Fehler beim Verarbeiten
dieser Anforderung.</message>
  • <innererror>

<message>Fehler beim Ausführen der Befehlsdefinition. Weitere Informationen
finden Sie in der internen Ausnahme.</message>
<type>System.Data.EntityCommandExecutionException</type>
<stacktrace>bei
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand
entityCommand, CommandBehavior behavior) bei
System.Data.Objects.Internal.ObjectQueryExecutionPlan.ExecuteTResultType(ObjectContext
context, ObjectParameterCollection parameterValues) bei
System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) bei
System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
bei
System.Data.Services.Providers.BasicExpandProvider.ExpandedQueryable`1.GetEnumerator()
bei
System.Data.Services.Providers.BasicExpandProvider.ExpandedQueryable`1.System.Collections.IEnumerable.GetEnumerator()
bei System.Data.Services.WebUtil.GetRequestEnumerator(IEnumerable enumerable)
bei System.Data.Services.DataService`1.SerializeResponseBody(RequestDescription
description, IDataService dataService, IODataResponseMessage responseMessage)
bei System.Data.Services.DataService`1.HandleNonBatchRequest(RequestDescription
description) bei System.Data.Services.DataService`1.HandleRequest()</stacktrace>
  • <internalexception>

<message>ERROR 42000 IBMIDS/UNIX32 A syntax error has
occurred.</message>
<type>IBM.Data.DB2.DB2Exception</type>
<stacktrace>bei IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior
behavior, String method, DB2CursorType reqCursorType, Boolean
abortOnOptValueChg, Boolean skipDeleted, Boolean isResultSet, Int32 maxRows,
Boolean skipInitialValidation) bei
IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String
method) bei IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior) bei
IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior behavior) bei
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) bei
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand
entityCommand, CommandBehavior behavior)</stacktrace>

</internalexception>

</innererror>
</error>
unfortunately I don ´t know what is generated because the profiler just traces only syntactical correct queries ...

In my opinion this is a bug , I already tried to submit this in connect with a less detailed explanation but got no comments until now.
Thank you for any helpful information
Updated on 2013-02-01T14:25:10Z at 2013-02-01T14:25:10Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: Odata query generation problems

    ‏2013-01-17T07:40:56Z  in response to SystemAdmin
    Aditionally to my initial post I now was able to find out the query which has been generated:
    the problem is that
    CAST('' AS nvarchar(0)) AS C2,
    causes the syntax error .
    SELECT
    Project1.a_haltbarkeit AS a_haltbarkeit,
    Project1.a_nr AS a_nr,
    Project1.a_name AS a_name,
    Project1.a_such AS a_such,
    Project1.a_dichte AS a_dichte,
    Project1.a_pgr_nr AS a_pgr_nr,
    Project1.a_stueck_kg AS a_stueck_kg,
    Project1.a_vpe AS a_vpe,
    Project1.a_pe_nr AS a_pe_nr,
    Project1.a_preis AS a_preis,
    Project1.a_dekl_nr AS a_dekl_nr,
    Project1.a_agnr AS a_agnr,
    Project1.a_gruppe AS a_gruppe,
    Project1.a_bestzul AS a_bestzul,
    Project1.a_melde AS a_melde,
    Project1.a_abteilungs_nr AS a_abteilungs_nr,
    Project1.a_ref_nr AS a_ref_nr,
    Project1.a_pr_nr AS a_pr_nr,
    Project1.a_zusatz_anr AS a_zusatz_anr,
    Project1.a_zusatz_proz AS a_zusatz_proz,
    Project1.a_sack_anr AS a_sack_anr,
    Project1.a_bemerkung AS a_bemerkung,
    Project1.a_ab_nr AS a_ab_nr,
    Project1.a_inh_menge AS a_inh_menge,
    Project1.a_inh_krit AS a_inh_krit,
    Project1.a_pr_anzahl AS a_pr_anzahl,
    Project1.a_aktiv AS a_aktiv,
    Project1.a_dispo_nr AS a_dispo_nr,
    Project1.a_dispo_wahl AS a_dispo_wahl,
    Project1.a_bediener AS a_bediener,
    Project1.a_datum AS a_datum,
    Project1.a_iris_nr AS a_iris_nr,
    Project1.a_ean AS a_ean,
    Project1.a_keko_best_nr AS a_keko_best_nr,
    Project1.ifx_row_version AS ifx_row_version,
    Project1.C1 AS C1,
    Project1.C2 AS C2,
    Project1.C5 AS C3,
    Project1.posi AS posi,
    Project1.artikel_nr AS artikel_nr,
    Project1.in_nr AS in_nr,
    Project1.wert AS wert,
    Project1.verm_faktor AS verm_faktor,
    Project1.C3 AS C4,
    Project1.C4 AS C5,
    Project1.in_nr1 AS in_nr1,
    Project1.in_name AS in_name,
    Project1.in_such AS in_such,
    Project1.in_kritisch AS in_kritisch,
    Project1.in_einheit AS in_einheit,
    Project1.in_komma AS in_komma,
    Project1.in_vf AS in_vf,
    Project1.in_aktiv AS in_aktiv,
    Project1.in_bediener AS in_bediener,
    Project1.in_datum AS in_datum
    FROM ( SELECT
    Extent1.a_nr AS a_nr,
    Extent1.a_name AS a_name,
    Extent1.a_such AS a_such,
    Extent1.a_dichte AS a_dichte,
    Extent1.a_pgr_nr AS a_pgr_nr,
    Extent1.a_stueck_kg AS a_stueck_kg,
    Extent1.a_vpe AS a_vpe,
    Extent1.a_pe_nr AS a_pe_nr,
    Extent1.a_preis AS a_preis,
    Extent1.a_dekl_nr AS a_dekl_nr,
    Extent1.a_agnr AS a_agnr,
    Extent1.a_gruppe AS a_gruppe,
    Extent1.a_bestzul AS a_bestzul,
    Extent1.a_haltbarkeit AS a_haltbarkeit,
    Extent1.a_melde AS a_melde,
    Extent1.a_abteilungs_nr AS a_abteilungs_nr,
    Extent1.a_ref_nr AS a_ref_nr,
    Extent1.a_pr_nr AS a_pr_nr,
    Extent1.a_zusatz_anr AS a_zusatz_anr,
    Extent1.a_zusatz_proz AS a_zusatz_proz,
    Extent1.a_sack_anr AS a_sack_anr,
    Extent1.a_bemerkung AS a_bemerkung,
    Extent1.a_ab_nr AS a_ab_nr,
    Extent1.a_inh_menge AS a_inh_menge,
    Extent1.a_inh_krit AS a_inh_krit,
    Extent1.a_pr_anzahl AS a_pr_anzahl,
    Extent1.a_aktiv AS a_aktiv,
    Extent1.a_dispo_nr AS a_dispo_nr,
    Extent1.a_dispo_wahl AS a_dispo_wahl,
    Extent1.a_bediener AS a_bediener,
    Extent1.a_datum AS a_datum,
    Extent1.a_iris_nr AS a_iris_nr,
    Extent1.a_ean AS a_ean,
    Extent1.a_keko_best_nr AS a_keko_best_nr,
    Extent1.ifx_row_version AS ifx_row_version,
    CAST('artinhalt' AS nvarchar(9)) AS C1,
    CAST('' AS nvarchar(0)) AS C2,
    Join1.artikel_nr AS artikel_nr,
    Join1.posi AS posi,
    Join1.in_nr1 AS in_nr,
    Join1.wert AS wert,
    Join1.verm_faktor AS verm_faktor,
    Join1.in_nr2 AS in_nr1,
    Join1.in_name AS in_name,
    Join1.in_such AS in_such,
    Join1.in_kritisch AS in_kritisch,
    Join1.in_einheit AS in_einheit,
    Join1.in_komma AS in_komma,
    Join1.in_vf AS in_vf,
    Join1.in_aktiv AS in_aktiv,
    Join1.in_bediener AS in_bediener,
    Join1.in_datum AS in_datum,
    CASE WHEN (Join1.artikel_nr IS NULL) THEN CAST(NULL AS varchar(1)) ELSE CAST('inhaltstoff' AS nvarchar(11)) END AS C3,
    CASE WHEN (Join1.artikel_nr IS NULL) THEN CAST(NULL AS varchar(1)) ELSE CAST('inhaltstoff' AS nvarchar(11)) END AS C4,
    CASE WHEN (Join1.artikel_nr IS NULL) THEN CAST(NULL AS int) ELSE CAST(1 AS int) END AS C5
    FROM "hansm".artikel AS Extent1
    LEFT OUTER JOIN (SELECT Extent2.artikel_nr AS artikel_nr, Extent2.posi AS posi, Extent2.in_nr AS in_nr1, Extent2.wert AS wert, Extent2.verm_faktor AS verm_faktor, Extent3.in_nr AS in_nr2, Extent3.in_name AS in_name, Extent3.in_such AS in_such, Extent3.in_kritisch AS in_kritisch, Extent3.in_einheit AS in_einheit, Extent3.in_komma AS in_komma, Extent3.in_vf AS in_vf, Extent3.in_aktiv AS in_aktiv, Extent3.in_bediener AS in_bediener, Extent3.in_datum AS in_datum
    FROM "hansm".artinhalt AS Extent2
    LEFT OUTER JOIN "hansm".inhaltstoff AS Extent3 ON Extent2.in_nr = Extent3.in_nr ) AS Join1 ON Extent1.a_nr = Join1.artikel_nr
    WHERE CAST('0101030' AS nvarchar(7)) = Extent1.a_nr
    ) AS Project1
    ORDER BY Project1.a_nr ASC, Project1.C5 ASC
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: Odata query generation problems

    ‏2013-01-17T15:08:33Z  in response to SystemAdmin
    Hi,
    I managed to create a demo project and to get a little bit nearer to the problem

    If you call
    http://localhost:53732/WcfDemoservice.svc/article?$filter=id eq 10&$expand=artinhalt

    you get the syntax error in generated sql
    SELECT
    Project1.id AS id,
    Project1.name AS name,
    Project1.concurrency AS concurrency,
    Project1.articlegroupid AS articlegroupid, Project1.a_pe_nr AS a_pe_nr,
    Project1.C1 AS C1,
    Project1.C2 AS C2,
    Project1.C3 AS C3,
    Project1.artikel_nr AS artikel_nr,
    Project1.posi AS posi,
    Project1.in_nr AS in_nr,
    Project1.wert AS wert,
    Project1.verm_faktor AS verm_faktor
    FROM ( SELECT
    Extent1.id AS id,
    Extent1.name AS name,
    Extent1.concurrency AS concurrency,
    Extent1.articlegroupid AS articlegroupid,
    Extent1.a_pe_nr AS a_pe_nr,
    CAST('artinhalt' AS nvarchar(9)) AS C1,
    CAST('' AS nvarchar(0)) AS C2,
    Extent2.artikel_nr AS artikel_nr,
    Extent2.posi AS posi,
    Extent2.in_nr AS in_nr,
    Extent2.wert AS wert,
    Extent2.verm_faktor AS verm_faktor,
    CASE WHEN (Extent2.artikel_nr IS NULL) THEN CAST(NULL AS int) ELSE CAST(1 AS int) END AS C3
    FROM "informix".article AS Extent1
    LEFT OUTER JOIN "informix".artinhalt AS Extent2 ON Extent1.id = Extent2.artikel_nr
    WHERE CAST(10 AS int) = Extent1.id
    ) AS Project1
    ORDER BY Project1.id ASC, Project1.C3 ASC

    Error :
    ...
    CAST('artinhalt' AS nvarchar(9)) AS C1,
    >> here>> CAST('' AS nvarchar(0)) AS C2,
    as far as I think these casts might have to do something with the related model objects

    In the model the article has another related Objekt Preiseinheit.

    If you call
    http://localhost:53732/WcfDemoservice.svc/article?$filter=id eq 10&$expand=artinhalt,preiseinheit
    You get
    SELECT
    Project1.id AS id,
    Project1.name AS name,
    Project1.concurrency AS concurrency,
    Project1.articlegroupid AS articlegroupid, Project1.a_pe_nr AS a_pe_nr,
    Project1.C1 AS C1,
    Project1.C2 AS C2,
    Project1.pe_nr AS pe_nr,
    Project1.pe_bezeichnung AS pe_bezeichnung, Project1.pe_such AS pe_such, Project1.pe_faktor AS pe_faktor, Project1.pe_aktiv AS pe_aktiv, Project1.pe_bediener AS pe_bediener, Project1.pe_datum AS pe_datum,
    Project1.C3 AS C3,
    Project1.artikel_nr AS artikel_nr,
    Project1.posi AS posi,
    Project1.in_nr AS in_nr,
    Project1.wert AS wert,
    Project1.verm_faktor AS verm_faktor
    FROM ( SELECT
    Extent1.id AS id,
    Extent1.name AS name,
    Extent1.concurrency AS concurrency,
    Extent1.articlegroupid AS articlegroupid,
    Extent1.a_pe_nr AS a_pe_nr,
    Extent2.pe_nr AS pe_nr,
    Extent2.pe_bezeichnung AS pe_bezeichnung,
    Extent2.pe_such AS pe_such,
    Extent2.pe_faktor AS pe_faktor,
    Extent2.pe_aktiv AS pe_aktiv,
    Extent2.pe_bediener AS pe_bediener,
    Extent2.pe_datum AS pe_datum,
    CAST('preiseinheit,artinhalt' AS nvarchar(22)) AS C1,
    CAST('preiseinheit' AS nvarchar(12)) AS C2,
    Extent3.artikel_nr AS artikel_nr,
    Extent3.posi AS posi,
    Extent3.in_nr AS in_nr,
    Extent3.wert AS wert,
    Extent3.verm_faktor AS verm_faktor,
    CASE WHEN (Extent3.artikel_nr IS NULL) THEN CAST(NULL AS int) ELSE CAST(1 AS int) END AS C3
    FROM "informix".article AS Extent1
    LEFT OUTER JOIN "informix".preiseinheit AS Extent2 ON Extent1.a_pe_nr = Extent2.pe_nr
    LEFT OUTER JOIN "informix".artinhalt AS Extent3 ON Extent1.id = Extent3.artikel_nr
    WHERE CAST(10 AS int) = Extent1.id
    ) AS Project1
    ORDER BY Project1.id ASC, Project1.pe_nr ASC, Project1.C3 ASC

    which is syntactically correct but crashes when there is no preiseinheit in the preiseinheit table
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: Odata query generation problems

    ‏2013-01-17T15:47:04Z  in response to SystemAdmin
    Hi,

    The syntax error is caused by the 'nvarchar(0)' in the generated query.

    APAR IC87451 is already logged and should be fixed in the next release of IBM DSD .Net provider.

    HTH.

    Yunming
    • bpgross
      bpgross
      627 Posts
      ACCEPTED ANSWER

      Re: Odata query generation problems

      ‏2013-01-17T16:49:27Z  in response to SystemAdmin
      Sascha - I just wanted to let you know that if you are working with IBM service on this, and are not able to wait for the next FP delivery, you are able to request a special build to get the problem fixed.

      I can not comment on timeframes for the next FP in an open forum, other than to say our FP deliveries are approx every 6 months (based on past history), and the last one, V10.1 FP2, was in Dec 2012.

      Brent.
      • SystemAdmin
        SystemAdmin
        2826 Posts
        ACCEPTED ANSWER

        Re: Odata query generation problems

        ‏2013-01-18T15:08:13Z  in response to bpgross
        Hello Brent,
        thank you for the answer and the suggestion to get a special build.
        If the two problems I have at the moment can be fixed in a special build
        ( the one with the syntax error is already mentioned by Yunming ) , the one with the null value when expand table contains no data
        you can find it here
        https://www.ibm.com/developerworks/forums/thread.jspa?threadID=467254&tstart=0
        is also solved, it would be very helpful for me to get a special build so that I can go on with my work.
        There seems to be a third problem with the merge modules when building a setup which contains the latest drivers
        ( seems to be something in the manifest which is deployed) but I have not investigated this in depth. For the moment
        the most important thing for me is to get my data queried ( and updated) from the service.

        Thank you
        Sascha
        • bpgross
          bpgross
          627 Posts
          ACCEPTED ANSWER

          Re: Odata query generation problems

          ‏2013-01-18T20:12:35Z  in response to SystemAdmin
          Sascha,

          I do not track the status of individual problems, the best thing to do is work with the support folks - make sure they know the impact to you of these problems, that you need a single update with both problems addressed, etc.

          Brent.
        • SystemAdmin
          SystemAdmin
          2826 Posts
          ACCEPTED ANSWER

          Re: Odata query generation problems

          ‏2013-01-24T22:09:48Z  in response to SystemAdmin
          Hi Sascha,

          As Brent suggested, you can call IBM Informix support (1-800-IBM-SERV) to get a special build for both fixes if you cannot wait for the next release.

          Regards, Yunming
          • SystemAdmin
            SystemAdmin
            2826 Posts
            ACCEPTED ANSWER

            Re: Odata query generation problems

            ‏2013-01-25T06:52:24Z  in response to SystemAdmin
            Hi Yunming,
            thank you for the phone Number to request the update,
            do you also have the Problem ID for the second issue ?
            So I would be able to make sure that the build contains the solution for the problem.
            Regards, Sascha
            • SystemAdmin
              SystemAdmin
              2826 Posts
              ACCEPTED ANSWER

              Re: Odata query generation problems

              ‏2013-01-28T17:12:01Z  in response to SystemAdmin
              Hi Sascha,

              I did a quick search, but I could not find any information about the second issue. According to your discussion with Arvind, it seems like he could not recreate the problem using the project you attached to the discussion. Have you opened up a PMR for that issue? If so, the PMR owner should assist you with it.

              Regards, Yunming
              • SystemAdmin
                SystemAdmin
                2826 Posts
                ACCEPTED ANSWER

                Re: Odata query generation problems

                ‏2013-02-01T14:25:10Z  in response to SystemAdmin
                Hi Yunming,
                I have now some new information about the issue. As You suggested I made a support request. During the things we tried with the IBM Support we found out it seems to be an issue which is caused only in the case you have a Linux 11.7 UC4 Version as server ( This may also be the reason why it was hard to reproduce).

                When I will have updated the database and have new results I can post this .

                Regards, Sascha
    • SystemAdmin
      SystemAdmin
      2826 Posts
      ACCEPTED ANSWER

      Re: Odata query generation problems

      ‏2013-01-18T14:56:46Z  in response to SystemAdmin
      Hello Yunming,

      thank you for the reply and the fact that this error has been tracked and will be fixed.
      I also have posted another error recently where I get a null value exception in a joined table that is empty
      ( the special fact is that the property which causes the exception is decimal and only the main table contains data ) you can find it here:

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

      is this also already known ?
      Thank you

      Sascha