Master Detail Relation "WHERE" error

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Master Detail Relation "WHERE" error

Post by chris901 » Sun 16 Oct 2016 22:06

Hello,

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

I installed the database like this:
http://www.postgresqltutorial.com/load- ... -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?

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Master Detail Relation "WHERE" error

Post by 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.

chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Master Detail Relation "WHERE" error

Post by 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;

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Master Detail Relation "WHERE" error

Post by 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.

chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Master Detail Relation "WHERE" error

Post by 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.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Master Detail Relation "WHERE" error

Post by 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: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Master Detail Relation "WHERE" error

Post by 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/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34690

Post Reply