Client and Web Applications - Design and Use of Relational Databases in Chemistry - page 37

chapter 5
Client and Web Applications
5.1 Introduction
Once a database has been installed, there are many ways to interact with it.
The primary way is using structured query language (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 (graphical user interface). These applications
typically provide some amount of postprocessing 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 sat-
isfy 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 considers ways of using ODBC (Open
Database Correctivity), JDBC for Java, Perl::DBI (Database Interface), pg
and pgdb for Python, and PDO for PHP.
5.2 Command Line Programs
Command line programs are one simple way to interact with the data-
base server. Each RDBMS provider supplies a command line program. For
example, PostgreSQL supplies psql, Oracle supplies sqlplus, and MySQL
supplies mysql. These command line shells allow the user to type in SQL
commands. The results are displayed, usually with minimal formatting.
There are other non-SQL commands that are unique to each RDBMS. For
example, the PostgreSQL psql command \d mytable will describe the
nature of the table named mytable, showing the names and data types
of the columns. The corresponding Oracle plpsql command is describe
mytable . For simple inquiries, command line SQL shells such as these
are very useful.
Here is a sample SQL command and output from the psql command
line client for PostgreSQL.
37