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.

1.2.1. C#

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

using System; using System.Data.SqlClient; namespace SQLHacks { class Sample { static void Main(string[] args) { try{ SqlCommand comm = new SqlCommand( ); comm.Connection = new SqlConnection( "Data Source=(local)\SQLEXPRESS;" + "Initial Catalog=dbname;" + "user=username;password=password;"); comm.CommandText = "SELECT winner,subject FROM nobel WHERE yr=1962"; comm.Connection.Open( ); SqlDataReader cursor = comm.ExecuteReader( ); while (cursor.Read( )) Console.Write("{0} {1} ",cursor["winner"],cursor["subject"]); comm.Connection.Close( ); }catch (Exception e){ Console.WriteLine(e.ToString( )); } } } }

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

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:

C:>csc Sample.cs Microsoft (R) Visual C# .NET Compiler version 7.10.6001.4 for Microsoft (R) .NET Framework version 1.1.4322 Copyright (C) Microsoft Corporation 2001-2002. All rights reserved. C:>sample John C. Kendrew Chemistry Max F. Perutz Chemistry John Steinbeck Literature Francis Crick Medicine James Watson Medicine Maurice Wilkins Medicine Linus Pauling Peace Lev Landau Physics

1.2.1.2. Other C# considerations

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:

import java.sql.*; public class Sample{ public static void main(String[] args){ try{ Class.forName("com.mysql.jdbc.Driver").newInstance( ); Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost/dbname", "username","password"); ResultSet cursor = conn.createStatement( ).executeQuery( "SELECT winner,subject FROM nobel WHERE yr=1962"); while (cursor.next( )){ System.out.println(cursor.getString("winner")+" "+ cursor.getString("subject")); } conn.close( ); } catch (Exception e){ System.err.println(e); } } }

1.2.2.1. Running Java

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:

$ javac Sample.java $ java -cp ../mysql-connector-java-3.1.13-bin.jar:. Sample John C. Kendrew Chemistry Max F. Perutz Chemistry John Steinbeck Literature Francis Crick Medicine James Watson Medicine Maurice Wilkins Medicine Linus Pauling Peace Lev Landau Physics

If you are executing Java on a Windows platform you need to use a semicolon in place of the colon:

C:>javac Sample.java C:>java -cp C:mysql-connector-java-3.1.13-bin.jar;. Sample

1.2.3. Perl

You can connect to a database using the DBI interface (see http://dbi.perl.org), which will help make your code vendor neutral:

#!/usr/bin/perl use strict; use warnings; use DBI; my $conn = DBI->connect("dbi:mysql:dbname:localhost", "username", "password") or die "Cannot connect "; my $cursor = $conn->prepare("SELECT winner, subject FROM nobel WHERE yr=1962") or die $conn->errstr; $cursor->execute or die $conn->errstr; while (my ($name,$region) = $cursor->fetchrow_array( )){ print "$name $region "; } $conn->disconnect;

1.2.3.1. Running Perl

And to run Perl:

$ perl Sample.pl John C. Kendrew Chemistry Max F. Perutz Chemistry John Steinbeck Literature Francis Crick Medicine James Watson Medicine Maurice Wilkins Medicine Linus Pauling Peace Lev Landau Physics

See "Filter Rows and Columns" [Hack #8] for more Perl syntax.

1.2.4. PHP

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: