Subject Re: [IBO] TIB_StoredProc
Author Geoff Worboys (TeamIBO)
> In my application when a new record is added to tablea several
> support records need to be added to tablesb,c, and d. I created a
> stored procedure to do this. I added a TIB_StoredProc to the form
> and use ExecProc when a new record is added to tablea. All works
> fine, sort of. The new record is added to tablea and the support
> records are added to tables b,c, and d. Problem is my form act
> strange after executing the stored procedure. I can not click on
> certain fields and the form blinks when I click on it. If I am
> running the the IDE even exiting the program and rerunning it won't
> help. However, if I exit Delphi then restart Delphi and the program
> all works fine. All records have been correctly added. I am using
> D5. The store procedure is simply a whole series of insert into
> statements. The only unique thing is that on the form the support
> tables (B,C,and D) are all linked to the main table (A) via
> master/detail. Everything works fine until I execute the stored
> procedure.

This is truly strange. I am not even sure where to start trying to
diagnose this problem.

One thing to check - did the server restart itself? Check the
Interbase.log file in the IB/FB installation directory. If your
connection was active inside the Delphi IDE and the stored proc
results in a server abort then that could explain why things dont
clean up until Delphi is restarted.

If that is the case then try running your stored proc manually, using
IB_SQL. That is manually insert the required record into the table
then manually execute the necessary procedure, and see what happens.
If the server aborts again then you will need to review the stored
procedure and all triggers of associated tables to try and see where
the problem lies.

Side Note: If a procedure should be run as the result of an insert,
then it may be better to run it from an AFTER INSERT trigger anyway.

If the above does not explain the situation then...

If your current database and application are not too private (or too
large) perhaps you could zip up the whole lot and email to me as a
demonstration. Make sure you get rid of all the DCUs etc to try and
keep the zip as small as possible.

If the database or app is private, or if the zip is more than a few
hundred kb then see if you can recreate the problem using a new
application and perhaps a script to create the necessary database.

Sorry I cant be of more help directly.

Geoff Worboys - TeamIBO
Telesis Computing