Setting up a JDBC Database       Peter Komisar
                                                                                                                                                       Nov 14 / 2000


There are a number of options besides buying a database. One is to use the Cloudscape
database which is bundled with the Enterprise Edition of the JDK. This will only run on NT,
Solaris or Linux so we will pass this option onto to those individuals running these platforms.

The second option is to use the very compact and fast open-source database, called
HyperSonicSQL. This all java database is is only about 175K. It is a single threaded
model so it won't be ready for a huge amount of net traffic. Nonetheless, it is a very able
tool for light service and testing JDBC programs in Java. I have a recipe for deploying
hsql below.

Our third option is to use of the JDBC:ODBC bridge to connect to Microsoft's Access
database. This info is useful to demonstrate the use of the JDBC:ODBC bridge and also
to take advantage of the large installed base Access enjoys.

This approach comes down to creating a DSN or Data Source Name for a file that will
be the store for an Access Database session. The bulk of what needs to be done is executed
by a odbc administration program called odbcad32.exe found in Windows/System
(or represented by an icon in the Control Panel ).

 ( I will provide a step-by-step for this process at a future date. In the meantime note,
'Just Java' by P.V. Linden documents this info well and there are many code examples
on the net that use the jdbc-odbc bridge. Info on creating a DSN should also be available
from Micosoft documentation.)


Installing HypersonicSQL

1. Go to http://hsql.oron.ch/.
2. Click Download and download the database
3. Extract the database to a directory where you will do your JDBC ( i.e SQL or JDBC)
    You can use an evaluation copy of WinZip to do this. ( http://www.winzip.com/ )
 
 Details of Winzip

 1) Once you have Winzip installed double click the desktop icon to open it 
 2) When open, click open and find the hsql_143.zip file and open it.
 3) When it shows you a bunch of files go into the menu's and select 'Select All'
 4) With everything highlighted select 'Extract'. It will ask where to. Send it to c:\
     ( or a directory of your choice. It will create it's own directory structure ).
 

      // ie. Open--> SelectAll--> Extract --> MySQLDirectory

4. Now you have the source files. You still need to compile them. You can use build.bat. It is
    located in hypersonicsql / src /. If you don't have jykes (supposedly most of us) then that
    command has to be replaced with javac in build.bat. Make this change in the editor of your
    choice. Then switch to the src directory and run by entering build at the command line
    followed by enter.
    *  found this link to jikes, a very fast compiler created at IBM
      http://oss.software.ibm.com/developerworks/opensource/jikes/project/download/
 
 contents of build.bat        // from the hypersonicSQL download

  rem  If you don't have jikes replace 'jikes' with 'javac' on the next line
  rem  jikes *.java org\hsql\*.java org\hsql\util\*.java
          javac *.java org\hsql\*.java org\hql\util\*.java

  jar -cf ..\demo\hsql.jar *.class .\org\hsql\*.class .\org\hsql\util\*.class
  del *.class
  del org\hsql\*.class
  del org\hsql\util\*.class



 Note the jikes line has already been remmed out and replaced one with javac. The script file 
 compiles all the neccessary classes and stores them in a jar file.  It then removes all the 
 uncompressed. class files. This takes a little while as there is quite a few compiles to do 
 followed by the compression and deletes.
.

5. To make the the Hypersonic SQL package available copy the file 'hsql.jar' to where your jdbc
    program source code will be. (You will find one in the demo directory of the hierarchy that the
    build routine created.)

6. Copy the program below into your directory along side the hsql.jar.

7. Compile it as usual:
    javac T_Five.java.

8. Run the program using the following form from the directory that holds the jdbc program

    java -classpath %CLASSPATH%;hsql.jar jdbc_program

//  tells java where to look preventing the driver not found message
//  java.lang.ClassNotFoundException: org.hsql.jdbcDriver

Note when creating your own programs using hypersonicSQL, the file that the class loads
the driver from is referenced by " org.hsql.jdbcDriver " and the url describing the location
of the database referenced in the driver is" jdbc:HypersonicSQL: . "This is the 'In-Memory'
form of the url used to reference the database. You can substitute a name for the period in the
driver url and a a file will be created with that name, representing the database. Also note that
"sa" is a predefined accepted password you can use to access the database.

Once you have a permanent home for the database you can set path and classpath so you
won't have to bother with the extra directives. Also you can lookup how to set your users
and passwords.

**
 
 T_Five connects to a hsql database builds a table and prints out of of its rows 


import java.sql.*;

class T_Five{
   static String[] stray;
   static int key;
   public static void main(String args[]){

   key=1001; 
   String query="CREATE TABLE Q "         +
  "(SNumber INTEGER, Q1 VARCHAR(16), Q2 VARCHAR(16), Q3 VARCHAR(16) )"; 

   String queryIN1="INSERT INTO Q VALUES(1000,'A','B','C')"; 
   String queryIN2="INSERT INTO Q VALUES(1001,'Q','A','DNA')"; 
   String queryIN3="INSERT INTO Q VALUES(1003,'B','A','D')"; 
   String queryIN4="INSERT INTO Q VALUES(1004,'C','B','A')";
   String queryIN5="INSERT INTO Q VALUES(1005,'I','I','I')"; 

try{
    Class.forName("org.hsql.jdbcDriver");
    Connection con= DriverManager.getConnection("jdbc:HypersonicSQL:.","sa","");
    //a name for a file can be substituted in for the period ending the driver url
     Statement stmt=con.createStatement();
    stmt.executeUpdate(query);
    stmt.executeUpdate(queryIN1);
    stmt.executeUpdate(queryIN2); 
    stmt.executeUpdate(queryIN3);
    stmt.executeUpdate(queryIN4);
    stmt.executeUpdate(queryIN5);

 ResultSet rs=stmt.executeQuery("SELECT * FROM Q WHERE SNumber LIKE " + key); 

   printResultSet(rs); 
   rs.close();
  stmt.close();
  con.close();
  }
  catch(SQLException sx){
          while(sx!=null){
            System.out.println(sx.getMessage() + " " + sx.getNextException());
            break;} 
           }
  catch(ClassNotFoundException cnf){
           System.out.println("Class not found"+ cnf.getMessage());
           }
   } // end of main

   private static void printResultSet(ResultSet rs) throws SQLException{
        stray=new String[11];
        int numCols=rs.getMetaData().getColumnCount();
        while(rs.next()){
           for(int i=1;i<numCols+1;i++){
               System.out.print(rs.getString(i) + " | ");
               stray[i-1]=rs.getString(i);
               }
             System.out.print("\n >>>>>>> " + stray[0] + " <<<<<<<<");
           }
        } 
      }