Managing & Using MySQL, 2nd Edition

Connections

The starting point of your database interaction is in making a connection. The details behind what exactly constitutes a connection vary from API to API. Nevertheless, making a connection is basically establishing some sort of link between your code and the database. The variation comes in the form of logical and physical connections. Under some APIs, a connection is physical; i.e., a network link is established. Other APIs, however, may not establish a physical link until long after you make a connection, to ensure that no network traffic takes place until you actually need the connection.

The details about whether a connection is logical or physical should not generally concern a database programmer. The important thing is that once a connection is established, you can use that connection to interact with the database.

Once you are done with your connection, you need to close it and free up any resources it may have used. In a long-running application such as an Internet daemon process, a badly written application can eat up database resources until it locks up the system.

Part of cleaning up after yourself involves proper error handling. Some programming languages make it easy for you to remember to handle exceptional conditions (network failure, duplicate keys on insert, SQL syntax errors, etc.); but regardless of your language of choice, you must be aware of the error conditions that can arise from a given API call and act appropriately for each exceptional situation. ...