RE: How to set the library using JDBC in a servlet

Here's an example which may help get you started.  The code is not optimal,
(ie - you will probably not want to instantiate new String objects within
the resultset loop), but hopefully
is clear enough.

Also, in a servlet environment you will most likely want to use a database
connection pooling mechanism rather than creating and destroying connections
with each HTTP request.  If you are using WebSphere, it has a built-in
connection pool you can use (I have had mixed results with this), or you can
write your own, or you can use a 3rd party package if you like.  Connection
pooling will allow you to pre-create a few connections, request a connection
from the pool whenever you need one, use it, and return it to the pool with
out destroying the connection.  The connection pooling mechanism should be
responsible for managing the connections themselves.

You should set your date format to match the format you are using in your
table, such as ISO.  If your database and your application server (ie -
WebSphere) are on the same box, then this should be less important, but if
your application server is on a different machine than your database, it is
a good idea to set it explicitely (see the as4Properties settings below).
Trying to update a date or timestamp into a table with a mismatched date
format will give you fits.

Finally, to set a library, you have several options:

1.      For OS/400 V4R4 or later, you can opt to use the library list from the
user profile's job description (this is strict; adding a library to your
interactive job's lib list will Not affect a java program which you invoke
interactively, but rather the lib list from your job description will be
used).  Note that for lib list searching to work, you must specify "naming",
"system" (see below).  Prior to V4R4, QGPL will always be used.

2.      In your connection properties (see below), you can use "libraries",
"somelib" (specify a library name), to set the default library.  The name
"libraries" is misleading, since only a single library may be specified.

3.      Specify an explicit collection (library) name as part of your SQL
statement.  I prefer this method, but of course your application may be
better served using one of the other methods.  This will be something like
"select fieldname from library.table", as you will see below.  Again, if
"naming" is "system", you should use a "/" instead of a "."

Here's an example:

***

Connection as4Conn = null;
String select = null;

// Here are a few common properties which may be used in establishing your
//  database connection, but you may want to check out the other options
available,
//  especially the transaction isolation (ie - journaling).

Properties as4Properties = new Properties();
as4Properties.put ("user", "someuser");
as4Properties.put ("password", "somepass");
as4Properties.put( "date format", "iso");

// Here, "naming" may be either "sql" or "system", and it determines the
separator
//      character which will be used between the collection (library) name and
the
//      table name.  I prefer the more universal "sql", but it is a personal
preference.
//      However, if you want the library list from the job description to be 
used
to resolve
//      your table name, you must use "system".

as4Properties.put( "naming", "sql");

// This will work for option #2 above:
//      as4Properties.put( "naming", "system");
//      as4Properties.put( "libraries", "whateverlib");


// URL to database.  my400db must exist in the AS/400
//      relational database (WRKRDBDIRE).
// Issue the command WRKRDBDIRE, and add a new *LOCAL relational database
//      entry called whatever you like, such as my400db (You may want
//      the name to reflect the name of your AS/400 system).

// This is the url in the form jdbc:subprotocol:subname.  The subprotocol
//      is driver dependent, and the subname is usually the name of the
//      database, including a path if appropriate (ie - non-local), such as:
//              jdbc:db2://123.123.123.1/my400db or
//              jdbc:db2://as400name/my400db

String urlas4 = "jdbc:db2:my400db";

try {
        // Register a jdbc driver.
      // This is the native driver.  If this code is ported to
      //  a non-local host, then the toolkit driver should be used:
      //             Class is:  com.ibm.as400.access.AS400JDBCDriver",
      //  URL subcomponent is:  jdbc:as400 (instead of jdbc:db2)

      Class.forName("com.ibm.db2.jdbc.app.DB2Driver");

      // Connect to AS/400
        //  The DriverManager attempts to select an appropriate driver from the
        //      set of registered JDBC drivers.

      as4Conn = DriverManager.getConnection(urlas4, as4Properties);

      ResultSet rs;
        try {
                Statement stmt = as4Conn.createStatement();

                select = "select field1,field2,field3 from yourlib.yourfile" +
                           " where yourfield='XYZ'";

                rs = stmt.executeQuery( select );
                while( rs.next()) {
                        String strField1 = rs.getString(1).trim();
                        String strField2 = rs.getString(2).trim();
                        String strField3 = rs.getString(3).trim();

                        // Do whatever you like with the data
                }
        }
        catch (Exception e) {
                e.printStackTrace();
        }
}
catch (Exception e) {
        e.printStackTrace();
}
finally {
        try {
                as4Conn.close();
        } catch (Exception e) { }
}

***

Shawn

-----Original Message-----
From: owner-java400-l@midrange.com
[mailto:owner-java400-l@midrange.com]On Behalf Of by way of David Gibbs
<david@midrange.com>
Sent: Monday, March 26, 2001 5:17 PM
To: java400-l@midrange.com
Subject: How to set the library using JDBC in a servlet


We are attempting to write a servlet (our first) that accesses data in an
AS/400 table using JDBC.  How do we set the library where the table is
found?

Thanks in advance for your help.

Dwight Slessman

+---
| 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
+---