Subject Re: [IBO] Re: Strange error
Author Helen Borrie
At 05:38 PM 19/10/2005 +0000, you wrote:
>--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> > Wrong InterBase or Firebird message file?
> >
>
>Nope, not a wrong message file. After an hour or so with the
>debugger here is what I have found:
>
>Original contents of TIBOQuery SQL:
>
>SELECT LDS_NAME
> FROM LD_STOP
> WHERE LDS_TEMPLATE_FLAG = 'N' AND
> LDS_LOCATION = :LDS_LOCATION AND
> LDS_LOAD_NUMBER = :LDS_LOAD_NUMBER AND
> LDS_LOAD_SFX = :LDS_LOAD_SFX AND
> LDS_STOP_NUMBER = :LDS_STOP_NUMBER
> PLAN (LD_STOP INDEX (LDS_STOP_KEY))

Ah! don't put the plan in the SQL property, put it in the SQLPlan property
(or in the Plan property, if you are using TIBOQuery).


>In IB_Components.TIB_BindingCursor.GetPrepare IBO is trying to
>prepare the following SQL:
>
>SELECT LDS_NAME, LD_STOP.RDB$DB_KEY
> FROM LD_STOP
> WHERE LD_STOP.RDB$DB_KEY=? /* BIND_0 */
> PLAN (LD_STOP INDEX (LDS_STOP_KEY))

Missing or wrong keylinks, too?


>Snippet of code showing offending section:
>
>procedure TIB_BindingCursor.GetPrepare;
>var
> SaveCW: word;
> MonitorText: string;
>begin
> FNeedPrepare := true;
> with IB_Session do
> begin
> if PstHandle^ = FakePointer then
> PstHandle^ := nil;
> SysAllocate;
> asm fstcw [SaveCW] end;
>--> errcode := isc_dsql_prepare( @status,
> PtrHandle,
> PstHandle,
> null_terminated,
> PChar( FRefinedSQL ),
> IB_Connection.SQLDialect,
> CursorFields.PSQLDA );
> asm fldcw [SaveCW] end;
> //ADDED// CW 2000-06-06
> if ( errcode = 0 ) and ClientMonitorHooksIn then
> begin
>
>
>This is obviously incorrect. It would appear to be a bug in IBO when
>attempting to handle SELECT statements with a PLAN specified.

Drop the plan clause from the SQL property and use the designated
property. IBO parses the SQL property at various times during the Prepare
sequence and breaks it into pieces, to which it applies things like
parameter values, SQLOrder, SQLWhereItems, et al. It does this levelled
breakdown (to a lesser extent) even when applying new values to prepared
parameters and this is where things seem to have got unstuck.

The Plan clause was just in the wrong place at the wrong time!

Note also that you can't change the plan on a prepared query. When you
want to change the plan, call InvalidateSQL and apply the new plan in
OnPrepareSQL.

All that said, you are *still* going to get the exception back from the
engine if you specify a plan that the engine can't use, i.e. include a
non-existent index, or one that doesn't participate in the construction of
the streams for the query.

Helen