Problem solve Get help with specific problems with your technologies, process and projects.

Sample code: Accessing MS SQL Server database from the iSeries

Nowadays with the help of Java the iSeries can be integrated with other databases quite easy. This tip shows you how.

Nowadays with the help of Java the iSeries can be integrated with other databases quite easy. This tip shows you...

how. The code included here uses the free Microsoft driver that can be downloaded from here. (SQL Server 2000 Driver for JDBC Service Pack 3)

If your SQL server does not include the Northwind Sample Database you can find it here.

The download contains the following files:

msbase.jar
mssqlserver.jar
msutil.jar

Those files needs to be copied to the iSeries directories (/home/r_eitan/ExternalJARs).

Here's the directory structure (on the iSeries) for this sample:

/home/r_eitan/ExternalJARs - Microsoft files
(msbase.jar,mssqlserver.jar,msutil.jar)
/home/r_eitan/JdbcTest02 - My code (Main.java,Main.class)

The Java code
-----------------------------------------------------------

 
import java.sql.*; 

import java.io.*;

class Main {
 /**
  * Connect to Microsoft SQL server and download file
northWind.products as tab
  * seperated file. (products.txt)
  */
 public static void main(String args[]) {

  try {

   PrintStream outPut = new PrintStream(new
BufferedOutputStream(
     new
FileOutputStream("products.txt")));

 
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

   Connection connection =
DriverManager.getConnection(
 
"jdbc:microsoft:sqlserver://
 
  :1433", "
  
   ");

   System.out.println("Connection Done");

   connection.setCatalog("northWind");

   String sqlCmdString = "select * from products";

   Statement statement =
connection.createStatement();
   ResultSet resultSet =
statement.executeQuery(sqlCmdString);
   ResultSetMetaData resultSetMetaData =
resultSet.getMetaData();

   int columnCount =
resultSetMetaData.getColumnCount();

   // Iterate throught the rows in resultSet and
   // output the columns for each row.

   while (resultSet.next()) {
    
    for (int index = 1; index <=
columnCount; ++index) {

     String value;
     switch
(resultSetMetaData.getColumnType(index)) {
      case 2 :
      case 3 :
       value =
resultSet.getString(index);
       break;
      default :
       value = """ +
resultSet.getString(index) + """;
       break;
     }
     outPut.print(value + (index <
columnCount ? "t" : ""));
    }
    outPut.println();
   }

   outPut.close();
   resultSet.close();
   connection.close();

   System.out.println("Done");

  } catch (SQLException exception) {
   exception.printStackTrace();
  } catch (Exception exception) {
   exception.printStackTrace();
  }
 }
}
------------------------------------------------------------------------
-------
A CLP to run the show.

             PGM

             DCL        VAR(&CLASSPATH) TYPE(*CHAR) LEN(256)

/*  Set Java class path      */

             CD         DIR('/home/r_eitan/JdbcTest02')

             CHGVAR     VAR(&CLASSPATH) +
                          VALUE('.:../ExternalJARs/msbase.jar:../Exte+
                          rnalJARs/mssqlserver.jar:../ExternalJARs/ms+
                          util.jar')

             RUNJVA     CLASS('Main') CLASSPATH(&CLASSPATH) +
                          CHKPATH(*IGNORE) OPTIMIZE(20) OUTPUT(*PRINT)

             ENDPGM
-----------------------------------------------------------
  
 

Some ideas for using JDBC:

  • Getting data to the iSeries.
    A manufacturing company use a large automatic filling system to fill tanks. this system stored the filling transaction in JDBC compatible server.

    A Java program activated by the iSeries scheduler can periodical get the data to the iSeries.

  • Getting data from the iSeries. A large number of Off-the-Shelf Application use non iSeries database.

    A trigger program attached to some master file can submit a Java program to update those databases.

    Final note: The code was tested on V5R2 IDE – Eclipse-Java compiler 1.5 with compliance to release 1.3.

    ==================================
    MORE INFORMATION ON THIS TOPIC
    ==================================

    The Best Web Links: tips, tutorials and more.

    Visit the ITKnowledge Exchange and get answers to your developing questions fast.

    Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.


  • Dig Deeper on iSeries Java programming

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchDataCenter

    Close