Fetching and Updating Rows in Proc*C

Fetching and updating rows are essential operations when working with databases in C using Proc*C. The ability to retrieve data from a database and modify it using cursors provides a powerful means of interaction. In this tutorial, we will explore how to effectively fetch and update rows in Proc*C, with examples and step-by-step explanations to illustrate the significance of these operations in building efficient and dynamic database-centric C applications.

1. Fetching Rows

Fetching rows from a database involves using a cursor to retrieve one or more rows based on a specified SQL query. Once a cursor is declared and opened, rows can be fetched one by one using the EXEC SQL FETCH statement.

      /* EXEC SQL BEGIN DECLARE SECTION; */
      int employee_id;
      char employee_name[50];
      float salary;
      /* EXEC SQL END DECLARE SECTION; */
  /* Declare and open the cursor */
  /* EXEC SQL DECLARE emp_cursor CURSOR FOR
           SELECT employee_id, employee_name, salary FROM employees
           WHERE department_id = 100;
  EXEC SQL OPEN emp_cursor; */

  /* Fetch and process rows from the cursor */
  while (SQLCODE == 0) {
      /* EXEC SQL FETCH emp_cursor INTO :employee_id, :employee_name, :salary; */
      printf("Employee ID: %d, Employee Name: %s, Salary: %.2f\n", employee_id, employee_name, salary);
  }

  /* EXEC SQL CLOSE emp_cursor; */

In this example, we declare a cursor emp_cursor to retrieve employee_id, employee_name, and salary from the employees table based on the department_id (100 in this case). We open the cursor and use a loop to fetch each row from the cursor. The values of employee_id, employee_name, and salary are updated with each fetch, and we print them to the console.

2. Updating Rows

Updating rows in a database involves using a cursor to select the rows to be updated and then using the EXEC SQL UPDATE statement to modify the data.

      /* EXEC SQL BEGIN DECLARE SECTION; */
      int employee_id;
      float new_salary;
      /* EXEC SQL END DECLARE SECTION; */
  /* Declare and open the cursor */
  /* EXEC SQL DECLARE emp_cursor CURSOR FOR
           SELECT employee_id, salary FROM employees
           WHERE department_id = 200;
  EXEC SQL OPEN emp_cursor; */

  /* Fetch rows and update data using the cursor */
  while (SQLCODE == 0) {
      /* EXEC SQL FETCH emp_cursor INTO :employee_id, :salary; */
      new_salary = salary + 500.00;
      /* EXEC SQL UPDATE employees SET salary = :new_salary WHERE CURRENT OF emp_cursor; */
  }

  /* EXEC SQL CLOSE emp_cursor; */

In this example, we declare a cursor emp_cursor to retrieve employee_id and salary from the employees table based on the department_id (200 in this case). We open the cursor and use a loop to fetch each row from the cursor. For each row, we update the salary by adding 500.00 to the current value, and then we use the cursor to update the row in the database.

3. Common Mistakes with Fetching and Updating Rows in Proc*C

• Forgetting to open the cursor before fetching or updating rows.
• Not checking the SQLCODE after each fetch or update, leading to unexpected behavior.
• Using incorrect host variable data types in the cursor declaration or update statement.

4. Frequently Asked Questions (FAQs)

• Q: Can I fetch and update multiple rows using a single cursor?
  A: Yes, you can use a cursor to fetch multiple rows and update them one by one in a loop.
• Q: How do I handle errors while fetching or updating rows?
  A: You can check the SQLCODE after each cursor operation and handle errors accordingly using error handling techniques.
• Q: Can I use a single cursor to fetch from one table and update in another?
  A: Yes, you can use a cursor to fetch data from one table and then use the fetched data to update rows in another table.
• Q: Is it necessary to close the cursor after fetching or updating rows?
  A: Yes, it is good practice to close the cursor after you are done using it to release database resources.
• Q: Can I use cursors with dynamic SQL statements?
  A: Yes, you can use cursors with both static and dynamic SQL statements in Proc*C.

5. Summary

Fetching and updating rows in Proc*C are essential operations for interacting with a database in C programs. Using cursors, you can efficiently retrieve data from a database and modify it as required. Avoid common mistakes and refer to the FAQs for any queries related to fetching and updating rows. With this understanding, you can effectively utilize these operations to develop powerful and database-centric Proc*C applications that interact seamlessly with the database.