chapter 5

Client and Web Applications

Once a database has been installed, there are many ways to interact with it. The primary way is using SQL, either directly or indirectly. Some client programs connect to the database server directly and allow the user to type in SQL commands and display the results with minimal processing. Some programs are more elaborate, providing a web-based interface or other GUI. These applications typically provide some amount of post-processing of SQL output. In some cases, many operations can be carried out without direct knowledge of SQL. These will certainly help the novice database user, but may also satisfy many of the needs of more experienced users and developers. Sometimes these general-purpose applications are not sufficiently specific to the needs of database users. In that case, custom applications can be written. Many programming languages have extensions that allow data to be selected from the database and read into data structures for further operations. This chapter will consider ways of using ODBC, JDBC for Java, Perl::DBI, pg and pgdb for python and PDO for php. [view wordle] [hide wordle]

select sid, activity_outcome, "log_gi50_M" from pubchem.nci_h23 limit 10;
channel = odbcConnect("PostgreSQL30", uid="reader", pwd="something"); 
sql = "Select logp, xlogp From xlogp.test_set"; 
df = sqlQuery(channel, sql, max=0); 
use DBI;
use DBD::Pg;
#these variable must be set to values appropriate for your site
my $dbname = "book";
my $username = "reader";
my $password = "something";
my $host = "rigel";      
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host", $username, $password);
my $sql = "Select smiles,cas from nci.structure where matches(smiles,'c1ccccc1C(=O)NC')";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute;
while (my @row = $sth->fetchrow_array()) {
  print join "\t",@row;
  print "\n";
import pg
conn = pg.connect(dbname='book', host='rigel', user='reader')
sql = "Select smiles,cas from nci.structure where gnova.matches(smiles,'c1ccccc1 C(=O)NC')"
for (smi,cas) in (conn.query(sql).getresult()): 
  print smi, cas conn.close()
<?php $dbconn = pg_connect("host=rigel dbname=book user=reader password=something");
$sql = "Select smiles,cas from nci.structure where gnova.matches(smiles,'c1ccccc1C(=O)NC')"; 
$result = pg_query($dbconn, $sql); 
while ( $row = pg_fetch_array($result) ) {  print $row['smiles'] . "\t" . $row['cas'] . "\n"; } pg_close($dbconn) ?>
import java.sql.*;

public class JDBCDemo {
  public static void main( String[] args ) {
    try {
      // Connect to the database
      String url = "jdbc:postgresql://rigel/book";
      Connection con = DriverManager.getConnection(url, "reader");
      // Execute the SQL statement
      Statement stmt = con.createStatement();
      ResultSet resultSet = stmt.executeQuery(
       "SELECT smiles,cas from nci.structure where gnova.matches(smiles,'c1ccccc1C(=O)NC')");
      System.out.println("Got results!");
      // Loop thru all the rows
      while( ) {
        String smi = resultSet.getString( "smiles" );
        String cas = resultSet.getString( "cas" );
        System.out.println( smi + "\t" + cas );
    catch( Exception e ) {