Hi Helen,

Once again thank you very much, for your time and effort in continually
sharing your knowledge. You're really great.

Cheers,
James
Helen Borrie wrote:

> At 10:19 AM 21/10/2004 +0800, you wrote:
>
> >Hi,
> >
> >Good day. I would like your opinion on which of the following solution
> >will have better performance. Performing a search by changing the
> >sqlwhereitem in afterprepare
>
> You don't change a SQLWhereItem in AfterPrepare. The only place you
> do it
> is in the OnPrepareSQL event.
>
> Changing SQLWhereItems or SQLOrder causes the whole statement to be
> invalidated, i.e. the structure of the statement gets altered and so the
> dataset gets unprepared and reset. IBO provides this trick to avoid
> completely resetting all of the properties, as would otherwise happen
> when
> the SQL is changed. When you use SQLWhereItems or SQLOrder, the Fields[]
> structures remain intact and the Params[] become are invalid.
>
> So, using SQLWhereItems is cheaper than invalidating the whole
> kit-and-caboodle, but it's considerably more costly than merely
> substituting values into existing Params.
>
> >or have a parametize where clause then just change the parameter value?
>
> This is by far the recommended way to do things. Once the statement is
> prepared, you just keep it prepared. IBO does the magic for you when you
> call the methods of Params, by merely slotting the latest values into the
> appropriate places of the XSQLDA structure that actually goes across the
> wire when successive instances of the statement are called.
>
> I've been watching your recent postings in support and I think maybe you
> miss the difference between changing the *values* in parameters and
> changing the parameters themselves. Here's a simple example:
>
> select col1, col2, col3
> from atable
> where col1 = :col1
> and col2 = :col2
>
> This statement has three ib_column objects in fields[] and two ib_column
> objects in params[]. There is a relationship between Fields[0] and
> Params[0]; and another between Fields[1] and Params[1]. Whenever you
> run
> this statement, that relationship is unchanged. The returned values in
> col1 and col2 (and therefore, in col3) depend on the values that your app
> passes to params[0] and params[1], respectively. That relationship
> doesn't
> change, so no repreparation is necessary.
>
> Now, let's change the columns that we specify in the WHERE clause:
>
> select col1, col2, col3
> from atable
> where col2 = :col2
> and col3 = :col3
>
> Now, the *structure* of the statement has changed - it's a different
> statement. If you do this by changing the whole of the SQL property, the
> whole original statement is invalidated. the Fields[] and Params[]
> arrays
> both disappear. The client lets go of the statement's handle and we are
> back at ground zero.
>
> The same applies if you add an ORDER BY clause that wasn't there
> before, or
> change the ordering to be on a different column or in a different
> direction. For example, adding an ORDER BY clause to our original
> statement changes its structure:
>
> select col1, col2, col3
> from atable
> where col1 = :col1
> and col2 = :col2
> order by col3
>
> When they are rebuilt, although the columns in Fields[] are the same, the
> relationships are different. Now Fields[1] is related to Params[0] and
> Fields[2] is related to Params[2]. Params[] will be nil until the
> statement is prepared. Then, if the server confirms that everything is
> cool, the new Params[] are ready to use.
>
> Now, as long as you don't change these structural relationships, you can
> run the same statement many times, changing the VALUES in the Params[] as
> often as you like.
>
> With SQLWhereItems and SQLOrder, IBO just gives you some middle
> ground. It's a way of taking account of the fact that the new structure,
> created by the change in the columns designated by the changed WHERE
> clause
> structure, doesn't affect the structure of the set itself....so it's a
> way
> of saving some resources during the unprepare/reprepare cycle.
>
> Helen
>
>
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or
> InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
> papers,
> keyword-searchable FAQ, community code contributions and more !
>
>
> *Yahoo! Groups Sponsor*
> ADVERTISEMENT
> <http://us.ard.yahoo.com/SIG=129puj6gi/M=315388.5497957.6576270.3001176/D=groups/S=1705007183:HM/EXP=1098414083/A=2372354/R=0/SIG=12id813k2/*https://www.orchardbank.com/hcs/hcsapplication?pf=PLApply&media=EMYHNL40F21004SS>
>
>
>
> ------------------------------------------------------------------------
> *Yahoo! Groups Links*
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/IBObjects/
>
> * 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/>.
>
>