I'm sorry to take your attention somewhere else but: DO all of us
agree thaat my original problem with the SP is just commiting
transaction ???

Somebody please confirm this. For the nature of my application is not
very easy to verify everything is fine untill some user complains and
confirms everything is wrong, and that's not very nice for them.


Pablo

--- In firebird-support@yahoogroups.com, "Pablo Lerner"
<plerner@s...> wrote:

> Hello everyone...
>
> I'm having some troubles with a store procedure. It wasn't

written by me so

> I don't realy know what may be the problem (I don't really know how

to write

> one but it is MY problem if application doesn't work properly).
>
> Problem is that sometimes when one application client

executes the

> procedure, it takes too long to finish its work. Sometimes up to 3

min,

> sometimes forever (you have to reset PC to contonue working). I

think it

> happends when two or more clients try to execute it while

anotherone's

> execution hasn't finished.
>
> My guess is that there's a transaction problem. But ODBC

driver is supposed

> to handle transaction automatically. Is there a problem with the

procedure

> I'm missing ?? Is there any sentence missing ?? should I ask for

ODBC driver

> support instead ??
>
> Technical Info. ahead....
>
> Server : Firebird 1.02 on Win2000
> Clients :Win98, Visual Fox Pro 7.0 application, connecting via

Gemini ODBC

> 2.1 driver.
>
> The Store Procedure :
>
> CREATE PROCEDURE INCAN (
> TABLENAME_ CHAR(20) CHARACTER SET NONE,
> INCREMENT INTEGER)
> RETURNS (
> NUMBER_ INTEGER)
> AS
> DECLARE VARIABLE CN INTEGER;
> BEGIN
> SELECT COUNT(*) FROM AUTONUM WHERE TABLENAME = :tablename_

INTO :cn ;

> IF (:cn = 0) THEN
> INSERT INTO AUTONUM (TABLENAME,NUMBER) VALUES(:tablename_,1);
> SELECT NUMBER FROM AUTONUM WHERE TABLENAME = :tablename_

INTO :number_;

> UPDATE AUTONUM SET NUMBER = NUMBER + :INCREMENT WHERE TABLENAME =
> :tablename_;
> SUSPEND;
> END
>
> Table Autonum:
>
> CREATE TABLE AUTONUM (
> TABLENAME VARCHAR(20) CHARACTER SET NONE NOT NULL,
> NUMBER INTEGER,
> AUTO_STAMP TIMESTAMP
> );
>
>
> On client application execution is like:
>
> cTName = "PRV"
> cStatement = "SELECT * FROM INCAN (?cTName,1)"
> nOk = SQLEXEC( nCon, cStatement, '_INCAN' )