Databases

The text assumes that you already have some basic experience with databases - most likely using Java's JDBC. The majority of server-side applictions will need to retrieve data from, and add data to tables in relational databases.

In learning exercises, the database usage is trivial. In most examples, a single table will suffice. Database operations are limited to data inserts, and simple data selection requests; all using limited, very generic SQL query statements. Consequently, only basic familiarity is requied.

The examples in the book use either Oracle or Microsoft Access. You may wish to use other databases. Linux users should have a Postgres database system as part of their Linux system. MySQL is an alternative for Windows and for Linux. An evaluation edition of DB2 can be downloaded from IBM; this has a free-usage period of two to three months which should be quite sufficient for use in an introductory course on web server technology.

The following sections in this page provide links to sites that do contain introductory tutorials and references on databases and SQL queries. There are also a few links to sites from where you can download database systems or obtain information on configuring systems and installing drivers.

SQL basics

• SQLCourse; this site is really a part of Internet.com. This is a site that you should bookmark because of its valuable links to numerous tutorials and reviews of all aspects of the Internet. (There is a supplementary course that teaches some slightly more advanced topics.)
• W3Schools has another introductory guide to SQL. Like Internet.com, W3Schools is a useful site to bookmark for its many links to tutorial information.
• You can get what claims to be A Gentle Introduction to SQL; this site has examples designed specifically for different database systems including Oracle, SQLServer, MySQL, etc. The tutorial examples that are embedded in the web pages run against a database that should be operational at Napier University.
• Yet another site is from the BayCon group; this is a general educational resource site.
• There is a site with tutorials that is associated with the O'Reilly publishing company.

Java and JDBC

Sun's main Java site includes a variety of tutorials on JDBC. These include a short introductory course on the original JDBC, and second course that introduces features that were added with JDBC 2.0. The main Java tutorial also has a JDBC 'trail'.

IBM's Developer Works site has many tutorials. There are more than one hundred articles and tutorials on JDBC available; they tend to be a little bit more advanced. They include tutorials on topics like management of pools of connections to databases, servlet related examples, EJB examples, and illustrations of how to combine XML with database usage. You have to get a signon for "Developer Works", but this is free. The site has new material added every week. Apart from Java, the main site has sections on Linux, XML, Web services, open source projects, and much more. It is well worth bookmarking.

Additional links to tutorials on JDBC are availabe from JSPIN.

Perl and Databases

Your Perl installation should already include documentation on database usage (as "perldoc" documents or HTML pages). There are numerous short tutorials and articles on the Web that illustrate simple use of Perl's DBI database interface; one is an introduction to DBI at perl.com,

PHP and Databases

The database chapters in the PHP manual include rather terse examples of database usage. Web based materials include a tutorial on PHP and MySQL at WebMonkey, and a variety of tutorials at DatabaseJournal.com. The PHP resource index naturally has database examples along with many other example fragments of PHP code. The PHPBuilder site has numerous PHP examples, including examples illustrating operation with Oracle, MySQL, and Postgres databases.

Databases

Microsoft Access

Microsoft Access will be a convenient choice for those using Windows. Academic versions of Microsoft Office products are cheap so Access should be available. Its graphical interface makes it simple to set up the database tables.

Before you can use Access from a program, you must define an ODBC "Data Source". This set up operation is performed using the ODBC Data Sources component in the Windows Control Panel. Essentially, what you are doing is providing a symbolic database name, the name that is used in your Perl/PHP/Java program, and its mapping to your Access (or other ODBC) database.

Instructions on how to set up an ODBC data source are available at Microsoft's Developer Network site. But you might prefer the instructions with visual screen shots that are included at Mathworks.com.

Once you have defined your ODBC Data Source, you can access the database using an ODBC driver. You may need to download a Perl driver if one is not included in your Perl installation. The PHP download should include its ODBC driver. The standard Java Development Kit includes Sun's JDBC-ODBC driver.

Microsoft SQL Server

Teaching laboratories may have Microsoft's SQL Server accessible through a local network. ODBC style drivers can be used to access SQL Server, but there are more efficient drivers available. A PHP download should include its drivers for SQL Server. JDBC drivers can be obtained from Merant. Your school should organize the necessary drivers and install them on laboratory machines.

DB2

If you've established a (free) membership at IBM's Developer Works site, you can get your free evaluation copy of DB2 by download. (Uhm. It is about 600Mbytes, might be faster to order the CD.) There are drivers for Perl available from the same link page. PHP uses its ODBC interface for accessing DB2 databases (it is a native mode driver, it is just the APIs for DB2 and ODBC are the same). The DB2 download includes its Java drivers and example code.

Oracle

Oracle also has a download site from where you can obtain an evaluation copy of "Personal Oracle" (again, an impossibly large download) and simpler things such as the Java drivers. (As with the IBM site, you need a free Oracle login. The page need to establish an "account" with Oracle will pop-up as soon as you reach an access controlled part of the site.) The download has installation and set up instructions. Your PHP system should include Oracle drivers; you may have to use Perl's PPM module manager to install drivers for Perl. Oracle's Java drivers come as a zip file ("classes12.zip").

If you are using Oracle in a laboratory environment, your school should already have installed all necessary drivers and have created database accounts for individual students.

MySQL

You get your copy of MySQL from the MySQL.com site. The download page on this site has ODBC, DBI (Perl), and Java drivers as well as the actual database product. Online documentation at the MySQL site includes installation instructions. Other articles on the site cover things such as working with PHP.

PostreSQL

The site for Postgres is at Postgresql.com. Some version of Postgres should be included with most Linux releases. There is a tutorial at PHPBuilder.com on setting up Postgres and PHP. If JDBC drivers are not already included in your Postgres system, you can collect them from the Postgres site.

These database products all have utility programs that allow you to create tables and submit simple SQL queries. These may be elaborate graphical user interface programs such as Microsoft Access, or simple command line utilities like the sqlplus program used with Oracle. It will be best if you create tables, enter initial data, and test run your SQL queries using the appropriate database utility program.