Database: existing External - Joining tables

wings

How do you join tables of an external database?

I have a header table and 3 details tables that I want to display as tabs on the header form. As the discover attributes dosn't have the ability to indicate how the tables join, how is it done?

Also you can't set the order of the primary key for a multi column key or dosn't this matter?

aware_support

You should define a VIEW in the external database that joins the tables. Then you should specify the name of this view when defining an external object, rather than specifying the name of the table.

The order of primary keys shouldn't matter.

wings

If you use a multi table view you can't update the data.

The table I'm looking at has 8 other related tables and I want to be able to add tabs to the form to show the related data as an aware table does.

From what your saying this isn't possible?

aware_support

If you make the view updatable you'll be able to both update the data and display it as a table. Not sure what tabs have to do with it.

Another approach is to define several external objects - one per table and then define a standard object with references and shortcuts to the external objects. Then have shortcuts displayed by a query. You won't be able to filter on shortcuts, though.

wings

If you make the view updatable you'll be able to both update the data and display it as a table.

For an informix view to be naturally updatable (without the need to write triggers for it), it must be a subset of a single table only. Other databases may be different such as DB2 which allows an updatable multi table view.

Not sure what tabs have to do with it.

I want to be able to display the data as:

Main Table
-------|Tab 1|---|Tab 2|---|Tab 3|----

where the tabs are a table of items of the related table to the main table.

Basic awareim functionality.

A View is not practical and would be imposible to display on a form. The related tables will have 1000's of rows.

aware_support

You can try doing something like this:

a) Define a VIEW for multiple tables and an Aware IM object that maps to this view
b) Define Aware IM objects for each of the tables in the multi-view
c) Define an umbrella native Aware IM object that will have references to all objects in b). You will have a form with tabs for this object. Each tab will show the corresponding related objects. All editing will be here.
d) You define a query on the object that maps to View and a process to edit such an object. Your aim in this process is to find native umbrella object in c) that corresponds to the view object. Finding such object should be possible if you keep some unique value in the native object that exists in the view object. So by using this unique value in view you can find the native object that has this unique value.

I hope you understand what I am talking about (I don't

🙂 )

wings

struth, I'll give it a go :roll:

wings

We have tried to link to two tables in an external informix database using a driver that we know works correctly.

The first table retrieves data and its definition is as follows:

create table postal
(
nar_num integer not null ,
mdu_ref char(2) not null ,
mdu_acc char(21) not null ,
pst_cde char(6) not null ,
pst_ad1 char(40),
pst_ad2 char(40),
pst_ad3 char(40),
cre_dte_tme datetime year to second not null ,
mod_dte_tme datetime year to second not null ,
mod_opr integer not null ,
dpid char(10),
barcode char(70)
);

the second table does not retrieve any data. Its definition is as follows:

create table zone
(
rte_zne char(2) not null ,
rte_yer smallint not null ,
rte_cls char(2) not null ,
rte_cde smallint not null ,
rte_dif smallint not null ,
rte_flg char(1),
occ_flg char(1),
cre_dte_tme datetime year to second not null ,
mod_dte_tme datetime year to second not null ,
mod_opr integer not null
)

Both table contain data.

We can't see any obvious differences in the definition of the tables, so we are at a loss to know why one works and the other doesn't.

Any ideas???