[fpc-pascal] Master Detail relationship in SQLdb
jszcmpr at netspace.net.au
Thu Jul 12 16:18:41 CEST 2007
I have a table structured like this:
ref (integer) (value generated by a sequence) (also Primary Key)
I am using it as a two level look-up list of values. Top level entries
have a par_ref of 0, next level entries have a par_ref that points to
the ref of a top_level entry. The app does not allow further levels (at
The master SQLQuery that has something like:
select * from mytable where par_ref = 0 order by name;
Works fine, (including updating with my own update queries)
The slave query has:
select * from mytable where par_ref = :PRef order by name;
I have an on_scroll event on the master table that sets the value of the
PRef parameter. (I asked a question about this previously)
This also works ... except that it returns rubbish values for 'ref' -
9-digit numbers instead of numbers like 6 or 8 that they should be.
(Absolutely no way they are real numbers from wrong records). This
means, of course, that in the update query, (update ... where ref =
:ref) nothing happens, because no records match the ref parameter
value. However, if I replace the :PRef by a real number (eg 5), I get
sensible ref values, so it seems to be associated with the parameter.
Yet the parameter value DOES select the correct records, so it must be
I have tried tracing this through (thanks for the help on getting that
to work) but can't find anything wrong. (Most of the values I really
want to check are properties, so I can't get their values). Does anyone
have any ideas ?
Another question (sort of related)
The only way I have been able to get the query to 'read' the new
parameter value is to close and reopen the query. Should this be so ?
This means it unprepares and then re-prepares the (same) query,
reallocates all the fields... Is there not some way of just
re-executing the same prepared query with the new bound value(s) ?
(I guess it means I could work around my problem by just modifying the
sql text each time, as I would have no additional overhead, but it seems
the "wrong way" to go about it!)
More information about the fpc-pascal