The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Database Access

Next ►
►

In this section, we will explore how to access and manipulate databases using RPG. This is a crucial skill for any RPG programmer, as most business applications require interaction with databases to store and retrieve data.

Key Concepts

  1. Database Terminology

    • Table: A collection of related data entries.
    • Row: A single record in a table.
    • Column: A single field in a table.
    • Primary Key: A unique identifier for a row in a table.
    • Foreign Key: A field in a table that links to the primary key of another table.

  2. SQL (Structured Query Language)

    • SELECT: Retrieve data from a database.
    • INSERT: Add new data to a database.
    • UPDATE: Modify existing data in a database.
    • DELETE: Remove data from a database.

  3. Embedded SQL in RPG

    • Using SQL statements directly within RPG code.

Setting Up Database Access

Before you can access a database, you need to ensure that your development environment is properly configured. This typically involves setting up a connection to the database and ensuring that you have the necessary permissions.

Example: Connecting to a Database

DCL-S dbName CHAR(10) INZ('MYDB');
DCL-S userName CHAR(10) INZ('USER');
DCL-S password CHAR(10) INZ('PASSWORD');

EXEC SQL CONNECT TO :dbName USER :userName USING :password;
IF SQLCODE <> 0;
  // Handle connection error
ENDIF;

Explanation

  • DCL-S: Declares a standalone variable.
  • INZ: Initializes the variable with a value.
  • EXEC SQL CONNECT: Connects to the specified database using the provided username and password.
  • SQLCODE: A variable that holds the status of the last executed SQL statement. A value of 0 indicates success.

Retrieving Data

To retrieve data from a database, you use the SELECT statement. This can be embedded directly in your RPG code.

Example: Retrieving Data

DCL-S customerId CHAR(10);
DCL-S customerName CHAR(50);

EXEC SQL
  SELECT name INTO :customerName
  FROM customers
  WHERE id = :customerId;

IF SQLCODE <> 0;
  // Handle retrieval error
ENDIF;

Explanation

  • SELECT name INTO :customerName: Retrieves the name field from the customers table and stores it in the customerName variable.
  • WHERE id = :customerId: Specifies the condition to match the id field with the customerId variable.

Inserting Data

To add new data to a database, you use the INSERT statement.

Example: Inserting Data

DCL-S newCustomerId CHAR(10) INZ('CUST123');
DCL-S newCustomerName CHAR(50) INZ('John Doe');

EXEC SQL
  INSERT INTO customers (id, name)
  VALUES (:newCustomerId, :newCustomerName);

IF SQLCODE <> 0;
  // Handle insertion error
ENDIF;

Explanation

  • INSERT INTO customers (id, name): Specifies the table and columns to insert data into.
  • VALUES (:newCustomerId, :newCustomerName): Provides the values to be inserted.

Updating Data

To modify existing data in a database, you use the UPDATE statement.

Example: Updating Data

DCL-S updateCustomerId CHAR(10) INZ('CUST123');
DCL-S newCustomerName CHAR(50) INZ('Jane Doe');

EXEC SQL
  UPDATE customers
  SET name = :newCustomerName
  WHERE id = :updateCustomerId;

IF SQLCODE <> 0;
  // Handle update error
ENDIF;

Explanation

  • UPDATE customers SET name = :newCustomerName: Specifies the table and column to update.
  • WHERE id = :updateCustomerId: Specifies the condition to match the id field with the updateCustomerId variable.

Deleting Data

To remove data from a database, you use the DELETE statement.

Example: Deleting Data

DCL-S deleteCustomerId CHAR(10) INZ('CUST123');

EXEC SQL
  DELETE FROM customers
  WHERE id = :deleteCustomerId;

IF SQLCODE <> 0;
  // Handle deletion error
ENDIF;

Explanation

  • DELETE FROM customers WHERE id = :deleteCustomerId: Specifies the table and condition to delete the matching row.

Practical Exercise

Task

  1. Connect to a database.
  2. Insert a new customer record.
  3. Retrieve the newly inserted customer record.
  4. Update the customer's name.
  5. Delete the customer record.

Solution

DCL-S dbName CHAR(10) INZ('MYDB');
DCL-S userName CHAR(10) INZ('USER');
DCL-S password CHAR(10) INZ('PASSWORD');
DCL-S newCustomerId CHAR(10) INZ('CUST123');
DCL-S newCustomerName CHAR(50) INZ('John Doe');
DCL-S updatedCustomerName CHAR(50) INZ('Jane Doe');
DCL-S retrievedCustomerName CHAR(50);

// Connect to the database
EXEC SQL CONNECT TO :dbName USER :userName USING :password;
IF SQLCODE <> 0;
  // Handle connection error
  RETURN;
ENDIF;

// Insert a new customer
EXEC SQL
  INSERT INTO customers (id, name)
  VALUES (:newCustomerId, :newCustomerName);
IF SQLCODE <> 0;
  // Handle insertion error
  RETURN;
ENDIF;

// Retrieve the newly inserted customer
EXEC SQL
  SELECT name INTO :retrievedCustomerName
  FROM customers
  WHERE id = :newCustomerId;
IF SQLCODE <> 0;
  // Handle retrieval error
  RETURN;
ENDIF;

// Update the customer's name
EXEC SQL
  UPDATE customers
  SET name = :updatedCustomerName
  WHERE id = :newCustomerId;
IF SQLCODE <> 0;
  // Handle update error
  RETURN;
ENDIF;

// Delete the customer record
EXEC SQL
  DELETE FROM customers
  WHERE id = :newCustomerId;
IF SQLCODE <> 0;
  // Handle deletion error
  RETURN;
ENDIF;

Explanation

  • The solution follows the steps outlined in the task, demonstrating how to connect to a database, insert, retrieve, update, and delete a record.

Common Mistakes and Tips

  • Forgetting to check SQLCODE: Always check SQLCODE after executing an SQL statement to handle any errors.
  • Incorrect variable types: Ensure that the RPG variables match the data types of the database fields.
  • Not closing the connection: Remember to close the database connection when done to free up resources.

Conclusion

In this section, you learned how to access and manipulate databases using RPG. You covered the basics of connecting to a database, and performing CRUD (Create, Read, Update, Delete) operations. These skills are essential for developing robust business applications. In the next section, we will delve into data structures, which will further enhance your ability to manage and manipulate data efficiently.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved