This is a migrated thread and some comments may be shown as answers.

Manual queries

13 Answers 248 Views

Development (API, general questions)

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

Alex asked on 17 Feb 2009, 10:16 PM

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

13 Answers, 1 is accepted

answered on 18 Feb 2009, 03:29 PM

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 !

answered on 19 Feb 2009, 03:47 PM

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.

answered on 19 Feb 2009, 04:34 PM

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

answered on 23 Feb 2009, 06:20 PM

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.

answered on 23 Feb 2009, 07:22 PM

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

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Manual queries

13 Answers, 1 is accepted