Application development with C/C++ - DB2 Express-C: The Developer for XML, PHP, C/C++, Java, and .NET - page 163

EXEC SQL INSERT INTO inventory (PID, quantity, location) VALUES
(:PID, :quantity, :location);
if (sqlca.sqlcode!=0) {
if (sqlca.sqlcode == -803)
printf("\n Error: Product already exists in the database.\n");
else
printf("\n Error: Problem occurred when adding a new product.
Sqlcode: %d Sqlstate: %s\n",sqlca.sqlcode, sqlca.sqlstate);
return;
}
EXEC SQL COMMIT;
printf("\n Product successfully added.");
}
In Example 4-14 on page 162, we prompt the user for information on the product
to be added and populate the host variables with the supplied values. We then
use the host variables in the INSERT statement and check whether the
processing was successful.
Note that we add a COMMIT after the execution of the INSERT. This is because
when a database connection is initially established, the application starts a
transaction with any executable SQL statement such as SELECT, INSERT,
CREATE, GRANT, and so on. Within the transaction, any number of SQL
statements can be issued. This transaction is considered to be an atomic unit of
work where either all or none of the changes within a transaction are made. To
end the transaction, a COMMIT or a ROLLBACK must be issued. Issuing a
COMMIT will apply all the changes to the database, whereas issuing a
ROLLBACK cancels any changes to be made to the database.
4.3.12 Retrieving data
There are multiple ways to retrieve data from a database. Depending on whether
a single row or multiple row result set is returned, this determines whether a
cursor will be used. A cursor is a mechanism that is used to process each row of
a result set.
For example, assume we want to find all the products available at the store in our
INVENTORY table. Because it is possible that more than one row is returned from
the query, we use a cursor to retrieve and process the rows. Example 4-15 on
page 164 shows how to use a cursor.