Recent

Author Topic: [SOLVED] Firebird annoyance - object in use  (Read 10007 times)

JD

  • Hero Member
  • *****
  • Posts: 1848
[SOLVED] Firebird annoyance - object in use
« on: February 26, 2017, 09:22:15 pm »
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][+][-]
  1.     //
  2.     with dm.TZStoredProc1 do
  3.     begin
  4.       //
  5.       try
  6.         StoredProcName := 'beneficiaire_doublons';
  7.         // les Params sont automatiquement créés à la suite de l'appel Prepare
  8.         Prepare;
  9.         //
  10.         ParamByName('ID').AsInteger         := intGarder;                      
  11.         ParamByName('ID_DOUBLON').AsInteger := intSupprimer;           
  12.         // Ce procédure ne retourne aucun résultat,
  13.         ExecProc;
  14.         //
  15.         CommitUpdates;
  16.         //
  17.         LJSResponse := Format('{"response": "%s"}', [strDoublonEffacerReussi]);
  18.       except
  19.         On E: Exception do
  20.         begin
  21.           //CLEANUP!
  22.           // Rollback transaction because an error occurred
  23.           CancelUpdates;
  24.           //
  25.           LJSResponse := Format('{"response": "%s"}', [strDoublonEffacerEchec]);
  26.         end     // On E: Exception do
  27.       end;
  28.    end;
  29.  

The server connects to the database using Zeos with the following connection properties
Code: Pascal  [Select][+][-]
  1.     with TZConnection1.Properties do
  2.     begin
  3.       Add('Codepage=utf8');
  4.       Add('isc_tpb_concurrency');      
  5.       Add('isc_tpb_nowait');            
  6.     end;      // with Properties do
  7.  

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
« Last Edit: March 03, 2017, 10:47:08 am by JD »
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Firebird annoyance - object in use
« Reply #1 on: February 26, 2017, 09:39:23 pm »
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)

Quote
All 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

  • Hero Member
  • *****
  • Posts: 1848
Re: Firebird annoyance - object in use
« Reply #2 on: February 26, 2017, 10:06:41 pm »
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)

Quote
All 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?
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Firebird annoyance - object in use
« Reply #3 on: February 26, 2017, 10:12:04 pm »
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

  • Hero Member
  • *****
  • Posts: 1848
Re: Firebird annoyance - object in use
« Reply #4 on: February 26, 2017, 10:19:56 pm »
I see what you mean. Thanks a lot. I'll do that in real time tomorrow.
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Firebird annoyance - object in use
« Reply #5 on: February 27, 2017, 05:21:01 am »
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

  • Hero Member
  • *****
  • Posts: 1848
Re: Firebird annoyance - object in use
« Reply #6 on: March 03, 2017, 10:46:23 am »
@rvk and @valdir.marcos

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

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

JD
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

ibexpert

  • Newbie
  • Posts: 3
Re: [SOLVED] Firebird annoyance - object in use
« Reply #7 on: October 21, 2017, 08:41:57 pm »
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

 

TinyPortal © 2005-2018