Manual queries in Data Access, formerly OpenAccess ORM Development (API, general questions)

Alex

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

PetarP

Admin

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.

Alex

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 !

Urs

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.

Jan Blessenohl

Admin

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.

Alex

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

Alex

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

Urs

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

Thomas

Admin

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.

Thomas

Admin

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.

Alex

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

Thomas

Admin

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.

Steele

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.

Thomas

Admin

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.