Hack 2. Connect to SQL from a Program

You can access an SQL database from most programming languages, including Perl, PHP, Ruby, Java, and C#.

Working with a database from a programming language commonly involves a database connection and a statement cursor. In each language demonstrated here, you do the following:

Connect to the server

You specify the location of the server and name of the database. You also supply a username and password. In return, you obtain a connection handle that represents the connection. If you have several SQL commands to send you can reuse this connection. This process can fail if the server is not available or if your credentials are not accepted.

Execute an SQL SELECT command

This involves sending the SQL statement to the server via the connection handle. In return, you obtain a cursor. This process can fail if the SELECT statement includes a syntax error or your permissions are inadequate.

Retrieve the data

Typically you will loop until the cursor indicates that it is exhausted. At each iteration, your cursor points to a single row of data. You can get individual fields of the row from the cursor and then move on to the next row. Failure at this stage is uncommon but not unheard of (for example, your network may go down while you are in the middle of processing a result set).

Close the cursor and close the connection

Do this when you have finished issuing all your queries and are ready to disconnect from the database.

This pattern is a reasonable compromise between efficiency and utility, and there are many variations. If the data set is of a reasonable size, you might prefer to get the entire data set into a suitable data structure in one go. Each language given here will support that.

If your SQL statement does not return any data (it might be an INSERT or an UPDATE or a CREATE statement), there is no need for a cursor. Instead, you get a simple response that indicates whether an error occurred.

Each example shows a simple command-line program connecting to MySQL or SQL Server. You can connect to any database from any language.

The Nobel Prize data set used in this hack is available from http://sqlzoo.net/h.htm#data.

1.2.1. C#

In this example, the connection is to the SQLEXPRESS instance of SQL Server running on the local machine:

The Read method advances the cursor to the next line; it returns false when it reaches the end of the data set.

If you are connecting to a database other than SQL Server, you will need to use System.Data.Odbc rather than System.Data.SqlClient. You will obtain an OdbcCommand in place of SqlCommand. Your data reader will be an OdbcDataReader rather than an SqldataReader.

The cursor is an instance of a DataReader. The connection handle is the Connection property of the SqlCommand.

1.2.1.1. Compiling C#

You will need the .NET framework installed, which includes csc.exe, the C# compiler. You will find it in C:WINDOWSMicrosoft.NETFrameworkv2.0.50727 or a similar directory, so make sure that the directory is in your PATH environment variable:

The .NET framework includes an impressive collection of data adapters and containers intended to make life easier for the applications programmer. You can "wire up" controls on your forms (desktop application or web-based forms) so that they update the database or are updated by the database with scarcely a line of program code. You can use the Visual Studio range of products to build database-backed applications for the Web or for the desktop.

1.2.2. Java

You will need a JDBC driver for the SQL vendor that you are using. All of the popular systems have such connectors. You also have the option of using an ODBC/JDBC bridge. This example shows MySQL's Connector/J driver, which is available from http://www.mysql.com:

You compile Java to bytecode with javac (the Java compiler) and then execute the bytecode from java (this loads the Java Virtual Machine). You need to specify the location of the JDBC connector .jar file at runtime using the -cp (classpath) switch:

The following example uses the mysql_ functions. If you are using the MySQL extensions there is no need to explicitly create a variable to hold the connection, unless you have more than one connection or you prefer to make it more visible: