| Subject | RE: [IBO] Using InsertSQL, EditSQL, DeleteSQL |
|---|---|
| Author | Helen Borrie |
| Post date | 2001-02-11T21:54:58Z |
Michael,
you have really two questions here.
I'll answer the "on topic" one first and take time to offer some suggestions for the second in a different posting.
At 10:54 AM 11-02-01 -0500, you wrote:
If you set RequestLive to True, the dataset object itself creates the three statements itself, if it can, using the columns of the statement in the SQL property, along with the Keylinks and other information it collects itself from the Prepare statement and some of the properties. If the SQL is a straightforward SELECT <fieldlist> from ATABLE, you don't have to worry about the updateSQL properties yourself at all.
Use one or more of the updateSQL properties when the main SQL statement returns a read-only dataset (e.g. a JOINed dataset or a SELECT from a stored procedure); or when you want to run a custom update, typically calling a SP to do the work.
In the case of a JOINed dataset, the IBO dataset can apply some intelligence to allow you to do your update stuff directly with dynamic SQL. A single dynamic can perform DML on only one table. For a joined dataset, you make an entry in the KeyRelation to tell IBO which underlying table is to be operated on by your updateSQL statements; then you place the three statements into InsertSQL, EditSQL, DeleteSQL and, provided they are valid, your dataset will become "live".
In cases where, for example, you want the DML in a joined dataset, a non-updatable view or a selectable SP to be "live", you would typically create parameterised stored procedures for these datasets in your database. Then, your updateSQL statements are simply parameterised statements that call these SPs, e.g. for InsertSQL you might have 'EXECUTE PROCEDURE INS_INTO_ATABLE(:Param1, :Param2...). If the input columns have the same names as the columns in your dataset, you don't even have to assign them, IBO will do it for you - otherwise you can intercept the operation at the OnPrepareSQL event and assign the parameters yourself using ParamByName('Param1').AsSomething := <whatever>.
Although SPs are an elegant way to do custom updates, you can use a single DSQL statement if the update affects only one table.
You can combine your custom updateSQL with triggers to perform some very complex processing on the client side when your user clicks a button on the Update bar!
Regards,
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________
you have really two questions here.
I'll answer the "on topic" one first and take time to offer some suggestions for the second in a different posting.
At 10:54 AM 11-02-01 -0500, you wrote:
>I would still like more details on how to use the InsertSQL,Think of these as "row by row" - what you want to happen when the user selects a row for updating or deleting, or asks to insert a new row.
>EditSQL, DeleteSQL?
If you set RequestLive to True, the dataset object itself creates the three statements itself, if it can, using the columns of the statement in the SQL property, along with the Keylinks and other information it collects itself from the Prepare statement and some of the properties. If the SQL is a straightforward SELECT <fieldlist> from ATABLE, you don't have to worry about the updateSQL properties yourself at all.
Use one or more of the updateSQL properties when the main SQL statement returns a read-only dataset (e.g. a JOINed dataset or a SELECT from a stored procedure); or when you want to run a custom update, typically calling a SP to do the work.
In the case of a JOINed dataset, the IBO dataset can apply some intelligence to allow you to do your update stuff directly with dynamic SQL. A single dynamic can perform DML on only one table. For a joined dataset, you make an entry in the KeyRelation to tell IBO which underlying table is to be operated on by your updateSQL statements; then you place the three statements into InsertSQL, EditSQL, DeleteSQL and, provided they are valid, your dataset will become "live".
In cases where, for example, you want the DML in a joined dataset, a non-updatable view or a selectable SP to be "live", you would typically create parameterised stored procedures for these datasets in your database. Then, your updateSQL statements are simply parameterised statements that call these SPs, e.g. for InsertSQL you might have 'EXECUTE PROCEDURE INS_INTO_ATABLE(:Param1, :Param2...). If the input columns have the same names as the columns in your dataset, you don't even have to assign them, IBO will do it for you - otherwise you can intercept the operation at the OnPrepareSQL event and assign the parameters yourself using ParamByName('Param1').AsSomething := <whatever>.
Although SPs are an elegant way to do custom updates, you can use a single DSQL statement if the update affects only one table.
You can combine your custom updateSQL with triggers to perform some very complex processing on the client side when your user clicks a button on the Update bar!
Regards,
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________