I am having a problem with LLBL recognizing relationships between parent table and synonym children.

The child relations work just fine for table to table name. However, LLBL doesn't seem to notice that the synonyms in the database actually refer to tables that are children to the parent. Therefore, the generated code doesn't let me traverse from parent table entity to child rows.

Using: LLBLGen Pro v 1.0.2005 Final Oracle 10g express database server Oracle 10g DBDriver (1.0.04282006) ASP.NET 2.0

Thanks, Darren

Please check the following thread, that might be related to your problem: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3218

This isn't my problem. I can create an entity on the c# side of the table using the synonym identifier. The synonyms are objectivied just fine. However the problem is a matter of relating parent entity to children, both in the llbl designer and c# generated code, where children are identified by synonym.

In the LLBL designer, when creating (for example) a typed list, starting with the parent table, the rest of the tables (synonym of the child tables) are greyed out, illustrating that llbl doesn't recognize the relationships.

And conversely, it does recognize the actual (non-synonym) table name as a child.

Because of my system architecture, I must be able to use synonyms.

When you have a table Foo and a synonym SynFoo, the table Foo is added to the project as SynFoo. If you have another table Bar and a synonym SynBar and that table Bar has a relation with Foo, the relation should appear in the project between SynBar and SynFoo.

When you retrieve the schema from the database, (create new project, refresh catalog) do you see any remarks in the Application output window which suggest a relation is dropped? Do you see both synonym tables in your project?

Also, if you have Foo and SynFoo both in your project, it will be added just once: as Foo.

Also if you don't find those relations defined in the designer, you may create them manually. Please refer to the LLBLGen Pro documentation manual "Using the designer -> Adding custom relations".

1. I do not see any remarks about relationships being dropped.
2. I see both synonym tables in project. (designer and c# side)

Note: I do not want or need the actual child table in my projects, just the synonyms for them.

Here is the refresh catalog results:

All "X" tables are synonyms. I included one of the actual tables it has "Z". The parent table is recordheader.

Migration of project elements to catalog 'qqqqqqq' Actions performed on Entities Entity 'X12A10012A12AC89' Entity migrated. Entity 'X12A10012A12AC4' Entity migrated. Entity 'X12A10012A12AC52' Entity migrated. Entity 'X12A10012A12AC99' Entity migrated. Entity 'X12A10012A12AC31' Entity migrated. Entity 'Flights' Entity migrated. Entity 'X12A10012A12AC42' Entity migrated. Entity 'X12A10012A12AC48' Entity migrated. Entity 'X12A10012A12AC1' Entity migrated. Entity 'X12A10012A12AC2' Entity migrated. Entity 'X12A10012A12AC98' Entity migrated. Entity 'X12A10012A12AC97' Entity migrated. Entity 'X12A10012A12AC46' Entity migrated. Entity 'X12A10012A12AC96' Entity migrated. Entity 'X12A10012A12AC94' Entity migrated. Entity 'X12A10012A12AC95' Entity migrated. Entity 'X12A10012A12AC34' Entity migrated. Entity 'X12A10012A12AC35' Entity migrated. Entity 'X12A10012A12AC93' Entity migrated. Entity 'X12A10012A12AC91' Entity migrated. Entity 'X12A10012A12AC92' Entity migrated. Entity 'Recordheader' Entity migrated. Entity 'X12A10012A12AC104' Entity migrated. Entity 'X12A10012A12AC105' Entity migrated. Entity 'X12A10012A12AC109' Entity migrated. Entity 'X12A10012A12AC113' Entity migrated. Entity 'X12A10012A12AC49' Entity migrated. Entity 'X12A10012A12AC101' Entity migrated. Entity 'X12A10012A12AC102' Entity migrated. Entity 'X12A10012A12AC100' Entity migrated. Entity 'X12A10012A12AC106' Entity migrated. Entity 'X12A10012A12AC107' Entity migrated. Entity 'X12A10012A12AC110' Entity migrated. Entity 'Z390V350C110' Entity migrated. Entity 'X12A10012A12AC103' Entity migrated. Entity 'X12A10012A12AC112' Entity migrated. Entity 'X12A10012A12AC108' Entity migrated. Entity 'X12A10012A12AC47' Entity migrated. Entity 'X12A10012A12AC117' Entity migrated. Entity 'X12A10012A12AC111' Entity migrated. Entity 'X12A10012A12AC114' Entity migrated. Entity 'X12A10012A12AC22' Entity migrated. Entity 'X12A10012A12AC21' Entity migrated. Actions performed on Action SP Calls Actions performed on Typed Lists Typed List 'Flights' Actions performed on Typed Views Actions performed on Retrieval SP Calls

Walaa.

I actually saw that and considered it, however, my large volume of tables make that an awkward solution. The schema changes a lot, too.

Thanks for the suggestion though.

Though is there a FK constraint defined between the table you have a synonym of (so not the synonym, but the real table), and the parent table? Because that relation should be picked up.

Or did you have an FK between the synonym and the parent table? Because those aren't picked up, only the relations between teh real tables

Yes, I have a FK constraint defined between the real child table and the parent table. That relationship is picked up. However, synonym for the child table doesn't relate to "real" parent table in llbl.

Puromtec wrote:

Yes, I have a FK constraint defined between the real child table and the parent table. That relationship is picked up. However, synonym for the child table doesn't relate to "real" parent table in llbl.

I think we're talking about different things, because I don't understand what you mean.

the thing is, LLBLGen Pro doesn't know anything about synonyms: the driver for Oracle finds the synonyms but reports them back as normal tables. So if you have the situation: TableParent, TableChild and SynonymChild which is a synonym of TableChild, and you have a relation between TableParent and TableChild, LLBLGen Pro will see TableParent and SynonymChild, given that tablechild isn't in the schema. (why else use synonyms if the table is in the same schema). Then, the relation between TableChild and TableParent is shown in LLBLGenPro as the relation between TableParentEntity and SynonymChildEntity.

What I don't understand, or miss, is what exactly is in your schema? Do you have all tables and all synonyms in one schema? Because that's completely unclear to me.

Yes, all parent tables (real), child tables (real) and child table synonyms are in the same schema.

I need to do this because of the complex and dynamic nature of the application/system. And, I need to use llbl to only use the synonym version of the child tables. (my real child table names have sequence/ids embedded, so they can change and synonyms can always give me the latest version of the (real) table schema.)

Having llbl generated api use synonyms, allows me to update real table names and prevents llbl generated code from breaking.

There is only one oracle schema/user.

OK, then the following is IMHO a better approach: - keep your tables in the current schema, - create a new schema with only your synonyms. - use the schema with the synonyms for the project.

Synonyms are indeed handy when the real schema changes a lot, though it's often also then done with a separate schema with solely the synonyms.

Could you try with just the parent/child synonyms in a separate schema, if this works for you? My testprojects on oracle use a mix of tables and synonyms though the synonyms are based on tables in other schemas, and when I then add that other schema as well to the catalog (e.g HR and SCOTT, where HR has synonyms on SCOTT's tables), the entities on the real tables are present, not on the synonyms.

I understand what you are suggesting for me to do. Though I do not think that it will address my problem.

I want to be able to implement analysis of the data in the database using entities / variable names that remain the same. (natually)

Because my underlying table names (table schema for that matter) will be continually blown away and rebuilt with different table names, I've created a process to "create or replace" the synonym's that point to the newly created underlying (real) tables.

I would like the rest of the development of this application to use these synonyms in compiled code. In other words, no where in the analysis code do I want to refer to the (real) table names.

Example:

//somewhere in Analysis code

//GOOD!!!! //XHelloSyn is synonym to a real table int idb = flightsEntity.Recordheader[0].XHelloSyn.Idb123;

//BAD!!!!!! //Z350V279C79 is the real child table name (which can change over time) //int idb = flightsEntity.Recordheader[0].Z350V279C79.Idb123;

....and thanks for taking time to work out my problem.

side note: i've successfully added relationships manually (walaa's posted suggestion)

I'll see if I can reproduce it here with a set of synonyms on tables in the same schema. Please correct me if I'm wrong (as you talk about relations between parentTABLE and child SYNONYM ), I assume it's true that you have all relations defined on the tables and all tables you want to work with all have synonyms.

You are correct.

I did another sanity check: flight.Recordheader[0].Z390V350C110

Z... is a real table name. It's synonym is set properly too.

Thanks

I wonder why no one else has encounterd this situation.

If you can determine a fix, I'll be in a much better position with this project.

-Darren

I'll setup a test today (this morning) on Oracle 9i and see what it gives for results. I'll then report my test results here.

I can't reproduce it. I created in SCOTT 2 synonyms: SCOTT.SYNDEPT, synonym for SCOTT.DEPT and SCOTT.SYNEMP, synonym for SCOTT.EMP

I created a project on SCOTT. I added Synemp and Syndept as entities to my project. There's a relation between them, namely the relation dept-emp.

Am I doing something different than you're doing?

Ok, to answer the question... YES, i am doing something a little different.

However, I've tweaked my approach to match your simple example and it works.

The problem was that I used the real table name for the parent, and the children were synonym names.

If I used a synonym for both parent and children tables, the relationships appear to be correct in the designer and the resulting generated project.

i.e: I could not relate SCOTT.DEPT to SCOTT.SYNEMP, but syndept to synemp does relate.

Thanks for the help here.

Aha Indeed I then misunderstood you, but I'm glad it's solved now