ORM GetSqlQuery oddness

Thread is closed for posting
6 posts, 0 answers
  1. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 23 Jun 2011 Link to this post

    I have run into a problem with using the GetSqlQuery() in IObjectScopeProvider.

    I have the following code that causes this error:
    Query failed: System.NullReferenceException: Object reference not set to an instance of an object.
       at OpenAccessRuntime.Data.VariableLengthStringConverter.Write(DataHolder& data)
       at OpenAccessRuntime.Relational.RelationalQueryResult.SetParameters(RelationalCompiledQuery rcq, PreparedStatement stat, Object[] values)

    IQuery q1 = sc.GetSqlQuery(@"SELECT PopulationID, LocationName, Direction, Distance FROM populations WHERE populationid IN  (SELECT populationid FROM populationswrittenreports WHERE  writtenReportID = ?)", typeof(Population), "VARCHAR writtenReportID");
     
             IQueryResult res = q1.Execute(new object[] { reportID });
     
             try
             {
                 int count = res.Count;
                 return DataDriver.Utilities.ToList<Population>(res.ToList());
             }
             catch
             {
                 return new List<Population>();
             }

    Now when I change the first two lines to below. the code executes and runs fine. I assume the SELECT IN clause is what is causing the error, seeing as that is the only change. Is there a way to accomplish this without putting all my "complex" sql code into stored procedures?

    IQuery q1 = sc.GetSqlQuery(@"SELECT PopulationID, LocationName, Direction, Distance FROM populations ", typeof(Population), null);
     
    IQueryResult res = q1.Execute();


    thanks
  2. Ady
    Admin
    Ady avatar
    589 posts

    Posted 29 Jun 2011 Link to this post

    Hi Kris,

    I did try a similar scenario with the standard Northwind database by executing the following query - "select * from Orders where ShipVia in (select ShipperID from Shippers where CompanyName = ? )"
    and I could succesfully get the results. Can you provide some information about the table definition, this might provide further insight into the problem. If you can provide a sample solution where I can reproduce the exception this would definitely save us some time.

    Looking forward to resolving the issue soon.

    Best wishes,
    Ady
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  3. Ady
    Admin
    Ady avatar
    589 posts

    Posted 29 Jun 2011 Link to this post

    Hello Kris,

    I did try a similar scenario with the standard Northwind database by executing the following query - "select * from Orders where ShipVia in (select ShipperID from Shippers where CompanyName = ? )"
    and I could succesfully get the results. Can you provide some information about the table definition, this might provide further insight into the problem. If you can provide a sample solution where I can reproduce the exception this would definitely save us some time.

    Looking forward to resolving the issue soon.

    Best wishes,
    Ady
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  4. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 29 Jun 2011 Link to this post

    I have opened a support ticket (# 438900) and attached a sample app to it. Please take a look and let me know, thanks.
  5. Ady
    Admin
    Ady avatar
    589 posts

    Posted 05 Jul 2011 Link to this post

    Hello Kris,

     Could we close this thread as I've already answered to your problem via the support thread?

    Greetings,
    Ady
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  6. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 07 Jul 2011 Link to this post

    Sure can. I will post your answer in case other developers run into the same issue. Thanks for the help

    Answer:
    It occurs since the type specified in the query is 'VARCHAR' and the actual column is of type 'uniqueidentifier'. You should replace the 'VARCHAR' with 'GUID' and then it will work

    SELECT PopulationID, LocationName, Direction, Distance FROM populations WHERE populationid IN  (SELECT populationid FROM populationswrittenreports WHERE  writtenReportID = ?)", typeof(Population), "GUID writtenReportID"
Back to Top