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:

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.

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:

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:

user','password') or die(mysql_error( ));
mysql_select_db('dbname') or die(mysql_error( ));
$query = "SELECT winner,subject FROM nobel WHERE yr=1962";
$cursor = mysql_query($query) or die(mysql_error( ));
while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)) {
 echo $line{winner} . "	" . $line{subject}."
";
}
mysql_close( );
?>

 

1.2.4.1. Running PHP

Although PHP is normally used in web development, you can run it from the command line:

$ php Sample.php
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.5. Ruby

You can iterate over every row either using fetch_hash or each_hash as shown here:

require "mysql"
begin
 # connect to the MySQL server
 conn = Mysql.real_connect('localhost', 'scott', 'tiger', 'dbname')
 cursor = conn.query("SELECT winner,subject FROM nobel WHERE yr=1962")
 cursor.each_hash do |row|
 printf "%s	%s
", row['winner'], row['subject']
 end
rescue MysqlError => e
 print e.error( ), "
"
end

 

1.2.5.1. Running Ruby

To run Ruby:

$ ruby Sample.rb
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