This is a very bad way to generate record ID numbers. The first client to execute this SP will lock the row until it commits, which will block any other clients from getting their ID number. I assume in the 'forever' case, that you have a client just sitting there without committing?

It would be a much better idea to replace this with an sp that uses a generator.

Regards,
Dan.


*********** REPLY SEPARATOR ***********

On 1/14/2004 at 1:19 PM Pablo Lerner 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' )





Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/