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 do 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.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0