Data Processing (MySQL & mSQL)

8.2. Data Processing

Part I, "Getting Started with MySQL and mSQL", introduced the concepts of transaction management and result sets. A database application is nothing more than a tool for managing database transactions and processing result sets. For example, if you have an address book application, your processing of result sets is the grabbing of each row from the database and displaying it for the user. Your transaction management simply amounts to making sure that an update to the address and person tables are handled as a single unit.

NOTE

As we have mentioned before, MySQL and mSQL have no support for transaction management. Any modification you make to the database is automatically committed when you send it. This limitation requires you to go to special lengths to make sure you do not end up with corrupt data from transactions that fail in the middle of two related accesses.

The other two important pieces to database application flow are connection and disconnection. It stands to reason that before you actually issue a query, you should first connect to the database. It is not uncommon, however, for people to forget the other piece of the puzzle -- cleaning up after themselves. You should always free up any database resources you grab the minute you are done with them. In a long-running application like an Internet daemon process, a badly written system can eat up database resources until it locks up the system.

Part of cleaning up after yourself involves proper error handling. Better programming languages make it harder for you to fail to handle exceptional conditions (network failure, duplicate keys on insert, SQL syntax errors, etc.); but, regardless of your language of choice, you must make sure that you know what error conditions can arise from a given API call and act appropriately for each exceptional situation. The MySQL and mSQL C libraries provide a rowset-based look at your database. By rowset based, we mean that the C libraries enable you to deal directly with database data as it exists conceptually in the database. Chapter 13, "C and C++", goes into the practical details of programming in this model using the MySQL and mSQL C APIs.

Accessing a relational database from an object-oriented environment exposes a special paradox: the relational world is entirely about the manipulation of data while the object world is about the encapsulation of data behind a set of behaviors. In an object-oriented application, the database serves as a tool for saving objects across application instances. Instead of seeing the query data as a rowset, an object-oriented application sees the data from a query as a collection of objects.