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.
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:
BEGIN ATOMICtells Db2 to treat the body as one unit. If any statement fails, or the procedure signals an error, every change the procedure has already made is undone.COMMIT ON RETURN NOleaves the commit to the caller. The connector has autocommit switched on by default, so a call that completes successfully is committed.
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:
- Temporarily add
SIGNAL SQLSTATE '75999' SET MESSAGE_TEXT = 'Forced failure';as the last statement beforeEND, and run the script again. - Send a rental request for an available tool. It should fail.
- Query
TOOLRENT.TOOLS. The tool must still beAVAILABLE, andTOOLRENT.RENTALSmust have no new row. - 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.

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