[SOLVED] Firebird annoyance - object in use
lazarus / 16-Databases

----------------------------------------------------------------------
JD · 2017-02-26 20:22

Hi there everyone,

I have a Firebird stored procedure that deletes some records in a
database. The database is in a network and so multiple users use the
database at any given time of the day.

The stored procedure works fine. The problem is that any attempt to
modify the stored procedure using say IBExpert, at a later time
produces the error in the attached dialog. I can only do modifications
AFTER I shut down the server and this is not practical on a busy day.
I would like to be able to modify any stored procedure without having
to shut down the server first.

The server code that runs the stored procedure is as follows:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     //   
with dm.TZStoredProc1 do    begin      //      try       
StoredProcName := 'beneficiaire_doublons';        // les Params sont
automatiquement créés à la suite de l'appel Prepare        Prepare;  
    //        ParamByName('ID').AsInteger         := intGarder;      
                       ParamByName('ID_DOUBLON').AsInteger :=
intSupprimer;                    // Ce procédure ne retourne aucun
résultat,        ExecProc;        //        CommitUpdates;        // 
     LJSResponse := Format('{"response": "%s"}',
[strDoublonEffacerReussi]);      except        On E: Exception do    
  begin          //CLEANUP!          // Rollback transaction because
an error occurred          CancelUpdates;          //         
LJSResponse := Format('{"response": "%s"}',
[strDoublonEffacerEchec]);        end     // On E: Exception do     
end;   end; 
The server connects to the database using Zeos with the following
connection properties
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     with
TZConnection1.Properties do    begin      Add('Codepage=utf8');     
Add('isc_tpb_concurrency');             Add('isc_tpb_nowait');       
       end;      // with Properties do 
I thought that committing the updates after execution closes the
stored procedure BUT it still complains about the NO_WAIT. I need the
NO_WAIT to avoid deadlocks in my multiuser environment.

I am using Firebird 2.5.7 in SuperClassic mode.

What can I do in the server code to close off stored procedures and be
able to modify them after they have been executed?

Thanks,

JD

----------------------------------------------------------------------
rvk · 2017-02-26 20:39

What happens if you only use a WAIT transaction for changing the
PROCEDURE?

(The clients can still do NO_WAIT on using it but shouldn't use the
PROCEDURE during changing)

QuoteAll you need to do is to use a WAIT transaction to perform DDL
for
procedures (including ALTER PROCEDURE). When using a NOWAIT
transaction
to alter procedures, this is expected to get an "object in use" error.

----------------------------------------------------------------------
JD · 2017-02-26 21:06

Quote from: rvk on February 26, 2017, 09:39:23 pmWhat happens if you
only use a WAIT transaction for changing the PROCEDURE?

(The clients can still do NO_WAIT on using it but shouldn't use the
PROCEDURE during changing)

QuoteAll you need to do is to use a WAIT transaction to perform DDL
for
procedures (including ALTER PROCEDURE). When using a NOWAIT
transaction
to alter procedures, this is expected to get an "object in use" error.
You might find this funny but how do I do that? Do I have to use the
command line iSQL tool?

----------------------------------------------------------------------
rvk · 2017-02-26 21:12

You should be able to set the transaction-settings in any DBManager
you use. Even FlameRobin has it.

I don't use IBExpert anymore but don't you see any option to set the
transaction-parameters when you change the PROCEDURE ?

As an alternative you could create a procedure in Lazarus in which you
set separate transaction-parameters (with WAIT) and change the
PROCEDURE in code.

----------------------------------------------------------------------
JD · 2017-02-26 21:19

I see what you mean. Thanks a lot. I'll do that in real time tomorrow.

----------------------------------------------------------------------
valdir.marcos · 2017-02-27 04:21

When have you last made a backup/restore of your database?
Why do you need to change this stored procedure so often?

On FlameRobin, got to Execute SQL Statements, menu Statement,
Transaction Settings, change the parameters, then try to execute again
your DML, as the image attached.

----------------------------------------------------------------------
JD · 2017-03-03 09:46

@rvk and @valdir.marcos

Sorry for the late reply. I was off doing that which programmers
dread: writing user documentation. 

I was finally able to do it using FlameRobin. Thanks a lot for your
kind suggestions.

JD

----------------------------------------------------------------------
ibexpert · 2017-10-21 18:41

even this is a very old thread, perhaps a real solution helps anyone

if ibexpert´s database registration and also in most libraries you
should simply replace
isc_tpb_nowait

by

isc_tpb_wait

in metadata transaction setting to avoid object  in use error message
(this will also
come very often in fb3 when you are still using isc_tpb_nowait)

Holger

www.ibexpert.com