Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Using primary keys from the key pool

When a sales representative wants to add a new customer to the Customers table, the primary key value to be inserted is obtained using a stored procedure. This example shows a stored procedure to supply the primary key value, and also illustrates a stored procedure to carry out the INSERT.

The procedures takes advantage of the fact that the Sales Rep identifier is the CURRENT PUBLISHER of the remote database.

• NewKey procedure    The NewKey procedure supplies an integer value from the key pool and deletes the value from the pool.

  CREATE PROCEDURE NewKey(
        IN @table_name VARCHAR(40),
        OUT @value INTEGER )
  BEGIN
     DECLARE NumValues INTEGER;
     
     SELECT count(*), min(value)
     INTO NumValues, @value
           FROM KeyPool
           WHERE table_name = @table_name
           AND location = CURRENT PUBLISHER;
     IF NumValues > 1 THEN
        DELETE FROM KeyPool
        WHERE table_name = @table_name
        AND value = @value;
     ELSE
     // Never take the last value, because
     // ReplenishPool will not work.
     // The key pool should be kept large enough
     // that this never happens.
        SET @value = NULL;
     END IF;
  END;

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

  CREATE PROCEDURE NewCustomers(
        IN customer_name CHAR( 40 ) )
  BEGIN
     DECLARE new_cust_key INTEGER ;
     CALL NewKey( 'Customers', new_cust_key );
     INSERT
     INTO Customers (
        cust_key,
        name,
        location
        )
     VALUES (
        'Customers ' ||
        CONVERT (CHAR(3), new_cust_key),
        customer_name,
        CURRENT PUBLISHER
        );
  );
  END

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