How to use database-linked controls

[Jan Schenkel]

--- Bill <bill at bluewatermaritime.com> wrote:
> I am using MySQL.

Heh, that's what I use for testing and updating the
codebase -- I'll try and simulate the error in the
next couple of days.

> But if we cannot pass a user defined variable to the
> database query builder then it makes the
> database-linked controls kind of weak. I hope that
you 
> complete that user interface soon.
> 

It isn't straightforward to implement a clear
interface for this : should it be global variables ?
should it be a call to a function in the stack script
? should it be the content of a custom property ?

For now the easiest way to achieve dynamic queries is
to save a template query somewhere, and fill in the
dynamic parts, then update the SQL of the query using
the undocumented command "'revSetSQLOfQuery <name of
the query>,<sql statement>".
An example :
--
on mouseUp
  put "123456" into tCustomerID # or some field data
  put "SELECT * FROM orders WHERE customerid='" & \
         tCustomerID & "'" into tSQLStatement
  revSetSQLOfQuery "orders",tSQLStatement
end mouseUp
--

> Perhaps I can use the database-liked controls in
> combination by viewing
> subsets of records with only one or two of the
> fields and then going to that
> record for the whole card and its set of fields --
> which means I still have
> to get the update function working.
> 

You can use the same query for a table to show a list
of records, and individual fields to display details
-- though you'll have to turn on the Record set
caching option.
As for the update problem, I'm sure we'll find it :)

> The other problem is that moving through records
> only works for one table
> and that fields which are populated by records
> linked by primary keys to
> other tables have to be updated by a separate call
> (calls). I guess I'm not
> having very much luck with writing queries in the
> builder that use left
> joins -- are there some example MySQL access stacks
> using query builder that
> I can look at?
> 

Not an example stack per se, but with the above tip
regarding revSetSQLOfQuery, you should get master
detail forms working until the necessary code has been
written to allow dynamic queries through
point-and-click.

> I would be happy to send you my example stack and a
> nice database it
> accesses if you need something more to test.
> 

If I can't find it in a reasonable amount of time,
I'll be sure to contact you for those ; but I think I
have enough information for now -- thanks for the
offer.

Jan Schenkel ~ <jan at runrev.com>


		
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail