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' )