Subject Re: [IBO] IBO-Query / CanInsert ?
Author Geoff Worboys
> but it still does not do the insert part :(
> Even in SQL editor, it looks like as soon as you select
> from a storedprocedure the insertsql is completely ignored ?
> Here is what I tried against the employee.gdb with
> hardcoded parameters:

The example was very useful, thank you.

I think the problem comes down to the fact that TIB_Query is a
buffered dataset and expects to be able to retrieve a inserted row
specifically. To do that it must know the key, which of course does
not exist with a stored procedure. (Jason and I have discussed ways
of making it possible to do this efficiently with stored procedures
but it has not eventuated - maybe in 4.1 :-)

Anyway, if you have the full source, the problem lies in
IBA_BDataset.IMP and the following procedure...

procedure TIB_BDataset.SysPreparedChanged;
var
ii: integer;
begin
FCanInsert := true;
if not Assigned( FBindingCursor ) and
IsSelectSQL then
for ii := 0 to KeyFields.ColumnCount - 1 do
if FindField( KeyFields.Columns[ii].FullFieldName ) = nil then
begin
FCanInsert := false;
Break;
end;
inherited SysPreparedChanged;
end;

With KeyLinksAutoDefine=false and a stored procedure, the KeyFields
contains "KEYROWNUM" which does not exist in the actual SQL so the
FindField fails.

You can resolve the problem (at least temporarily) by doing the
following...

if (FindField( KeyFields.Columns[ii].FullFieldName ) = nil) and
(InsertSQL.Count = 0) then


You may find that you need to fully refresh the TIB_Query after doing
the insert(s), in order that the new values will be available.

I had something similar in an application. Its not all that
efficient, but as long as the result set is small it is not too bad.

Hopefully when Jason has a chance to look in he may be able to say
whether this "fix" is appropriate in the long term.


HTH

Geoff Worboys
Telesis Computing