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 Database Database SQL Database Driver Interface API
Why database interfaces? The open source databases MySQL and PostgreSQL come with sets ● of 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 DBI Module PostgreSQL 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 on 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 Your Programme EnsEMBL DBAdapter DBI DBD::MySQL EnsEMBL
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 output of the script of the script in HTML. The following script produces a web page that returns the row count ● of 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”) or 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;
Recommend
More recommend
