Menu

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

Return a Tool and List Open Rentals

Complete the rental workflow by recording returns, making tools available again, and listing current and overdue rentals.

IBM iNode.jsDb2 for iSQLREST API

A rental is not complete until the return is recorded and the tool becomes available again. We will make those two changes one database operation.

Add the return procedure

Append this procedure to sql/003-rental-procedures.sql and run it in development:

CREATE OR REPLACE PROCEDURE TOOLRENT.RETURN_TOOL (
    IN P_RENTAL_ID INTEGER
)
LANGUAGE SQL
MODIFIES SQL DATA
COMMIT ON RETURN NO
BEGIN ATOMIC
    DECLARE V_TOOL_ID INTEGER DEFAULT 0;
    DECLARE V_CHANGED INTEGER DEFAULT 0;

    SELECT COALESCE(MAX(TOOL_ID), 0)
      INTO V_TOOL_ID
      FROM TOOLRENT.RENTALS
     WHERE RENTAL_ID = P_RENTAL_ID
       AND RETURNED_AT IS NULL;

    IF V_TOOL_ID = 0 THEN
        SIGNAL SQLSTATE '75003'
          SET MESSAGE_TEXT = 'Open rental was not found';
    END IF;

    UPDATE TOOLRENT.RENTALS
       SET RETURNED_AT = CURRENT_TIMESTAMP
     WHERE RENTAL_ID = P_RENTAL_ID
       AND RETURNED_AT IS NULL;

    GET DIAGNOSTICS V_CHANGED = ROW_COUNT;

    IF V_CHANGED <> 1 THEN
        SIGNAL SQLSTATE '75003'
          SET MESSAGE_TEXT = 'Open rental was not found';
    END IF;

    UPDATE TOOLRENT.TOOLS
       SET STATUS = 'AVAILABLE'
     WHERE TOOL_ID = V_TOOL_ID;
END;

The second attempt to return the same rental fails cleanly because RETURNED_AT is no longer null. As in the rental procedure, BEGIN ATOMIC means the rental row and the tool row change together or not at all.

Add two database functions

Add these functions to src/db.js and export them:

async function returnTool(rentalId) {
  await pool.prepareExecute(
    'CALL TOOLRENT.RETURN_TOOL(?)',
    [{ value: rentalId, io: 'in' }]
  );
}

async function listOpenRentals() {
  const rows = await pool.runSql(`
    SELECT R.RENTAL_ID,
           T.TOOL_ID,
           T.NAME AS TOOL_NAME,
           C.CUSTOMER_ID,
           C.NAME AS CUSTOMER_NAME,
           R.RENTED_AT,
           R.DUE_DATE,
           CASE WHEN R.DUE_DATE < CURRENT_DATE
                THEN 1 ELSE 0 END AS OVERDUE
      FROM TOOLRENT.RENTALS R
      JOIN TOOLRENT.TOOLS T
        ON T.TOOL_ID = R.TOOL_ID
      JOIN TOOLRENT.CUSTOMERS C
        ON C.CUSTOMER_ID = R.CUSTOMER_ID
     WHERE R.RETURNED_AT IS NULL
     ORDER BY R.DUE_DATE, R.RENTAL_ID
  `);

  return rows ?? [];
}

module.exports now lists five functions: findToolById, listAvailableTools, listOpenRentals, rentTool, and returnTool.

The query returns names that the interface can display, while the identifiers remain available for links and actions.

Add the routes

server.get('/rentals/open', async () => {
  return listOpenRentals();
});

server.post('/rentals/:rentalId/return', {
  schema: {
    params: {
      type: 'object',
      required: ['rentalId'],
      properties: {
        rentalId: { type: 'integer', minimum: 1 }
      }
    }
  }
}, async (request) => {
  await returnTool(request.params.rentalId);
  return { message: 'Tool returned' };
});

Import both functions from ./db, restart the server, and try:

curl http://127.0.0.1:3000/rentals/open
curl -X POST http://127.0.0.1:3000/rentals/1/return
curl http://127.0.0.1:3000/tools

The returned tool should appear in the available-tools response again.

Keep history instead of deleting it

Returning a tool updates the rental rather than deleting it. That history can answer useful questions later:

Do not add those reports yet. The current endpoint serves the immediate job at the rental counter and keeps the underlying data available for future reporting.

At this point the main business workflow works. We now need predictable validation and error responses before exposing it to users.

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.