Re: Generic JDBC driver. -- JAVA400-L


Couple points:
1) If you writing the program to run directly on the 400, use the native
JDBC driver.  Its faster.

2) The native JDBC driver is in a class file called db2_classes.jar and
where it is moves around a little.  Before JDK 1.2 you didn't need to put
it in your classpath, it was found automatically.  With JDK 1.2, you should
add it to your extensions directory if you plan to use it and then never
worrry about your classpath again).  Full details are at
http://www.as400.ibm.com/developer/jdbc/Faqs/JDBCFAQ.html#B19.  They are
working on PTFs for V4R5 and V5R1 that will make the Native JDBC driver
always found again and these instructions (while they won't hurt anything)
will no longer be needed.

3) There was really no need to import the Toolbox access classes.  Use
Class.forName() to load the driver and register it.  Here I have removed
the DriverManager.registerDriver call and replaced it with two calls to
Class.forName.  This will register *both* IBM supplied JDBC drivers (you
only need one).  Then I get two Connection objects that could be used (we
only use c in the program, but we could use c2 as well).  These two objects
are different implementations of the same interface.  The way the
DriverManager knows which implementation to hand out is based on the URL.
Notice the minor change of "jdbc:as400" to "jdbc:db2" for the second
connection.  It would be wise to get these two values set based on an input
parameter, a properties file, or some other mechanism.  Doing this allows
you to run a program using each driver without doing any recompilation.

  try {

  // Register AS/400 JDBC Driver:
     Class.forName("com.ibm.db2.jdbc.app.DB2Driver");        // native
     Class.forName("com.ibm.as400.access.AS400JDBCDriver");  // toolbox

  // Connect to AS/400 (with toolbox)
  Connection c = DriverManager.getConnection(
    "jdbc:as400://myas400;naming=sql;errors=full",
    "myuser",
    "mypwd");

  // Connect to AS/400 (with native JDBC driver)
  Connection c2 = DriverManager.getConnection(
    "jdbc:db2://myas400;naming=sql;errors=full",
    "myuser",
    "mypwd");

  // Run a query to load a result set:

     // First create an SQL statement:
     Statement s = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);

     // Then execute a statement:
     ResultSet rs = s.executeQuery (
       "SELECT STDNAM FROM BCSSTUDENT.PAOSSTU WHERE STDCLS = '11' ORDER BY
STDNAM");

    // Then loop through ResultSet records to load combo box:
    // For now, just load first student so we don't have to wait.
    rs.first();
    jComboBox1.addItem(rs.getString("STDNAM"));

    // Close the statement and disconnect:
    s.close();
    c.close();

  }   // try

  catch (SQLException sqle) {
    sqle.printStackTrace();
  }   // catch



Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"Biologists have a special word for stability -- dead"

                Larry Wall
                Open Source Developers Journal
                Issue 1, Jan  2000


"Steve Swett" <swett@bcswebsite.com>@midrange.com on 03/09/2001 10:59:24 AM

Please respond to JAVA400-L@midrange.com

Sent by:  owner-java400-l@midrange.com


To:   <JAVA400-L@midrange.com>
cc:
Subject:  Re: Generic JDBC driver.



If you're using the AS/400 Toolbox for Java jar or zip file (jt400.zip for
example) when building your app, you can use a section of code like the
following to connect via a JDBC driver:


  try {

  // Register AS/400 JDBC Driver:
  DriverManager.registerDriver (new AS400JDBCDriver ());

  // Connect to AS/400:
  Connection c = DriverManager.getConnection(
    "jdbc:as400://myas400;naming=sql;errors=full",
    "myuser",
    "mypwd");

  // Run a query to load a result set:

     // First create an SQL statement:
     Statement s = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);

     // Then execute a statement:
     ResultSet rs = s.executeQuery (
       "SELECT STDNAM FROM BCSSTUDENT.PAOSSTU WHERE STDCLS = '11' ORDER BY
STDNAM");

    // Then loop through ResultSet records to load combo box:
    // For now, just load first student so we don't have to wait.
    rs.first();
    jComboBox1.addItem(rs.getString("STDNAM"));

    // Close the statement and disconnect:
    s.close();
    c.close();

  }   // try

  catch (SQLException sqle) {
    sqle.printStackTrace();
  }   // catch


In this case BCSSTUDENT is the name of an AS/400 library (also known as a
database or collection), and PAOSSTU is the name of the file (AKA table).

For less coding, I used an import statements like the following at the top
of the program:

import java.sql.*;
import com.ibm.as400.access.*;

You can also refer to the Toolkit programmer's guide for good examples and
explanations:

http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/java/rzahh/to

olbox.htm

Once there, look under the Access classes for the section on JDBC.

Hope that helps.




-----Original Message-----
From: Bartell, Aaron L. (TC) <ALBartell@taylorcorp.com>
To: 'JAVA400-L@midrange.com' <JAVA400-L@midrange.com>
Date: Friday, March 09, 2001 11:40 AM
Subject: Generic JDBC driver.


>Hi all,
>
>I have written my first java pgm's on the 400 that were rather simple, and
>now I want to get into more complex coding with file I/O and stuff.
>
>I can't seem to find an SQL driver so I can access my AS/400's DB.  I have
>tried AS400JDBCDriver.class but it does not exist.  I have looked in
>\\My400\QIBM\ProdData\Java400\com\ibm\as400\access in the IFS but cannot
>find anything that would resemble a file I/O driver.
>
>Where should I look next?  Can I just copy one from another 400 or does it
>need to be installed or something?  Help . . .
>
>Aaron Bartell
>+---
>| This is the JAVA/400 Mailing List!
>| To submit a new message, send your mail to JAVA400-L@midrange.com.
>| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
>| To unsubscribe from this list send email to
JAVA400-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner: joe@zappie.net
>+---
>

+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---



+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---