| Subject | RE: [IBO] Updating left outer join |
|---|---|
| Author | Roger Vellacott |
| Post date | 2006-06-05T14:06:34Z |
Jason's release note for version 4.3Ac says the following:
"I fixed a bug in the handling of TField objects in the
InternalSetFieldData method. I thought I was checking the field's
ReadOnly property to prevent the setting when appropriately but due to
the way the variable reference scope was it was mistakenly reading the
dataset's ReadOnly property instead. Thus, I was allowing fields marked
as read only to be modified. It will now raise an exception as it
should."
In other words, previous versions did not mark joined fields as ReadOnly
fields, so it was much easier to customise the update process, with
separate DSQLs, code in the BeforePost event, etc etc. We now have to
circumvent the new behaviour by routinely looping through all fields
setting ReadOnly off in the AfterOpen event, but, even so, IBO seems to
reimpose the ReadOnly properties from time to time. This makes it much
more difficult to create a reliable system in which joined fields, or
other normally "readonly" fields, are editable by the user.
It used to be possible to make a computed field editable, and respond to
user edits by updating the fields or the tables from which it was
computed. This no longer works, as IBO will not allow ReadOnly to be
switched off on computed fields. It will, however, allow editing of
Calculated Fields.
I can see the logic of setting ReadOnly on such fields by default -
especially where joined tables are displayed in editable grids, but it
would be much more stable for those of us who want to control the
editing and updating process if we could switch this behaviour off -
perhaps with a new property of TIB_Statement. Any chance of this Jason?
By the way, we have an empty stored procedure called DO_NOTHING, so we
can set EditSQL etc to say "EXECUTE PROCEDURE DO_NOTHING", in order to
persuade IBO that the update has been completed. This would be much
faster than your "where rec = -1".
Roger Vellacott
Passfield Data Systems Ltd.
<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" />
-----Original Message-----
From: Paul Hope [mailto:paulhope@...]
Sent: 05 June 2006 14:25
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Updating left outer join
I solved it using OnCustomEdit and a separate DSQL. Problem was I
could
not edit the grid without the EditSQL in place, so I left it there and
crippled it with 'where rec = -1'.
This raises two questions
Should IBO have supported what I was doing below?
How do you use OnCustomEdit, make the grid editable and not have a
crippled
statement in the InsertSQL?
Regards
Paul
___
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.
________________________________________________________________________
___
http://www.ibobjects.com <http://www.ibobjects.com> - your IBO
community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
SPONSORED LINKS
Inventory database software Database management software Database
design software
Database software Oracle database software Customer
database software
_____
YAHOO! GROUPS LINKS
* Visit your group " IBObjects
<http://groups.yahoo.com/group/IBObjects> " on the web.
* To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com
<mailto:IBObjects-unsubscribe@yahoogroups.com?subject=Unsubscribe>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .
_____
"I fixed a bug in the handling of TField objects in the
InternalSetFieldData method. I thought I was checking the field's
ReadOnly property to prevent the setting when appropriately but due to
the way the variable reference scope was it was mistakenly reading the
dataset's ReadOnly property instead. Thus, I was allowing fields marked
as read only to be modified. It will now raise an exception as it
should."
In other words, previous versions did not mark joined fields as ReadOnly
fields, so it was much easier to customise the update process, with
separate DSQLs, code in the BeforePost event, etc etc. We now have to
circumvent the new behaviour by routinely looping through all fields
setting ReadOnly off in the AfterOpen event, but, even so, IBO seems to
reimpose the ReadOnly properties from time to time. This makes it much
more difficult to create a reliable system in which joined fields, or
other normally "readonly" fields, are editable by the user.
It used to be possible to make a computed field editable, and respond to
user edits by updating the fields or the tables from which it was
computed. This no longer works, as IBO will not allow ReadOnly to be
switched off on computed fields. It will, however, allow editing of
Calculated Fields.
I can see the logic of setting ReadOnly on such fields by default -
especially where joined tables are displayed in editable grids, but it
would be much more stable for those of us who want to control the
editing and updating process if we could switch this behaviour off -
perhaps with a new property of TIB_Statement. Any chance of this Jason?
By the way, we have an empty stored procedure called DO_NOTHING, so we
can set EditSQL etc to say "EXECUTE PROCEDURE DO_NOTHING", in order to
persuade IBO that the update has been completed. This would be much
faster than your "where rec = -1".
Roger Vellacott
Passfield Data Systems Ltd.
<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" />
-----Original Message-----
From: Paul Hope [mailto:paulhope@...]
Sent: 05 June 2006 14:25
To: IBObjects@yahoogroups.com
Subject: RE: [IBO] Updating left outer join
I solved it using OnCustomEdit and a separate DSQL. Problem was I
could
not edit the grid without the EditSQL in place, so I left it there and
crippled it with 'where rec = -1'.
This raises two questions
Should IBO have supported what I was doing below?
How do you use OnCustomEdit, make the grid editable and not have a
crippled
statement in the InsertSQL?
Regards
Paul
> -----Original Message-----<http://us.click.yahoo.com/_7bhrC/NGxNAA/yQLSAA/PhFolB/TM>
> From: IBObjects@yahoogroups.com
> [mailto:IBObjects@yahoogroups.com] On Behalf Of Paul Hope
> Sent: 05 June 2006 13:09
> To: IBObjects@yahoogroups.com
> Subject: RE: [IBO] Updating left outer join
>
> Hi Helen
>
>
> > -----Original Message-----
> > From: IBObjects@yahoogroups.com
> > [mailto:IBObjects@yahoogroups.com] On Behalf Of Helen Borrie
> > Sent: 05 June 2006 12:57
> > To: IBObjects@yahoogroups.com
> > Subject: Re: [IBO] Updating left outer join
> >
> > At 09:42 PM 5/06/2006, you wrote:
> > >Hi
> > >
> > >I have this query connected to a grid
> > >
> > >select s.rec,s.shipment,s.whs,s.pid,s.qty,s.alloc,w.description
> > >from (warehouses w left outer join shipment_allocations s
> > > on s.whs=w.ref and s.shipment=:shipment and s.pid=:pid)
> > >
> > >in the EditSQL I have
> > >
> > >execute procedure shipment_alloc_edit(:shipment,:pid,:qty,:whs)
> > >
> > >:shipment comes from MasterParams and MasterSource :qty is
> > entered in
> > >the grid :pid is set in code AfterPrepare (ParamByName) :whs
> > is one of
> > >the fields extracted in the SQL
> > >
> > >When I look at the SQLMonitor I see that when the query is
> > opened both
> > >:shipment and :pid are correctly set. However when the
> InsertSQL is
> > >executed :shipment and :qty are set, but :pid and :whs are null.
> > >
> > >How can I persuade it to pick up :pid and :whs?
> >
> > Try improving the syntax for the main query:
> >
> > select s.rec,s.shipment,s.whs,s.pid,s.qty,s.alloc,w.description
> > from warehouses w
> > left outer join shipment_allocations s
> > on s.whs=w.ref
> > WHERE s.shipment=:shipment and s.pid=:pid
> >
> > Helen
> >
> That breaks it, I always want the grid populated with fields
> from warehouses even when there is no data in
> shipment_allocations. Changing it as you suggest returns no rows ;-(
>
> Regards
> Paul
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~--> You can search right from your
> browser? It's easy and it's free. See how.
> http://us.click.yahoo.com/_7bhrC/NGxNAA/yQLSAA/PhFolB/TM
> --------------------------------------------------------------community resource for
> ------~->
>
> ______________________________________________________________
> _____________
> IB Objects - direct, complete, custom connectivity to
> Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> ______________________________________________________________
> _____________
> http://www.ibobjects.com <http://www.ibobjects.com> - your IBO
> Tech Info papers,________________________________________________________________________
> keyword-searchable FAQ, community code contributions and more
> !
> Yahoo! Groups Links
>
>
>
>
>
>
___
IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
without the need for BDE, ODBC or any other layer.
________________________________________________________________________
___
http://www.ibobjects.com <http://www.ibobjects.com> - your IBO
community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
SPONSORED LINKS
Inventory database software Database management software Database
design software
Database software Oracle database software Customer
database software
_____
YAHOO! GROUPS LINKS
* Visit your group " IBObjects
<http://groups.yahoo.com/group/IBObjects> " on the web.
* To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com
<mailto:IBObjects-unsubscribe@yahoogroups.com?subject=Unsubscribe>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .
_____