Database Query Builder -- errors

[Jan Schenkel]

--- Bill Humphrey <Bill at BlueWaterMaritime.com> wrote:
> In case anyone else wants to use this great thing
> extensively then when 
> designing your databases always use unique names for
> every column even 
> when in different tables because if you refer to a
> column as 
> tablename1.shipID and another one as
> tablename2.shipID even though SQL 
> is happy with this the database query builder update
> feature will choke.
> 

[Sorry for the late reply, jsut cleaning the backlog]

You're touching on one of the main drawbacks of SQL
databases: their relational syntax makes it easy to
combine data from multiple tables in a meaningful way
-- but what if you want to update the data ?

In the above example, the shipID field might be the
primary key for both tables, and there might be a
one-to-one relationship between them.
But it is far more likely that it is the primary key
for tablename1, but only part of the primary key for
tablename2 -- or it could be just a foreign key.

This means that while the revDatabse frontscirpt can
farily easily update the database when you're
displaying data from one table and telling it the
primary key of that table ; but updating combined data
from multiple tables is a different game entirely !
You would have to provide the primary key for each
table involved in the query and may therefore have to
add fields in order to have the key columns.

Note that this is also the main reason why it is near
impossible to provide a 'Create record' functionality
in that frontscript: if there's more than one table
involved, where should it create new records? In all
tables? In some of them? In only one of them?
Database solutions like FileMaker Pro have ready
access to the full database schema, including the
relations between the tables, and may use some
heuristics to figure out what it needs to create.

But it is very tricky to get this right without inner
knowledge of the database structure. Which is why I
usually tell people to do the SQL-stuff themselves,
and employ the database-linked controls mainly for
viewing data and minor editing.
You can easily piggy-back on the existing query
conection data by using the other calls in the
revDatabase frontscript like :
--
  revExecuteWithQuery <query name>,<sql query>
--

Hope this helped,

Jan Schenkel.

Quartam - Tools for Revolution
<http://www.quartam.com>

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)


		
__________________________________ 
Discover Yahoo! 
Use Yahoo! to plan a weekend, have fun online and more. Check it out! 
http://discover.yahoo.com/