PrevNext

Embedding SQL in other languages

Almost all applications using databases are written in some other programming language to provide for example a graphical user interface or data structures not supported in SQL. There are several standard interfaces to SQL databases: for example

C

A C program using a database is written with special statements, where C and SQL can be mixed. The C program can pass C values to SQL and get result back. In the case of PostgreSQL the command ecpg converts each Sql statement into calls to the special PostgreSQL C library and the program can be compiled and run. For example: 

    exec sql begin declare section;
    char * name;
    char  * result;
    exec sql end declare section;
    ...
    strcpy(name,"Alfred Hitchcock");
    exec sql select director into :result  from films where title = :name ;
    printf("%s made %s",name,result);

perl

Perl has a library (DBI) for accessing databases. Although you have to specify what database system is used (Postgresql, mydb, oracle etc) the functions used are independent of the database type. 

    use DBI;
    # connect to the database
    my $dsn = "DBI:Pg:dbname=films;host=db;port=5432";
    my $dbh = DBI->connect($dsn, "lab", "lab",{ RaiseError => 1});
    # create a statement
    $example = $dbh -> prepare("SELECT name,born FROM ACTORS" );
    # run the statement
    $example -> execute();
    # fetch and print the results
    while ( ($actor,$born) = $example -> fetchrow_array()) {
      print "An actor: $actor born on $born\n";
    }
    $example -> finish();
    $dbh -> disconnect();

Java

Java has a standard library for accessing databases, an example: 

import java.sql.*;

public class JDBCSample {
  public static void main(String[] args) {
    // load the postgresql driver
    try {
      Class.forName("org.postgresql.Driver");
    }
    catch (ClassNotFoundException e) {
      System.err.println("Can't load the postgresql driver");
      return;
    }

    try {
      // connect to the database
      Connection con = 
      DriverManager.getConnection("jdbc:postgresql://db/films","lab","lab");
      // create and run an sql query
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT TITLE FROM FILMS");
      // retrieve the results and print them
     while(rs.next()) {
       System.out.println(rs.getString("TITLE"));
     }
     
     rs.close();
     stmt.close();
     con.close();
    }
    catch (SQLException se) {
      System.err.println("SQL Exception: "+se.getMessage());
      se.printStackTrace(System.err);
    }
  }
}

PrevHomeNext
Running SQL on Postgresql Bibliography