Very Computer

Hello all, I am very stuck and need help desperately!!  What I need to do in a
nutshell is this:  I am trying to use a Tquery to fill a grid.  The values to
fill the grid are from  table X and need to be filtered based on a value in
table Y which is related to table X by the field Name.  I had intended to use
a nested Select which works fine if you don't plan to use the result as a Live
Set.  I need to use live data for posting, changing, etc.  I am even trying to
do this with InfoPower tools without success. There has got to be a way to do
this (afterall, I am working with a 'relation database'). Our database is on a
MS SQL server.  Please help, alot of the things I had planned for my project
are based on being able to access data in this manner.  Any and ALL help
greatly appreciated. TIA

Kelly Grigg

G'Day Kelly,

The only way I got this going was to have TTables of the base tables,
and using the ID from the joined query I altered added etc records.
This works, but can be tiresome.

As in Paradox, you can only query the master table and then you must
build a form around the result.  You don't need to filter the detail
table as the result of the live query will do that for you!

ttfn

John

: Hello all, I am very stuck and need help desperately!!  What I need to do in a
: nutshell is this:  I am trying to use a Tquery to fill a grid.  The values to

Very few tools allow modifications to SELECTs over multiple tables. The
standard workaround is to do a read-only multi-table SELECT that retrieves
the pkeys of the appropriate recordset, and then do multiple single-table
editable SELECTs on the proper pkeys.

--


Fax: +358 0 33 88 22                                    " Why me? "

I run into exactly the same problem and I am still looking for a solution. I believe that the two previous
responses misunderstood the problem. It is not an issue of master-detail. It is an issue of multiple table
queries when you want a live result. At least Interbase, and apparently MS SQL Server, open the secondary
tables as read only and the BDE raises an exception. One solution I am exploring is to use InfoPower's
TwwTables filtering the query through the OnFilter event. For example, if you have a two table query, you
can open both tables and have an OnFilter event in the main table which would return TRUE if the record key
is found in the second table (and maybe other conditions). The problem with this approach is that probably
slows down the query considerably in addition to the fact that every single row must be passed through the
filter thus undermining the built-in optimization of the SQL server. Let me know if you find another
solution. Thank you.

--
Gabriel Beccar-Varela

_____

[znip]

Quote:

>I run into exactly the same problem and I am still looking for a solution. I believe that the two previous
>responses misunderstood the problem. It is not an issue of master-detail. It is an issue of multiple table
>queries when you want a live result.

[znip]

Sorry, but I must have missed some postings. I fail to see that the
original question doesn't address a master-detail problem. It's amazing
what one could do with to tables in a master detail relationship. I wonder
if Kelly has tried something in that direction as a solution to his problem?
Otherwise maybe you could e-mail me Kelly and you and I maybe can work out
a solution together?

--
    _/_/_/_/_/_/_/_/_/_/_/_/
   _/    _/_/           _/
  _/_/_/_/_/_/_/     _/
 _/      _/       _/
_/      _/_/_/_/_/_/_/_/

For me, and probably for you Kelly, this problem of non-updateable multi-table
queries is one of the biggest disappointments in Delphi V1.0.

On a typical application that makes heavy use of a reasonably well normalised
database, I usually find that I want to load a form (and update) with the
results of query that joins two of three tables.  All I'm trying to say is
that I would like to use updateable multi-table queries all the time, and it
is a HUGE pain not having them!

A few suggestions:

1) If you can, (as John Wilson suggested) use two (or more) single-table
   queries linked via the DataSource property of your TQuery objects.
   i.e. a master-detail relationship. (Good but generally not applicable)

2) Denormalise your database so that you can do a single table select.

3) Use a dedicated table as temporary storage.  
   The table structure should match the results of your query that you
   want updateable.  
   Use the "insert into tmpDataTable as select ... from table1, table2 ..."
   SQL statement to  populate the temporary storage table.
   Point your grid datasource to the temporary storage table via a TTable
   or singe table TQuery. i.e. your data is now updateable, but in the wrong
   table.
   When the form is closed, or the data needs to be committed, update the
   real table, or tables, based on data in the temporary storage table.
   If you can use a tricky update statement, otherwise process the
   temporary storage table row by row.  When you're finished delete the
   data in the temporary storage table.
   If you're in a multi-user environment have an extra column in the
   temporary storage table to uniquely specify your dataset, as
   opposed to someone else performing the same function at the same time.
   You could also optimise your updates if you have another extra column
   (a flag) that you programmatically change when the row is updated.
   (Sorry this one is rather long winded, but if you're desperate it will
   work.  Thankfully I haven't had to resort to this yet, but I've thought
   about it.)

4) Write a component derived from TQuery that is updateable, regardless
   of the fact that it may be a multi-table select.
   (No I haven't done it, but I've contemplated it.  I suspect though,
   that TQuery's architecture won't allow this.)

5) Battle on, and hope a later version of Delphi provides the required
   functionality.  (hmmm)

All the best,
Brett McKenzie

_________________________________________________________________
BHP Information Technology, PO Box 216, Hamilton 2303, Australia.
Tel: +61-49-402101.  Fax: +61-49-402165.

I believe the culprit is BDE and not Delphi, although I could be wrong. When I try to do a multiple
table live query I get an error message "Table opened as read only" for table2, table3, etc.

Quote:

>A few suggestions:
>[...]
>3) Use a dedicated table as temporary storage.  
>   The table structure should match the results of your query that you
>   want updateable.  
>   Use the "insert into tmpDataTable as select ... from table1, table2 ..."
>   SQL statement to  populate the temporary storage table.
>   Point your grid datasource to the temporary storage table via a TTable
>   or singe table TQuery. i.e. your data is now updateable, but in the wrong
>   table.
>   When the form is closed, or the data needs to be committed, update the
>   real table, or tables, based on data in the temporary storage table.
>   If you can use a tricky update statement, otherwise process the
>   temporary storage table row by row.  When you're finished delete the
>   data in the temporary storage table.
>   If you're in a multi-user environment have an extra column in the
>   temporary storage table to uniquely specify your dataset, as
>   opposed to someone else performing the same function at the same time.
>   You could also optimise your updates if you have another extra column
>   (a flag) that you programmatically change when the row is updated.
>   (Sorry this one is rather long winded, but if you're desperate it will
>   work.  Thankfully I haven't had to resort to this yet, but I've thought
>   about it.)

This is a very interesting suggestion although I do not fully follow the extra column idea to
identify the dataset.  Can you elaborate on this a little more? I can see the flag for updates, but
not the other. Thank you.

--
Gabriel Beccar-Varela

----