Subject Re: IBO Commit action has unwanted side effect for me ;-(
Author dirknaudts
Hi Helen,

Thanks a lot for jumping in here, and giving me the much apreciated

I'm doing a small test app now to see how the InsertSql, updatesql
and deletesql work.

For this test I pulled one of the mentioned SP apart, havingone doing
the select, and another doing DML.

But now I realize that even the Insert statement should return a
result, only in case of failure, but nevertheless, it should.
If you look closely to the sample-sp, you'll see that, when there's a
PK violation (or any other check I might perform), I select a user
friendly errormessage from an applicatioerrors table, using the
languageid input parameter (so It's in the user's language), and
return that errormsg.

How would I be able to do all that from within the InserSql property
of TIBOQuery ? Can I somehow read output params of the query
performed in Insertsql property ?

(the Insertsql property now looks like 'Execute procedure ...')

Dirk Naudts.

Helen answered me saying :

>I think your approach to the DML is inappropriate and that you can
>solve this in a slightly different way.
>Make another stored procedure that only selects the output but does
>not perform any DML. Implement this in your application using a
>Modify the original stored procedure so that it only performs the
>DML but does not attempt to output a set.
>In the EditSQL, InsertSQL and DeleteSQL properties of the IB_Query,
>place the appropriate "mode" call to your modified SP. Then, when
>Edit, Insert or Delete is called upon the IB_Query, you will get the
>behaviour you want, very compactly.
>After the commit, have your query refresh in whatever way suits you
>and there will be no recursion.
>Although it is possible to make a combined SP that is both
>"selectable" and "excutable", in practice it is not recommended.
>The interplay between an IBO dataset and the server is a matter of
>harmonising atomic operations and enforcing the integrity of each
>operation - as indeed any robust client/server interface should do.
>In implementation, you need to aim for operations that work with
>this concept, rather than against it.
>Also, you need to understand this about selectable SPs. Although,
>in the client, thanks to the magic of IBO, it behaves on the client
>**somewhat** like a query on a tables, there is no table beneath it
>and hence, no database cursor to which IBO can attach as it does
>with table-based sets.
>For further help, please make use of the IBO list.