[fpc-pascal] Master Detail relationship in SQLdb

John 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)
par_ref (integer)
name    (varchar)
<other fields>

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 
this stage).

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 
basically working.

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 mailing list