Master Detail Relation "WHERE" error

Master Detail Relation "WHERE" error

Postby chris901 » Sun 16 Oct 2016 22:06

Hello,

I am using this database:
http://www.postgresqltutorial.com/postgresql-sample-database/

I installed the database like this:
http://www.postgresqltutorial.com/load-postgresql-sample-database/


I want to build a master-detail relation like this:

Master PgSqlDataTable: http://i.imgur.com/uQzryF9.png

Detail PgSqlDataTable: http://i.imgur.com/rZKPBxl.png

Setup of the Relation: http://i.imgur.com/u2RTZ6q.png

When I now want to CreateColumns on the Child Table I get the following error: http://i.imgur.com/1Z13fjw.png

I am not 100% but I think I got this working with a previous version of dotConnect.
I know that I once got master-detail-realtion working. What is wrong here?
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Master Detail Relation "WHERE" error

Postby Pinturiccio » Wed 19 Oct 2016 14:42

The master-detail relation can be used only for tables that have a relation between them on the server side. For example if a parent table will have a select query like "select * from film" and a child table will have a select query like "select * from film_actor", you can configure a master-detail relation between such tables, and CreateColumns won't throw the error.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: Master Detail Relation "WHERE" error

Postby chris901 » Wed 19 Oct 2016 16:55

I just found the reason for this bug. This took me some days to figure out.

If you use a
Code: Select all
;
in your child table select statement at the end the WHERE error gets thrown.

When you remove the
Code: Select all
;
symbol the error won't appear. Could you please fix this?


E.g:

Works:
Code: Select all
select * from film_actor fa
join actor a on a.actor_id = fa.actor_id



Error:
Code: Select all
select * from film_actor fa
join actor a on a.actor_id = fa.actor_id;
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Master Detail Relation "WHERE" error

Postby Pinturiccio » Tue 25 Oct 2016 12:45

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: Master Detail Relation "WHERE" error

Postby chris901 » Sat 29 Oct 2016 08:11

Just noticed this also happens if there is a commented line at the end of the statement

E.g:
Code: Select all
select * from movies
-- todo check fields


Then it throws "cannot establish master/detail relation" error.
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Master Detail Relation "WHERE" error

Postby Pinturiccio » Tue 01 Nov 2016 14:14

We have fixed the bug with creating columns for a child table in a master-detail relation when a select command in the child table ends with a semicolon. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

chris901 wrote:Just noticed this also happens if there is a commented line at the end of the statement

This is the designed behaviour. dotConnect for PostgreSQL does not support commented lines in a child select query. Supporting them would require syntax parsing of select statements, which would reduce performance.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: Master Detail Relation "WHERE" error

Postby Pinturiccio » Mon 12 Dec 2016 10:56

New version of dotConnect for PostgreSQL 7.7 is released!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=34690
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL