NEBC Database Course 2008 Introduction to Writing Database - - PowerPoint PPT Presentation

NEBC Database Course 2008

Introduction to Writing Database Interfaces Hanny Kwesi Nuhu (haes@ceh.ac.uk) NERC Environmental Bioinformatics Centre

Contents of the talk

• Why database interfaces?
• Perl DBI – an introduction to Perl Database

Interface

• Perl CGI – databases interfaces on the web
• PHP – more database interfaces on the web
• JDBC – the Java API for database interaction
• ODBC – the Open Database Connectivity API

A simple interface for users

Example application using Database Interfaces

Applications making use Database interfaces

Client/Server Application SQL Database Database Interface API Database Driver

Why database interfaces?

• The open source databases MySQL and PostgreSQL come with sets
• f clients to manipulate data (e.g. dump the contents and structure of

tables, import data into tables, perform administrative operations or execute arbitrary queries on a command line interface).

• These general use programs are great for developers, but to make a

system for regular users a more friendly interface is needed.

• Therefore the databases have client-programming libraries. These

allow you to write you own programmes and provide you with the flexibility to satisfy whatever specialist requirements you may have.

Why database interfaces?

• You can customise input handling. You can prompt users for the data

they need to input, and do input validation to avoid errors creeping into the database.

• You can simplify queries. You can provide the mechanisms for a user

to search a database without needing to know SQL. Your program can generate queries based on anything from a friendlier command line interface, a graphical application or even a form on a web page.

• You can customize your output. With command line interfaces to

RDBMS's you get and essentially unformatted output – generally delimited by some character (|, tab etc). With your own interface you can change NULL elements to something more human readable, customise headers, customise the table format, format numbers however you like.

Why database interfaces?

• You can go beyond SQL constraints. SQL is not a procedural

language – it does not supply flow control structures. With a database interface you can construct complex flow control statements using the constructs of your preferred programming language.

• You can put database interaction into any programme. Many

programmes benefit from the ability to interact with a database, whether it's stock tracking, a knowledge base or customer information being stored. If you already have an application in Perl, or run a website with PHP, you can integrate database functionality into the software.

General characteristics of Database Interfaces

• Make connections to the database
• Create or prepare SQL statements as a query
• Execute the query
• Fetch the Results
• Disconnect

What exactly is an API?

• It is possible to run SQL commands by using the psql command line

program

• Anyone familiar with shell scripts might be tempted to use this to

automate database processing tasks.

• But for real database program, you want your code to talk directly to

the database – this is where the API comes in

• API = Application Programming Interface

The Perl DBI API

• Perl is a popular general purpose scripting language
• The Perl DBI (database interface) is implemented as a Perl module

that you can incorporate into your scripts: use DBI;

• You can then get Perl to run SQL commands, and process the results

The Perl DBI API

• The Perl DBI API
• The Perl DBI interacts with Perl DBD (database driver) modules.

These modules know how to connect with specific database engines. Hence there is a DBD::mysql, DBD::mSQL and DBD::Pg for PostgreSQL.

• This allows you to write generic DBI scripts using a standard set of

commands.

• The one specific set-up stage you need is to specify the correct DBD

module at connection time: $dbh = DBI->connect(“DBI:Pg.....”);

DBI/DBD Caveats

• SQL implementations differ between RDBMS engines. It is therefore

possible to write SQL that will not necessarily be portable. You should take care to make your SQL generic – for example, don't be tempted to use MySQL SHOW TABLES statement, as it not portable SQL.

• DBD modules provide engine specific types of information to allow

people to use particular features of their favourite RDBMS, so be careful which features you are using if you want to allow your users to use different RDBMS engines.

#!/usr/bin/perl use DBI; my ($dsn) = “DBI:Pg:sample_db:my.computer.na.me”; my ($dbh, $sth, @ary); #connect to database $dbh = DBI->connect ($dsn, 'user1', 'password', {AutoCommit => 1}); #define SQL command $sth = $dbh->prepare (“SELECT one_thing, other_thing FROM this_table”); #execute SQL command $sth->execute(); #display the array of results while (@ary = $sth->fetchrow_array()) { print join (“/t”, @ary). “\n”; } #clean up $dbh->disconnect();

Perl CGI+ DBI Making Applications

Web Browser Apache CGI Module Your Programme PostgreSQL DBI Module DBD::Pg

Perl CGI

• Perl coders can easily create web pages from their Perl programs using

CGI.pm

• CGI = Common Gateway Interface
• CGI.pm simply allows you to generate HTML on the fly from within your

Perl code.

• By using it with DBI, you can wrap up database interaction and HTML

generation in a single script! For example, you can create forms based

• n database content, or display query results in HTML.
• CGI.pm is written by Lincoln Stein – the eminent bioinformatician!

#!/usr/bin/perl use CGI; $query = new CGI; print $query->header; print $query->start_html(“Example CGI.pm Form”); print “<H1>Example CGI.pm Form</H1>\n” &print_prompt($query); &do_work($query); print $query->end_html; sub print_prompt { my($query) = @_; print “<EM>What's your name?</EM><BR>”; print $query->textfield('name'); print $query->checkbox('Not my real name'); print $query->submit('Action','Badger'); print $query->endform; print “<HR>\n”; } sub do_work { my($query) = @_; my(@values,$key); print “<H2>Here are the current settings in this form</H2>”; foreach $key ($query->param) { print “<STRONG>$key</STONG> -> ”; @value = $query param($key); → print join(“, “@values),”<BR>\n”; } }

The EnsEMBL Perl API

DBI EnsEMBL DBAdapter Your Programme EnsEMBL DBD::MySQL

The EnsEMBL Perl API

use Bio::EnsEMBL::DBSQL::DBAdapter; my $dbdame = 'rattus_norvigicus_core_20_3b'; my $db = new Bio::EnsEMBL::DBSQL::DBA(

• host => 'ensembldb.ensembl.org', -user => 'anonymous', -dbname =>

$dbname); my $slice_adapter = $db->get_SliceAdaptor(); #obtain a slice of the entire chromosome 1: my $slice = $slice_adapter->fetch_by_region('chromosome', '1', 1, 30_000_000); my $genes = $slice->get_all_Genes(); print scalar(@$genes), “ genes found in the slice.\n”

Further Support

• Perl Database API
• Useful Perl DBI and CGI links:

http://dbi.perl.org/ http://perldoc.perl.org/CGI.html

• Useful Ensembl Perl Links:

http://oct2007.archive.ensembl.org/info/software/api_installation.html http://www.ensembl.org/info/data/api.html http://www.ensembl.org/info/docs/api/registry.html http://www.ensembl.org/info/data/biomart.html

PHP

• PHP is also a scripting language a little like Perl, but specifically

focused on providing an API for embedding executable scripts into web pages.

• PHP stands for PHP:Hypertext Preprocessor
• When a browser sends a request for a PHP page on a web server the

PHP executes any script it finds on the page and then returns the

• utput of the script of the script in HTML.
• The following script produces a web page that returns the row count
• f a table.
• Further documentation and tutorials can be found on

http://www.php.net/manual/en/ http://w3schools.com/php/default.asp

PHP example

<html><head><title>Test PHP Script</title></head> <body> <p>Example PHP scripts</p> <?php $link = @mysqli_connect (“my.computer.na.me”. “dan”, “drowssap”, sample_db”)

• r die (“Could not connect to database”);

$result = mysqli_query ($link, “SELECT one_element FROM this_table”) or exit (); if ($row = mysqli_fetch_array ($result)) echo “<P>There were “ . $row[0] . “results returned”; mysqli_free_result ($result); ?> </body></html>

Java and the JDBC

• Java is an object oriented language developed by Sun Microsytems.
• Code is portable between systems – code is written and then

compiled on any platform into an intermediate form which is executed by machine specific “interpreters” aka the Java Virtual Machine.

• JDBC provides a standard library (API) for accessing RDBMS.
• The API standardises
• Ways to connect to the database
• Approach to initiating queries
• Data structure of the query result
• Like Perl DBI, the API does not standardise SQL syntax.
• JDBC may or may not be an acronym for Java Database

Connectivity!

Steps in using JDBC

• Load the driver:

Class.forName (“oracle.jdbc.driver.OracleDriver);

• Define the connection URL:

String host = “my.computer.na.me”; String dbName = “sample_db”; int port = 1234; String oracleURL = “jdbc:oracle:thin@” + host + “:” + dbName;

Steps in using JDBC

• Establish the connection:

String username = “dan” String password = “drowssap”; Connection connection = DriverManager.getConnection(oracleURL,username,password);

• Create a Statement:

Statement statement = connection.createStatement();

Steps in using the JDBC

• Execute a query:

String query = “SELECT one_element FROM this_table”; ResultSet resultSet = statement.executeQuery(query);

• Process the result:

while(resultSet.next()) { System.out.println(resultSet.getString(1)); }

• Close the connection:

connection.close;

Windows and ODBC

• The Open Database Connectivity (ODBC) interface by Microsoft allows

applications to access data in a range in a range of systems, using SQL as a standard for accessing the data

• It is primarily used on Microsoft systems to access things like Access or SQL

Server – the Microsoft RDBMS.

• Both the Perl DBI (via DBD::ODBC), PHP and JDBC can interact with

ODBC.

• One of the more useful features is to connect to PostgreSQL or MySQL

databases via Microsoft Access to use the GUI features.

• Databases can be queried in the 'JET' SQL syntax or native 'pass-through'

queries can be run.

• Get the PostgreSQL ODBC driver for your Windows machine here:

http://gborg.postgresql.org/project/psqlodbc

Acknowledgements

• Talk written by Dan Swan
• Talk amended for presentation Nov 2008 by Hanny K. Nuhu
• This work is licensed under the Creative Commons Attribution-

NonCommercial-ShareAlike License. To view a copy of this license, visit http://creativecommons.org/licenses/by-nc-sa/1.0/ or send a letter to Creative Commons, 559 Nathan Abbot Way Stanford, CA 94305, USA