How do I create Master/Detail between SQLTables? 1

[delphiman]

Can anyone please tell me how to create a Master/Detail
relationship between two Tables in MyDataBase.gdb which have a common field P_NO between them?

In MyDatBase.gdb I have one table called "MST" hooked to TIBDataSet1 and another table called "DET" hooked to TIBDataSet2. Each are hooked to a grid.

In the SelectSQL Property of TIBDataSet2 I have the following code

SELECT *
FROM DET t2, MST t1
WHERE t2.P_NO = t1.P_NO.

But moving the pointer in the MST-grid has no effect on the DET-grid whilst I am expecting to see ONLY those records in DET-grid where the P_NO = P_NO pointed to in MST-grid.

What am I missing?

 

[svanels]

The datasource property of the detail query TIBDataSet2 must point to datesource of the Masterquery

Regards S. van Els
SAvanEls@cq-link.sr

 

[delphiman]

I have that.

But (as I say) ..
>moving the pointer in the MST-grid has no effect on the DET-grid whilst I am expecting
to see ONLY those records in DET-grid where the P_NO = P_NO pointed to in MST-grid.

It occurs to me that I need to do something else with

SELECT *
FROM DET t2, MST t1
WHERE t2.P_NO = t1.P_NO.

Because (it would seem) the above code has me faithfully seeing in the DET-grid any and all records in tblDET which indeed have a similar P_NO somewhere (anywhere) in tblMST.

Since this code does not isolate the event to ONLY the particular P_NO one is pointing to in tblMST at any given moment.

I suspect I need to register the contents of tblMSTP_NO with any movement (UponChange)in tblMST - into a further tbl (say tblTFRP_NO) which at all times has ONLY ONE RECORD.

But this seems a bit clumsy. Any further ideas?

 

[svanels]

You detail SQL has an error

Select * from det as t2
where t2.p_no =_no

_no you can find in the params property of the query, and set it to the apropriate data type.


_no is the variable that takes his value from the master query when you scroll in MST grid


Your master query should look like this:

Select p_no, field1, field2 etc. S. van Els
SAvanEls@cq-link.sr

 

[delphiman]

>Select *
from det as t2
where t2.p_no =_no

Delphi doesn't like this because I get an exception
"Token unknown - line2,char 10 as."

>p_no you can find in the params property of the query, and set it to the apropriate data type.

Thanks I've now done that.

>Your master query should look like this:

>Select p_no, field1, field2 etc.

My master query is a TClientDataSet which doesn't have a Property where I can use a query.

Where would I introduce such a query?

I might mention that my TClientDataSet is itself
dependant upon a DCOMConnection to my IBServer.

On the IBServer dataModule I have now also done as you suggested in TSQLDataSet where I now also have p_no in the params property of the query, and have set it to the apropriate data type. But once again I don't see a Property where I can introduce a query.

What am I missing?

 

[svanels]

Kick out as, I had looked at the paradox sql sintax

>Select *
from det t2
where t2.p_no =_no

I am not comfortable with client datasets (yet).

But the way i use to build my master-detail query is with 2 (or more) tables or queries.

How do you specify the fields from your master table (MST)? S. van Els
SAvanEls@cq-link.sr

 

[delphiman]

>Kick out as, I had looked at the paradox sql sintax
Yup ... and the : before the p_no has to go too.

>But the way i use to build my master-detail query is with 2 (or more) tables or queries.

I am familiar with that technique - if using Paradox tables. But then one is stuck with
the BDE and all the deployment problems that go with it - amongst other things.

I am putting together a 3-Tier Project - using Interbase server - to access a strictly SQL
Database (i.e. a .gdb file.) which ecapsulates numerous tables. The result being that one
does not have Objects with MasterSource and MasterFields Properties to work with.

Relationships have to be strictly SQLQuery-controlled.

>How do you specify the fields from your master table (MST)?

A little bit complicated.

FIRST.
I have an Application Server (an .exe file running on its own.) wherein the MST
TSQLDataSet is on a TRemoteDataModule (NOTE: NOT a TDataModule)

By RIGHT-clicking on this TRemoteDataModule one has (amongst others) a "View as Text"
option. Click on that and you get what's below - amongst other things. (To come back
RIGHTClick again - to select "View as Form".) A look at "Database=c:\h\MST.gdb" below
answers your question.

object SQLConnection1: TSQLConnection
Connected = True
ConnectionName = 'IBLocal'
DriverName = 'Interbase'
'Database=c:\h\MST.gdb'
'DriverName=Interbase'
'LocaleCode='
'Password=masterkey'
end

The other Objects on the TRemoteDataModule are
TDataSet Provider;
TSQLConnection.

SECONDLY
I have a ThinClient which has TWO TDataModules.

TDataModule ONE
TDCOMConnection - to connect the ThinClient to the above Server.
Also a TClientDataSet which also connects to the Server.

TDataModule TWO
TIBDataSet which has the above as the above TClientDataSet as its DataSource.

THIS is where I have the following in the SELECTSQL Property

SELECT *
FROM DET t2
WHERE t2.P_NO = P_NO

Perhaps you would like a model - sent zipped by seperate e-mail?

 

[svanels]

Yes I am interested. Which version of Delphi do you have?

About the use of the BDE, you could use Interbase Express (IBX) on the Interbase Palette. It make use of the API of interbase, no ado or bde needed. Since you have to install interbase client on your machine, this could be an option to go.

The : is for indicating to interbase that the rest is a variable.

Until now didn't had problems with installing the BDE, because I use instalshield to create a setup program for my application, or when I am lazy, I stick in the Delphi Disc, do a custom setup, install the the BDE only with the SQL driver, and do a manual configuration. In most cases you have to do some additional things like testing the connection to the server, host files and other OS related things.

The big advantage of the BDE is that if you need to switch from database (oracle, informix, sql-server) you only need to make the modifications to the alias settings.

I found ODBC connections giving much more trouble.

S. van Els
SAvanEls@cq-link.sr

 

[svanels]

I have received your mails, didn't install nothing yet because I am working (shift work). Your problem with auto increment fields, I answered in:
How do I make an field that is autoincremental in Interbase?
thread756-82472 in the Interbase Forum

In the coming days I will examine your files

Regards S. van Els
SAvanEls@cq-link.sr

 

[svanels]

Case Closed !!! (at least for using the IBDataset)

Some comparisons
BDE Structure
Database --> Table, Query, StoredProc --> Datasource --> Data Aware Control (DBGrid, DBedit, DBMemo)


IBX structure

IBDatabase --> IBTansaction --> IBTable, IBQuery, IBDataset, IBStoredProc --> Datasource --> Data Aware Control


So you got an extra chain (IBTtansaction) in the Link


You need 1 IBDatabase (Mydata) for the whole project, put it in your main Datamodule and all the transactions must point to this Database (The source of your multiple logins)

You need
MasterTransaction, DetailTransaction
DefaultDatabase = MyData

MasterDataset
Database = MyData
Transaction = MasterTransaction
SelectSQL = select p_no, field1, field2 from MST

MasterDatasource
Dataset = MasterDataset

DetailDataset
Database = MyData
Transaction = DetailTransaction
SelectSQL = select field1, field2, p_no DET where p_no =: p_no
Datasource = MasterDatasource

DetailDasource
Dataset = Detaildataset


The sql errors like as etc. where caused by the use of the SQL editor (works with the BDE).
In general I construct my queries first with the use of the SQL-editor, and after that I modify them.
Of course with the IBX components the SQL editor is not available, and you have to rely on pure SQL code.

I didn't experiment with the InsertSQL, or DeleteSQL, but it would be straight forward, but !!! define your referential constraints in your database, without them you can get lost fields (Details without a master)

Regards
S. van Els
SAvanEls@cq-link.sr

 

[delphiman]

>Case Closed !!! (at least for using the IBDataset)

Unfortunately not ... see below.

>You need 1 IBDatabase (Mydata) for the whole project,
>put it in your main Datamodule and all the transactions must point to this
>Database (The source of your multiple logins)

Can't happen.

Bearing in mind that the Application server amounts to a
seperate .exe file with IT's TRemoteDataModule, running on its own, wherein
the required DatabaseName is already set as follows:
object IBFatherDBase: TIBDatabase
DatabaseName = 'C:\h\PChild\Tables\PCHILD.GDB'
Params.Strings = ('user_name=SYSDBA')
end

So that (inter-alia) one does not need the IBTable referred to below in the DataModules
of the ThinClients.

>IBX structure
IBDatabase --> IBTansaction --> IBTable, IBQuery, IBDataset,
IBStoredProc --> Datasource --> Data Aware Control

The IBDataSet component merges IBQuery with IBUpdate (which you still need to add to
the above) into a single component. So one does not need IBQuery either. Unless (as
you indicate in other e-mail) you need to maintain compatibility with Delphi BDE
applications. A problem I don't have.

Because the IBDataSet component allows me to work with a live result obtained by
executing a select Query I prefer to use the IBdataSet which (for the purpose of
solving my problem) comes to the same thing!)

In fact I have the settings (which you suggest above) in the model which I sent you.
The only difference being that I connect the various DataModules (which I must have -
for integration purposes) by declaring them in the UsesClause of each as necessary.
Which essentially amounts to having them all on one DataModule - as you suggest.


My problem remaining that moving the Pointer in the Master doesn't have any effect on the
relevant Detail/s. (i.e. By pointing in grdFather to (say) Father 2 in tblMaster I
continue to see the Children of Father 1 - in grdChildren which is hooked to (Detail)
tblChildren.

I'm afraid you'll have to run the model which I sent you (exactly as it is) to see what I mean - and my
problem.

Regards and thanks once again for your interest.

TERRY

 

[svanels]

Give me a little time to experiment with the remote datamodule, but In the example I used only 2 IbDataSets, 2 transactions and 2 dbgrids, the classic master and detail setup on 1 form.
I didn't set up the tables yet, but it looks like your master and detail tables are on different forms. That way you have to find a way to notify the detail table, that the master trable has scrolled.

Keep in touch Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

Thanks a million for your time.

I reckon if you
Install the dataBase into c:\h\PChild\Tables
Explode the model I sent you into c:\Program files\PChild and use it as it is
... you'll save yourself a lot of valuable time and
see the problem. (Solution?) :=)

Thanks a million (again)!!

TERRY

 

[svanels]

Case almost closed, the select query is running (update, insert, refresh etc not tackled yet)

By the way nice drill down variant of the Master Detail

Modifications, deleted all databases except IBFatherDBase The ParentDM is the main Datamodule must be used by all forms.

Default Database = ParentDM.IBFatherDBase for all transactions

Database = ParentDM.IBFatherDBase for all IBDatasets


IBFatherDataset
SelectSQL = select P_NO, PNAME from PARENT
Database = IBFatherDBase

IBChildDataset
SelectSQL = select C_NO, CAKV, CDOB, CNAME, P_NO from CHILD where P_NO =_NO
Datasource = ParentDM.dtsFather

IBGChildDataset

SelectSQL = select C_NO, GAKV, GC_NO, GCDOB, GCNAME, P_NO from GCHILD where GAKV =:GAKV
DataSource = ChildrenDM.dtsChild

But the last one is not fine tuned yet I didn't fill in all the numbers

One login and at first level a perfect Master Detail Relation ship

Regards






Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

Great stuff! But the following won't work ..

SelectSQL = select C_NO, GAKV, GC_NO, GCDOB, GCNAME, P_NO from GCHILD where GAKV =:GAKV

GAKV is a Unique AutoIncremental field which serves solely to prevent key-violations in tblGCHILD. You should have
where C_NO =: C_NO

I corrected it - but it still gives me ALL the GrandChildren - regardless of which Father I am focussed on.

I have also tried the following without any success ..

select C_NO, GAKV, GC_NO, GCDOB, GCNAME, P_NO
FROM GCHILD t3, CHILD t2, PARENT t1
WHERE (t3.P_NO= t1.P_NO) AND (t3.C_NO = t2.C_NO)

 

[svanels]

What is the meaninhg of C_NO? ,is it unique? (I didn't fill it in).
Another thing only fathers will be in the parents?
In your last SQL fragment you try to combine data from 3 tables, this will give problems when you try to update, or delete.

It must be data from the Grandchildren table, and the parameters must come from the Children table
_no & :c_no ? Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

>What is the meaninhg of C_NO?

It is the number of the child of that Father.

> ,is it unique?

No it can't possible be. Because then you will have the first child of Father 2 HAVING to being Child 2 (to avoid a key-violation) ... which is obviously not acceptable. And is why I use CAkv to be the Unique field in that table.. to obviate key-violations when you have 1... whatever repeated anywhere in C_NO anywhere in the table.

Whilst GAkv performs the same function in tblGCHILD and
GGAkv does it in tblGGChild.

I don't think you will get very far with coming up with a solution unless and until you run the .exe file which I sent you - which amounts to a perfectly working model of a 3-Tier system. Complete with ApplicationServer, ThinClient and a DataBase (PChild.gdb) - which must be put into c:\h\PChild\Tables.

(You MUST run the ApplicationServer Project (from within the environment) at least ONCE in order that it becomes registered on your system as an AutomationServer - thus making it available to client applications.)

Examine the script for the database which I sent you and you'll see all the fields that are unique. OR you could use the Metadata tab in IBConsole.

 

[svanels]

I ran the exe file, but I have no sample data, so I experimented punching some things.
Why don't you make a keyfield combination of P_no and C_No, secondary keyfield on C_No. That way you do not need CAkv

The same way as it is in an Orders x Items table

you would have 2 - 1, 2 - 2, ...3 - 4
Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

I'll send you a DataBase with data in it per other e-mail.

(I thought I had!!) ???

 

[svanels]

It was the script Steven van Els
SAvanEls@cq-link.sr