Using the primary keys from the key pool

Use the primary keys (SQL)

When a sales representative adds a new customer to the Customers table, the primary key value to be inserted is obtained using a stored procedure. This example uses a stored procedure to supply the primary key value, and a stored procedure to do the insert.

Create a procedure to run on the remote databases to obtain a primary key from the primary key pool table.

For example, 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;

The NewKey procedure takes advantage of the fact that the Sales Representative identifier is the CURRENT PUBLISHER of the remote database.

Create a procedure that runs on the remote databases to insert a new row in a subscribed table.

For example, 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 can enhance this procedure by testing the new_cust_key value obtained from NewKey to check that it is not NULL, and prevent the insert if it is NULL.

Discuss this page in DocCommentXchange.