Board index » delphi » Master-detail relationship on a multi-field index

Master-detail relationship on a multi-field index

Using Delphi 6.0 profeessional (with Bugfix #2),
Firebird 1.0 (lastest version) for Windows XP,
ASCII Character Set, Dialect 3,
I create a database as follows:

CREATE TABLE "VENDOPTS"
(
  "VEND_ID"  SMALLINT
    NOT NULL,
  "OPTION"  SMALLINT
    NOT NULL,
  PRIMARY KEY ("VEND_ID","OPTION")
);

CREATE TABLE "VENDCOPT"
(
  "VEND_ID"  SMALLINT
    NOT NULL,
  "OPTION"  SMALLINT
    NOT NULL,
  "COLLECT_ID"  SMALLINT
    NOT NULL,
  PRIMARY KEY ("VEND_ID", "OPTION", "COLLECT_ID")
);

I connect to the database as follows:

SQLDataSet1 -> Provider1 -> ClientDataSet1 -> DataSource1 -> DBGrid1

Where SQLDataSet1's command text is "Select * from VENDOPTS".
I create persistent fields for both the SQLDataSet1 and the ClientDataSet1,
making sure to set pfInKey = True for VEND_ID and OPTION.
The DBGrid1 appears correctly when the ClientDataSet1 is set to ACTIVE.

On the same form, I then create another set of components as follows:

SQLDataSet2 -> Provider2 -> ClientDataSet2 -> DataSource2 -> DBGrid2

Where SQLDataSet2's command text is "Select * from VENDCOPT".
I create persistent fields for both the SQLDataSet2 and the ClientDataSet2,
making sure to set pfInKey = True for VEND_ID, OPTION, and COLLECT_ID.
The DBGrid2 appears correctly when the ClientDataSet2 is set to ACTIVE.

If I set the MasterSource of ClientDataSet2 to DataSource1,
and then click on the MasterField ellipsis to add VEND_ID from both tables,
the DBGrid2 shows the proper master-detail relationship as I move from
record to record in DBGrid1. But what I really need to do is to add
both VEND_ID and OPTION from each table. When I try this,
I get the error message:

  Unknown token - line 1, char 47
  OPTION

and I cannot open the ClientDataSet2.

I suspect that anyone replyng to this message will tell me to use
SQL monitor to determine the SQL command that is being sent to the
server. I would love to do this but I have no idea how to access or
create an SQL monitor. I have read previous discussions of this here
on this list, and I have even posted a question about it (with no reply),
and so I guess I will need detailed instructions before I will be able
to use SQL monitor.

If anyone can help me to establish a master-detail relationship on a
multi-field index,
or if anyone can help me with SQL monitor, I would be grateful.

Happy Easter,
Paul

 

Re:Master-detail relationship on a multi-field index


For the tSQLMonitor question:

The easyiest way is to register it yourself:
===================================
unit MysqlMonitor;
interface
uses
  Windows, Messages, SysUtils, Classes, SQLExpr;
type
  TMysqlMonitor = class(tsqlMonitor)
  end;
procedure Register;
implementation
procedure Register;
begin
  RegisterComponents('test', [TMysqlMonitor]);
end;
end.
===================================
Copy and paste this in a new unit, and add it ti the componentpallet.

Greets,
Henk

"Paul Hooverson" <pa...@easychairsoftware.com> a crit dans le message de
news: 3ca4e1e2$1_1@dnews...

Quote
> Using Delphi 6.0 profeessional (with Bugfix #2),
> Firebird 1.0 (lastest version) for Windows XP,
> ASCII Character Set, Dialect 3,
> I create a database as follows:

> CREATE TABLE "VENDOPTS"
> (
>   "VEND_ID"  SMALLINT
>     NOT NULL,
>   "OPTION"  SMALLINT
>     NOT NULL,
>   PRIMARY KEY ("VEND_ID","OPTION")
> );

> CREATE TABLE "VENDCOPT"
> (
>   "VEND_ID"  SMALLINT
>     NOT NULL,
>   "OPTION"  SMALLINT
>     NOT NULL,
>   "COLLECT_ID"  SMALLINT
>     NOT NULL,
>   PRIMARY KEY ("VEND_ID", "OPTION", "COLLECT_ID")
> );

> I connect to the database as follows:

> SQLDataSet1 -> Provider1 -> ClientDataSet1 -> DataSource1 -> DBGrid1

> Where SQLDataSet1's command text is "Select * from VENDOPTS".
> I create persistent fields for both the SQLDataSet1 and the
ClientDataSet1,
> making sure to set pfInKey = True for VEND_ID and OPTION.
> The DBGrid1 appears correctly when the ClientDataSet1 is set to ACTIVE.

> On the same form, I then create another set of components as follows:

> SQLDataSet2 -> Provider2 -> ClientDataSet2 -> DataSource2 -> DBGrid2

> Where SQLDataSet2's command text is "Select * from VENDCOPT".
> I create persistent fields for both the SQLDataSet2 and the
ClientDataSet2,
> making sure to set pfInKey = True for VEND_ID, OPTION, and COLLECT_ID.
> The DBGrid2 appears correctly when the ClientDataSet2 is set to ACTIVE.

> If I set the MasterSource of ClientDataSet2 to DataSource1,
> and then click on the MasterField ellipsis to add VEND_ID from both
tables,
> the DBGrid2 shows the proper master-detail relationship as I move from
> record to record in DBGrid1. But what I really need to do is to add
> both VEND_ID and OPTION from each table. When I try this,
> I get the error message:

>   Unknown token - line 1, char 47
>   OPTION

> and I cannot open the ClientDataSet2.

> I suspect that anyone replyng to this message will tell me to use
> SQL monitor to determine the SQL command that is being sent to the
> server. I would love to do this but I have no idea how to access or
> create an SQL monitor. I have read previous discussions of this here
> on this list, and I have even posted a question about it (with no reply),
> and so I guess I will need detailed instructions before I will be able
> to use SQL monitor.

> If anyone can help me to establish a master-detail relationship on a
> multi-field index,
> or if anyone can help me with SQL monitor, I would be grateful.

> Happy Easter,
> Paul

Re:Master-detail relationship on a multi-field index


A detail dataset select statement should always contain a parameterized
'Where' clause on the link fields.

Select * from VENDCOPT Where VEND_ID = :VEND_ID and OPTION = :OPTION

--
Dave Rowntree

Re:Master-detail relationship on a multi-field index


Or even chorter:

unit MysqlMonitor;
interface
uses
  Classes, SQLExpr;
procedure Register;
implementation
procedure Register;
begin
  RegisterComponents('test', [TsqlMonitor]);
end;
end.

Re:Master-detail relationship on a multi-field index


Quote
> A detail dataset select statement should always contain a parameterized
> 'Where' clause on the link fields.

> Select * from VENDCOPT Where VEND_ID = :VEND_ID and OPTION = :OPTION

I tried using the CommandText as you suggested, but I still get the error
message. When created a parameterized CommandText query, is there anything
else that I must do in addition to adding the "where VEND_ID = :VEND_ID and
OPTION = :OPTION" clause?

I have also tried to do the master-detail relationship with multiple-field
keys using the Interbase 6.0 (rather than Firebird 1.0) that came with
Delphi. I get the same errors with that also.

Thanks,
Paul

Re:Master-detail relationship on a multi-field index


Quote
> I tried using the CommandText as you suggested, but I still get the error
> message. When created a parameterized CommandText query, is there anything
> else that I must do in addition to adding the "where VEND_ID = :VEND_ID
and
> OPTION = :OPTION" clause?

You need to pull the params from the detail DSP.DataSet to the detail CDS.
If you right click on the detail CDS, then select Fetch Params, that will
create the params for you on the detail CDS.
--
Dave Rowntree

Re:Master-detail relationship on a multi-field index


Quote
"Dave Rowntree" <da...@brookswood.co.uk> wrote in message

news:3ca6ee1b_1@dnews...

Quote
> > I tried using the CommandText as you suggested, but I still get the
error
> > message. When created a parameterized CommandText query, is there
anything
> > else that I must do in addition to adding the "where VEND_ID = :VEND_ID
> and
> > OPTION = :OPTION" clause?

> You need to pull the params from the detail DSP.DataSet to the detail CDS.
> If you right click on the detail CDS, then select Fetch Params, that will
> create the params for you on the detail CDS.
> --
> Dave Rowntree

Thanks to Henk, I seem to have an SQLMonitor component. Now, after
righ-clicking on my ClientDataSet to fetch the parameters, I still get the
same error message. Can anyone tell from the SQL Monitor output why I am
still getting the following error message:

token unknown - line 1, char 48
OPTION

SQL Monitor output:

INTERBASE - isc_attach_database
INTERBASE - isc_dsql_allocate_statement
INTERBASE - isc_start_transaction
select * from VENDOPTS
INTERBASE - isc_dsql_prepare
INTERBASE - isc_dsql_describe_bind
INTERBASE - isc_dsql_execute
INTERBASE - isc_dsql_fetch
INTERBASE - isc_dsql_allocate_statement
SELECT 0, '', '', A.RDB$RELATION_NAME,
 A.RDB$INDEX_NAME,
 B.RDB$FIELD_NAME,
 B.RDB$FIELD_POSITION,
 '', 0, A.RDB$INDEX_TYPE, '',
 A.RDB$UNIQUE_FLAG,
 C.RDB$CONSTRAINT_NAME,
 C.RDB$CONSTRAINT_TYPE
 FROM RDB$INDICES A,
 RDB$INDEX_SEGMENTS B
 FULL OUTER JOIN RDB$RELATION_CONSTRAINTS C ON
 A.RDB$RELATION_NAME = C.RDB$RELATION_NAME AND
 C.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
 WHERE (A.RDB$SYSTEM_FLAG <> 1 OR A.RDB$SYSTEM_FLAG IS NULL) AND
 (A.RDB$INDEX_NAME = B.RDB$INDEX_NAME) AND
 (A.RDB$RELATION_NAME = UPPER('VENDOPTS'))
 ORDER BY RDB$INDEX_NAME
INTERBASE - isc_dsql_prepare
INTERBASE - isc_sqlcode
INTERBASE - isc_dsql_free_statement
INTERBASE - isc_dsql_fetch
INTERBASE - isc_dsql_fetch
INTERBASE - isc_dsql_fetch
INTERBASE - isc_dsql_fetch
INTERBASE - isc_dsql_fetch
INTERBASE - isc_dsql_fetch
INTERBASE - isc_commit_retaining
INTERBASE - isc_dsql_free_statement
INTERBASE - isc_dsql_allocate_statement
select * from VENDCOPT where VEND_ID =  ?  and OPTION =  ?
INTERBASE - isc_dsql_prepare
INTERBASE - isc_sqlcode
INTERBASE - isc_commit_retaining
INTERBASE - isc_dsql_free_statement
INTERBASE - isc_dsql_free_statement

Thanks,
Paul

Re:Master-detail relationship on a multi-field index


Solved my own question. It seems I have to enclose the OPTION fieldname in
quotation marks. Everything seems to work fine now. Thanks to Henk and Dave.

Paul

Quote
> > > I tried using the CommandText as you suggested, but I still get the
> error
> > > message. When created a parameterized CommandText query, is there
> anything
> > > else that I must do in addition to adding the "where VEND_ID =
:VEND_ID
> > and
> > > OPTION = :OPTION" clause?

> > You need to pull the params from the detail DSP.DataSet to the detail
CDS.
> > If you right click on the detail CDS, then select Fetch Params, that
will
> > create the params for you on the detail CDS.
> > --
> > Dave Rowntree

> Thanks to Henk, I seem to have an SQLMonitor component. Now, after
> righ-clicking on my ClientDataSet to fetch the parameters, I still get the
> same error message. Can anyone tell from the SQL Monitor output why I am
> still getting the following error message:

> token unknown - line 1, char 48
> OPTION

> SQL Monitor output:

> INTERBASE - isc_attach_database
> INTERBASE - isc_dsql_allocate_statement
> INTERBASE - isc_start_transaction
> select * from VENDOPTS
> INTERBASE - isc_dsql_prepare
> INTERBASE - isc_dsql_describe_bind
> INTERBASE - isc_dsql_execute
> INTERBASE - isc_dsql_fetch
> INTERBASE - isc_dsql_allocate_statement
> SELECT 0, '', '', A.RDB$RELATION_NAME,
>  A.RDB$INDEX_NAME,
>  B.RDB$FIELD_NAME,
>  B.RDB$FIELD_POSITION,
>  '', 0, A.RDB$INDEX_TYPE, '',
>  A.RDB$UNIQUE_FLAG,
>  C.RDB$CONSTRAINT_NAME,
>  C.RDB$CONSTRAINT_TYPE
>  FROM RDB$INDICES A,
>  RDB$INDEX_SEGMENTS B
>  FULL OUTER JOIN RDB$RELATION_CONSTRAINTS C ON
>  A.RDB$RELATION_NAME = C.RDB$RELATION_NAME AND
>  C.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
>  WHERE (A.RDB$SYSTEM_FLAG <> 1 OR A.RDB$SYSTEM_FLAG IS NULL) AND
>  (A.RDB$INDEX_NAME = B.RDB$INDEX_NAME) AND
>  (A.RDB$RELATION_NAME = UPPER('VENDOPTS'))
>  ORDER BY RDB$INDEX_NAME
> INTERBASE - isc_dsql_prepare
> INTERBASE - isc_sqlcode
> INTERBASE - isc_dsql_free_statement
> INTERBASE - isc_dsql_fetch
> INTERBASE - isc_dsql_fetch
> INTERBASE - isc_dsql_fetch
> INTERBASE - isc_dsql_fetch
> INTERBASE - isc_dsql_fetch
> INTERBASE - isc_dsql_fetch
> INTERBASE - isc_commit_retaining
> INTERBASE - isc_dsql_free_statement
> INTERBASE - isc_dsql_allocate_statement
> select * from VENDCOPT where VEND_ID =  ?  and OPTION =  ?
> INTERBASE - isc_dsql_prepare
> INTERBASE - isc_sqlcode
> INTERBASE - isc_commit_retaining
> INTERBASE - isc_dsql_free_statement
> INTERBASE - isc_dsql_free_statement

> Thanks,
> Paul

Other Threads