Sybase DC38133-01-0902-01 manual Testing the key pool, To test the primary key pool, 179

Chapter 8. SQL Remote Design for Adaptive Server Enterprise

CREATE PROCEDURE NewKey @TableName VARCHAR(40), @Location VARCHAR(6), @Value INTEGER OUTPUT AS

BEGIN

DECLARE @NumValues INTEGER SELECT @NumValues = count(*),

@Value = min(value) FROM KeyPool

WHERE table_name = @TableName AND location = @Location

IF @NumValues > 1 DELETE FROM KeyPool

WHERE table_name = @TableName AND value = @Value

ELSE

--Never take the last value,

--because RestorePool will not work.

--The key pool should be kept large

--enough so this never happens. SELECT @Value = NULL

END

♦NewCustomer procedure The NewCustomer procedure inserts a new customer into the table, using the value obtained by NewKey to construct the primary key.

CREATE PROCEDURE NewCustomer @name VARCHAR(40), @loc VARCHAR(6) AS

BEGIN

DECLARE @cust INTEGER

DECLARE @cust_key VARCHAR(12)

EXEC NewKey ’Customer’, @loc, @cust output SELECT @cust_key = ’cust’ +

convert( VARCHAR(12), @cust )

INSERT INTO Customer (cust_key, name, rep_key ) VALUES ( @cust_key, @name, @loc )

END

You may want to enhance this procedure by testing the @cust value obtained from NewKey to check that it is not NULL, and preventing the insert if it is NULL.

Testing the key pool

❖To test the primary key pool

1.Re-extract a remote database using the field_user user ID.

2.Try this sample INSERT at the remote and consolidated sites:

EXEC NewCustomer ’Great White North’, rep1

179