DatabaseClients

Database Clients
Peter Komisar © Conestoga College version 1.0

reference: Core MySQL, Leon Atkinson, Prentice-Hall Publishing,
ODBC Overview, Why Was ODBC invented, Microsoft .NET Dev Center,
http://msdn2.microsoft.com/en-us/library/ms714587(VS.85).aspx

VBScript Database Tutorial
http://www.electronics.dit.ie/staff/srooney/Output/VBScript%20Database%20Tutorial.pdf

"Open Database Connectivity (ODBC) is a widely accepted
application programming interface (API) for database access.
It is based on the Call-Level Interface (CLI) specifications from
Open Group and ISO/IEC for database APIs and uses Structured
Query Language (SQL) as its database access language.

ODBC is designed for maximum interoperability - that is, the
ability of a single application to access different database
management systems (DBMSs) with the same source code."

"Database applications call functions in the ODBC interface,
which are implemented in database-specific modules called
drivers. The use of drivers isolates applications from database-
specific calls in the same way that printer drivers isolate word
processing programs from printer-specific commands. Because
drivers are loaded at run time, a user only has to add a new driver
to access a new DBMS; it is not necessary to recompile or relink
the application."

The following is also quoted from the Microsoft site.

"Historically, companies used a single DBMS. All database
access was done either through the front end of that system
or through applications written to work exclusively with that
system. However, as the use of computers grew and more
computer hardware and software became available, companies
started to acquire different DBMSs. The reasons were many:
People bought what was cheapest, what was fastest, what
they already knew, what was latest on the market, what worked
best for a single application. Other reasons were reorganizations
and mergers, where departments that previously had a single
DBMS now had several."

// companies migrated from using single to multiple DBMSs.

The issue grew even more complex with the advent of
personal computers. These computers brought in a host
of tools for querying, analyzing, and displaying data,
along with a number of inexpensive, easy-to-use databases.

From then on, a single corporation often had data scattered
across a myriad of desktops, servers, and minicomputers,
stored in a variety of incompatible databases, and accessed
by a vast number of different tools, few of which could get at
all of the data.

// PCs and inexpensive databases led to 'data scatter'

The final challenge came with the advent of client/server
computing, which seeks to make the most efficient use of
computer resources. Inexpensive personal computers (the
clients) sit on the desktop and provide both a graphical front
end to the data and a number of inexpensive tools, such as
spreadsheets, charting programs, and report builders.

// two and three tier systems provided the need to connect
// front end resources easily with mini and mainframe
// computers hosting the DBMSs.

Minicomputers and mainframe computers (the servers) host
the DBMSs, where they can use their computing power and
central location to provide quick, coordinated data access.

// the question became as follows

How then was the front-end software to be connected to the
back-end databases?

A similar problem faced independent software vendors (ISVs).
Vendors writing database software for minicomputers and
mainframes were usually forced to write one version of an
application for each DBMS or write DBMS-specific code for
each DBMS they wanted to access.

// vendors also were having trouble writing platform and software
// specific drivers

Vendors writing software for personal computers had to write
data access routines for each different DBMS with which they
wanted to work. This often meant a huge amount of resources
were spent writing and maintaining data access routines rather
than applications, and applications were often sold not on their
quality but on whether they could access data in a given DBMS.

// Summary of the need

What both sets of developers needed was a way to access
data in different DBMSs. The mainframe and minicomputer
group needed a way to merge data from different DBMSs
in a single application, while the personal computer group
needed this ability as well as a way to write a single application
that was independent of any one DBMS. In short, both groups
needed an interoperable way to access data; they needed
open database connectivity.

Microsoft's UDA in Relation to JDBC 2.0

Microsoft has a number of APIs such as OLE ( Object
Linking and Embedding) DB, ADO (ActiveX Data Objects)
and RDS (Remote Data Service). OLE DB and ADO are
object-oriented interfaces to databases that can do SQL.
ADO is a newer version with many functionalities that are
similar to JDBC 2. RDS is a database API written in
Microsoft's Java which is not portable.

Microsoft has created an umbrella term UDA or Universal
Data Access to encompass and streamline these APIs.
JDBC 2.0 contains all the functionality described in UDA
and goes a step further, for instance, by providing support
for SQL3.

The JDBC API Tutorial and Reference' by White et
al. puts the different Microsoft APIs in context relative
to the JDBC API.

Comparing Microsoft Database APIs to JDBC

UDA // Microsoft     <--> JDBC 2.0    // Java
   |___OLE
   |___DB
   |___ADO

// should check to see what SQL3 is now present
// in Microsoft offerings

connects to a MySQL server via Open Database
Connectivity (ODBC) API. Support is available for
ODBC connectivity for Windows, Unix and Mac
OS X platforms.

The MySQL manual states this connector "enables developers
to create .NET applications that use data stored in a MySQL
database. Connector/NET implements a fully-functional
ADO.NET interface and provides support for use with
ADO.NET aware tools. Applications that want to use
Connector/NET can be written in any of the supported
.NET languages.

"works with Connector/NET and Visual Studio 2005. The
plugin is a MySQL DDEX Provider, which means that you
can use the schema and data manipulation tools within
Visual Studio to create and edit objects within a MySQL
database".

provides driver support for connecting to MySQL from
a Java application using the standard Java Database
Connectivity (JDBC) API. Connector/MXJ is a tool that
enables easy deployment and management of MySQL
server and database through your Java application.

a Windows-only connector for PHP that provides the mysql
and mysqli extensions for use with MySQL 5.0.18 and later.

MySQL also supports connectivity for Perl, Python and the
PHP on other platforms.

Typically, you need to install Connector/ODBC only
on Windows machines. For Unix and Mac OS X
you the native MySQL network is used. To install
the Connector/ODBC connector on a Unix host, an
ODBC manager must also be installed.

Connector/ODBC Versions

Two version of Connector/ODBC are available
at the time of this writing:

He then expands the example to include a
SELECT query and includes some error
handling code.

#include <stdio.h>
#include <mysql/mysql.h>

int main(int argc, char *argv[]){
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;

/*
   * Initialize connection framework
*/
if (!mysql_init(&mysql)
    {
   fprintf(sterr, "Unable to initialize MYSQL struct! \n");
   exit( ) ;
   }

   /*
   * connect to database with default values
*/
if (!myslq_real_connect(&mysql, "localhost", "root",
   "password","mysql",0,NULL,0 ))
{
fprintf(stderr, "d: %s\n",
       mysql_errno(&mysql), mysql_error(&mysql));
       exit( );
       );
if (mysql_query(&mysql,
"SELECT User,Host FROM user ORDER BY 1,2 ))
   {
/*
   ** Query failed!
   */
fprintf(sterr, "%d: %s\n,
   mysql_errno(&mysql), mysql_error(&mysql_error));
   }
else
{
/*
   * Buffer results, count fields
*/

while (row=mysql_fetch_row(result)){
    {
      lengths=mysql_fetch_lengths(results);
      for(i=0;i<num_fields;i++)
   {
        printf("[%.*s]", (int)lengths[i],
               row[i] ? row [i] : "NULL");
      }
   printf("\n");
}

In all the clients, regardless of language there are
common characteristics that repeat themselves.
The database needs to be located, connected to
via a socket connection, queried and the result set
read, typically a compound loop where the outer
loops iterates the rows, and the inner loop reads
the fields from each row.

The JDBC API is a set of Java classes and interfaces
which provide a standard specification that developers
can use to create Java database applications. The primary
function of JDBC is to send SQL statements to database
systems. This functionality is being extended in JDBC 2
to include different data types.

The great advantage of JDBC is that it allows a single,
portable Java program to communicate with several
different databases regardless of who the vendor is,
whether it be Oracle, IBM, Microsoft or MySQL, to
name a few.

// one Java JDBC program can communicate with many
// databases from different vendors

JDBC is part of the standard library that accompanies
the Standard Edition of Java Development Kit. JDBC is
relatively easy to program allowing the creation of secure
and robust database applications. The ease and speed
with which programs can be developed and deployed
makes using Java's JDBC API very economical.

// JDBC is 'built into' the Java Development Kit making
// building database applications easy, fast and economical

The basic operations that are performed in a JDBC
program include establishing a connection to a data
source (a database) , sending queries in the form of
SQL statements and processing the results that are
returned.

JDBC, then, can be thought of as an environment
created inside a Java program that allows a database
to be queried using SQL statements. JDBC also is
designed to provide a foundation on which other
database applications can be built.

An example of an API that builds on JDBC is SQLJ.
SQLJ is a specification that has been developed by a
consortium which includes IBM, Oracle and Sun. SQLJ
supplies a preprocessor that allow a programmer to
intersperse SQL statements into Java code. The SQLJ
preprocessor sorts the mix and restores the code to a
form that can be executed using regular JDBC calls.
Other schemes where objects are mapped to relational
databases also take advantage of JDBC at a lower
level.

JDBC builds on some of ODBC's basic design features.
Both ODBC and JDBC are based on Open Group's earlier
design called SQL CLI, Call Level Interface. (Open Group
was formerly known as X/Open.)

While ODBC is written in the C programming language,
JDBC is written all in Java code. ODBC is a widely used
interface for accessing relational databases, offering the
ablility to connect to almost all databases.

The JDBC development team have supplied the 'JDBC-
ODBC Bridge' to take advantage of ODBC's wide spread
adoption. The bridge was created for several reasons.
Because ODBC is written in 'C' it could not be used
directly in Java. Because of the extensive use of pointers
in ODBC, the program did not translate well directly into
Java.

In addition, calling ODBC directly using Java's Native
Interface was not a good solution as Java's portability
was lost. There were also security concerns to this
approach as the C code is 'outside' Java's security
perimeter. ODBC is also considered complicated and
hard to learn. Java's JDBC greatly simplifies connecting
to a database making it easy to learn by comparison.

For these reasons, the Java team created the JDBC-
ODBC Bridge that encapsulates a Java connection to
the functionality of ODBC. This was a clever way of
leveraging the large support for ODBC for Java
developers.

The following code sample is from Peter Van der
Linden's 'Just Java' text. It is good as it ties in the
use of Microsoft's DSN, Java and Microsoft's Access
which is available on the machines at the school and
probably a large number of machines in the reader's
home.

1) Run Peter Van der Lindens simple class. First you will have to
set up a Microsoft type DSN. Here are the steps if you are not
familiar with it.

Setting up a Microsoft DSN

1) open odbcad32.exe in c:\windows\system32
2) when the screen opens, press add
3) highlight Microsoft Access Driver (*mdb) & press Finish
4) in ODBC Microsoft Access Setup enter myDSN
5) the other field is not important
6) Press 'select' to make a database file to associate with the DSN
7) The file to find is under c:-> Program Files-> Microsoft Office -> Office 11
-> Samples -> Press Enter ( Northwind.mdb shows up in left column )
8) Press OK and it should be ready to go!
.

Following is the Java code sample. It needs to be
compiled then run.

Java JDBC Code Example

// from ' Just Java ' by Peter Van der Linden,
// Sun Microsystems Press

class simple{
public static void main(String[]args){
String url = "jdbc:odbc:myDSN" ;
String query = "SELECT * FROM Customers"
                        + " WHERE CustomerID = 'QUICK' ";
try{
         // Load the jdbc-odbc bridge driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         // Attempt to connect to a driver
      Connection con = DriverManager.getConnection( url," "," " );
         // Create a Statement object so we can submit SQL statements to the driver
     Statement stmt = con.createStatement( );
         // Submit a query, creating a ResultSet object
     ResultSet rs = stmt.executeQuery(query);
         // Display all columns and rows from the result set
     printResultSet(rs);
     rs.close( );
     stmt.close( );
     con.close( );
     }
     catch(SQLException ex){
            while (ex != null){
               System.out.println("SQL Exception: " + ex.getMessage( ));
                                            ex = ex.getNextException();
                      }
              }
      catch(java.lang.Exception ex){
            ex.printStackTrace( );
            }
    }

private static void printResultSet(ResultSet rs) throws SQLException{
         int numCols = rs.getMetaData( ).getColumnCount( );
             while( rs.next( ) ){
                     for ( int i = 1; i < numCols; i ++ ){
                           System.out.print(rs.getString(i) + " | " );
                           }
                    System.out.println( );
                    }
          }
}