Menu

Lesson 13 · Node.js on IBM i — From RPG Developer to Modern IBM i Developer

Rent a Tool Safely

Protect the rental rules in Db2, call the transaction from Node.js, and add the first write endpoint to the API.

IBM iNode.jsDb2 for iTransactionsREST API

Renting a tool changes more than one row: it creates a rental and marks the tool as rented. Those changes must succeed or fail together.

Checking availability in Node.js and then sending two unrelated updates would leave a gap where another request could rent the same tool. We will keep the transaction close to the data in a small SQL procedure.

Create the rental procedure

Add sql/003-rental-procedures.sql:

CREATE OR REPLACE PROCEDURE TOOLRENT.RENT_TOOL (
    IN P_TOOL_ID INTEGER,
    IN P_CUSTOMER_ID INTEGER,
    IN P_DUE_DATE DATE
)
LANGUAGE SQL
MODIFIES SQL DATA
COMMIT ON RETURN NO
BEGIN ATOMIC
    DECLARE V_ACTIVE SMALLINT DEFAULT 0;
    DECLARE V_CHANGED INTEGER DEFAULT 0;

    IF P_DUE_DATE < CURRENT_DATE THEN
        SIGNAL SQLSTATE '75004'
          SET MESSAGE_TEXT = 'Due date cannot be in the past';
    END IF;

    SELECT COALESCE(MAX(ACTIVE), 0)
      INTO V_ACTIVE
      FROM TOOLRENT.CUSTOMERS
     WHERE CUSTOMER_ID = P_CUSTOMER_ID;

    IF V_ACTIVE <> 1 THEN
        SIGNAL SQLSTATE '75002'
          SET MESSAGE_TEXT = 'Customer is not active';
    END IF;

    UPDATE TOOLRENT.TOOLS
       SET STATUS = 'RENTED'
     WHERE TOOL_ID = P_TOOL_ID
       AND STATUS = 'AVAILABLE';

    GET DIAGNOSTICS V_CHANGED = ROW_COUNT;

    IF V_CHANGED <> 1 THEN
        SIGNAL SQLSTATE '75001'
          SET MESSAGE_TEXT = 'Tool is not available';
    END IF;

    INSERT INTO TOOLRENT.RENTALS (TOOL_ID, CUSTOMER_ID, DUE_DATE)
    VALUES (P_TOOL_ID, P_CUSTOMER_ID, P_DUE_DATE);
END;

Run the script in the development environment. The conditional UPDATE is important: only one request can change a particular tool from AVAILABLE to RENTED.

Two keywords carry the “succeed or fail together” promise:

An atomic procedure needs commitment control, which in turn needs journaled tables. The tables qualify because CREATE SCHEMA set up journaling in Lesson 10. If Run SQL Scripts refuses to create the procedure, check that its connection is not using commit level *NONE.

Do not take the promise on trust. After the API route below is working, prove it in development:

  1. Temporarily add SIGNAL SQLSTATE '75999' SET MESSAGE_TEXT = 'Forced failure'; as the last statement before END, and run the script again.
  2. Send a rental request for an available tool. It should fail.
  3. Query TOOLRENT.TOOLS. The tool must still be AVAILABLE, and TOOLRENT.RENTALS must have no new row.
  4. Remove the forced failure and run the script once more.

If the tool is left as RENTED, the call is not running under commitment control. Resolve that with your database administrator before continuing.

Call it from the database module

Add this function to src/db.js and export it:

async function rentTool({ toolId, customerId, dueDate }) {
  const sql = 'CALL TOOLRENT.RENT_TOOL(?, ?, ?)';
  await pool.prepareExecute(sql, [
    { value: toolId, io: 'in' },
    { value: customerId, io: 'in' },
    { value: dueDate, io: 'in' }
  ]);
}

The exports at the bottom of the file become:

module.exports = {
  findToolById,
  listAvailableTools,
  rentTool
};

For the SELECT in Lesson 11 a plain array of values was enough. A procedure parameter also has a direction, and the connector assumes input and output unless told otherwise. All three parameters of RENT_TOOL are declared IN, so each value is marked io: 'in' to match.

The API does not need to know which rows change. It asks Db2 to perform one business operation.

Add the POST route

Add this route in buildServer:

server.post('/rentals', {
  schema: {
    body: {
      type: 'object',
      additionalProperties: false,
      required: ['toolId', 'customerId', 'dueDate'],
      properties: {
        toolId: { type: 'integer', minimum: 1 },
        customerId: { type: 'integer', minimum: 1 },
        dueDate: { type: 'string', format: 'date' }
      }
    }
  }
}, async (request, reply) => {
  await rentTool(request.body);
  return reply.code(201).send({ message: 'Tool rented' });
});

Add rentTool to the import at the top of src/server.js:

const {
  findToolById,
  listAvailableTools,
  rentTool
} = require('./db');

Restart the server so it loads the new code. During development, npm run dev restarts it for you whenever a source file changes.

Try it with identifiers that exist in your development data:

curl -X POST http://127.0.0.1:3000/rentals \
  -H "content-type: application/json" \
  -d '{"toolId":1,"customerId":1,"dueDate":"2099-12-31"}'

The distant date keeps the published command runnable. In normal use, choose an appropriate future return date.

Then verify the result:

SELECT * FROM TOOLRENT.RENTALS ORDER BY RENTAL_ID;
SELECT TOOL_ID, NAME, STATUS FROM TOOLRENT.TOOLS ORDER BY TOOL_ID;

Tool 1 should be RENTED, with one open rental. Sending the same request again should fail rather than create a second open rental. For now the caller sees a general HTTP 500 response. Lesson 15 turns that into a clear “tool is not available” answer.

The database has protected the important rule. The next lesson adds the matching return operation and a useful view of current rentals.

References

Documentation and references used for this lesson.

← Return to Node.js on IBM i

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.