Manual queries

14 posts, 0 answers
  1. Alex
    Alex avatar
    10 posts
    Member since:
    Feb 2009

    Posted 17 Feb 2009 Link to this post

    Hi,

    I am currently evaluating OpenAccess and I have a problem with the GetSqlQuery method.  I have a table named Position with 2 columns for its primary key (OrganizationNo and PositionNo) and when I tried the following code to retrieve a list of Position objects :
                string query = @"select * from Position "
                IQuery queryy = scope.GetSqlQuery(query, typeof(Position), ""); 
                IQueryResult result = queryy.Execute(); 
     
    I received this error : "Persistent class 'DAL.Position' was specified as candidate, but the ResultSet does not contain any/all of the pk columns." 

    I tried the same type of query with another table with only one primary key and I didn't have the problem.  Is there a way to execute my query or is this a limitation of OpenAccess ?

    Regards,
    AlexD
  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 18 Feb 2009 Link to this post

    Hello Alex,
    You can easily retrieve list of positions by using the following code
                string queryString = "Select * From PositionExtent"
                IQueryResult result = scope.GetOqlQuery(queryString).Execute();  


    Greetings,
    PetarP
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
  4. Alex
    Alex avatar
    10 posts
    Member since:
    Feb 2009

    Posted 18 Feb 2009 Link to this post

    Thank you for your answer, it works for the problem I posted.  I tried to continue my evaluation by modifying my query to include an inner join and I saw that GetOqlQuery supports another format for its queries (which is fine as it use mapped classes).  I have 3 other questions :

    1) I have a legacy DLL that returns a select statement when a user performs a search and I need to be able to execute the following query :
    select Position.* 
    from Position 
        inner join Employee on Employee.PositionNo = Position.PositionNo 
        inner join Person on Person.PersonNo = Employee.PersonNo 
    where Person.FullName like 'A%' 
        and Position.PositionNo in (<select statement from legacy DLL>) 

    I tried a similar query with another table that has only one primary key (with GetSqlQuery) and it worked.  Is there a way to make this type of query works with my Position table (that has 2 primary keys) ?



    2) I have a legacy database that I cannot change that has the following schema :
        Table Position 
        (pk) OrganizationNo 
        (pk) PositionNo 
         
        Table StringNumber 
        (pk) StringNo 
     
        Table MediumStrings 
        (pk) StringNo 
        (pk) LanguageNo 
         StringData 
     
    The relation from Position to MediumStrings (to get the translated name for example) is :
    Position.PositionNo to StringNumber.StringNo and StringNumber.StringNo to MediumStrings.StringNo 

    OpenAccess was not able to find the relation between StringNumber and MediumStrings and when I tried to add it manually I saw that I needed to use both PK columns in MediumStrings for the relation between StringNumber and MediumStrings.  I modified the relation from MediumStrings to StringNumber to be a "one to many" and it worked but I still want to know if it is possible to create a relation without using all the PK columns or using a column that is not a PK).

    3) For reporting/performance reasons I create views, is it possible to map them in OpenAccess?

    Thanks !

  5. Urs
    Urs avatar
    15 posts
    Member since:
    Jun 2012

    Posted 19 Feb 2009 Link to this post

    Hi all

    I found the same problem when trying to execute a complex sql statement (which can not be ahndled by oql) to map to a class.
    I reduced complexity to "select * from table" and trying to map it to the class, so changing to oql is not a solution.
    I get the Exception: Persistent class ... was specified as candidate, but the ResultSet does not contain any/all of the pk columns.

    I get the message when I access the IQueryResult.Count property.
    If I don't touch it, (direct foreach on IQueryResult) the sql Statement is not executed at all and returning always no records.

    Thanks for help.

  6. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 19 Feb 2009 Link to this post

    Hello Urs,
    1. The returned column names must match the table column names exactly. Only then it is possible to use the data to create the object. OpenAccess does not find the right value descriptions in the result set.

    2. That is not possible. The references and collections have to contain the complete pk fields. But you can have a query in a property at the class and just execute that to fill the collection.

    3. Views can be used but the reverse engineering wizard can not detect them at the moment. The workaround is to have a db where all views are tables, reverse engineer that and during runtime use the real views. Because the needed sql is the same it will work.

    Greetings,
    Jan Blessenohl
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  7. Alex
    Alex avatar
    10 posts
    Member since:
    Feb 2009

    Posted 19 Feb 2009 Link to this post

    Hello,

    for #1, I tried to specify the column names in the query like this :
    IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope(); 
                string query = @"select Position.OrganizationNo as organizationNo, Position.PositionNo as positionNo
                                    from Position"
                IQuery queryy = scope.GetSqlQuery(query, typeof(Position), null); 
                dataGridView1.DataSource = queryy.Execute(); 

    But I still get "Presistent class 'Position' was specified as candidate, but the ResultSet does not contain any/all of the pk columns".  If I try the same code with another table having only one primary key, I don't have the problem.

    Can you show me some sample code/project that can load a list of entities that are based on a table with more than one primary key ? 

    Thanks !
    AlexD
  8. Alex
    Alex avatar
    10 posts
    Member since:
    Feb 2009

    Posted 19 Feb 2009 Link to this post

    Hello again,

    after some more testing, I found it wasn't a problem with multiple primary keys and I was able to replicate the problem in a separate project.  Can you tell me what I did wrong ? I need to use GetSqlQuery instead of GetOqlQuery to support complex SQL queries that cannot be created with OQL. You can find the test project here : http://www.nowol.org/files/TestOA.rar
    This is the last problem I have with OpenAccess and it is a showstopper before buying this product.

    Regards,
    AlexD
  9. Urs
    Urs avatar
    15 posts
    Member since:
    Jun 2012

    Posted 20 Feb 2009 Link to this post

    Dear Jan

    Thank you for the answer.
    I think it is not the answer to Alex and my problem, so I try to reexplain:

    1. I have a mapped class, say ClassX with a Guid pk (say ID) and some members (description)
    2. I need to create a complex sql statement. But for TESTING I create a very simple sql statement like:
        "SELECT * FROM tClassX"
    3. If this would work with mapping I would extend the sql statement...

    How has the SELECT Statment to look like?
    Do I need to alias coulmn names to the member variables?

    Thanks for help
  10. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 23 Feb 2009 Link to this post

    Hi AlexD,

    this looks like a bug on our side. I will fix that immediately, and will notify you when I'm done.

    Thanks for the reproducing test case.

    Sincerely yours,
    Thomas
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  11. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 23 Feb 2009 Link to this post

    Hello Urs & Alex,

    the problem is related to the returned order of the primary key fields. In the example database, there is PK field 1 returned, then some data, then PK field 2 (the server returned in alphabetical column order, not declaration order). When I changed the PK column names and prepended a underscore _  in both the database and the mapping file, the association between returned columns and expected fields worked as the PK columns stayed together; that might also be a temporary workaround in your cases.

    Regards,
    Thomas
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  12. Alex
    Alex avatar
    10 posts
    Member since:
    Feb 2009

    Posted 23 Feb 2009 Link to this post

    Hello Thomas,

    thank you for your answer.  I tried your workaround and it didn't work, I still get the same "any/all pk" error. 

    Will this issue be fixed in the next release in Q1 of 2009 ?  The workaround is not acceptable because it is a legacy database (I can do some modification but I cannot rename PKs) but we can wait until March for this project if the issue is corrected in the next release.  If it isn't fixed in the next release, we'll be forced to use our existing ORM (IdeaBlade's DevForce) instead of starting the new project with OpenAccess.

    (Someone else on my team opened a ticket about the problem : Support ID#192719)

    Sincerely yours,
    AlexD
  13. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 25 Feb 2009 Link to this post

    Hello Alex,

    yes, that will be fixed in the next build, I was just fixing it.

    Regards,
    Thomas
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  14. Steele
    Steele avatar
    96 posts
    Member since:
    Mar 2008

    Posted 15 Apr 2009 Link to this post

    Hi,
    Has this been fixed?  I am suffering the same error.
    Thanks,
    Steele.
  15. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 15 Apr 2009 Link to this post

    Hi Steele,

    yes, this is fixed in the latest build.

    Best wishes,
    Thomas
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
Back to Top
DevCraft banner