asked on

Connecting to a db2 as400 database

Hi there Experts, I need to know step by step how to connect with db2 database
(DB2 JDBC Drivers) I've read a little of "COM.ibm.db2.jdbc.app.DB2Driver"
but I 'm really really new with Iseries stuffs I have always worked with SQL server so I never had problem with connecting with driver="sun.jdbc.odbc.JdbcOdbcDriver"; but now this is all new for me
I also need to know how if the connection string is right
String strConn = "Provider=IBMDA400.DataSource.1;Persist Security Info=true;" + "User ID=" + strLoginId + ";" +"Password=" + strPassword + ";" +"Data Source=" + strServidor + ";" ;

edwbear1976a

ASKER

How can I get the DB2 driver?

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edwbear1976a

ASKER

Hi, thank your for your comments, I already downloaded before the IBM JTOPEN version
IMB TOOL BOX , do you think it's a good choice?, I'm going to download the DirectData evaluation version (where leads me the link you posted first) but I don't know wich one use

I'm developing a servlet that needs access to a as400 database, I'm running on a WinX OS, and as I said before as400 is a new world to me, so I just want to access, execute my queries and continue with my "Windows life"

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edwbear1976a

ASKER

Hi, I'm trying with JTOPEN,so I set up the Class path with the jars included, but I don't know if I'm missing something , I'm trying as drivers the following
String driver="com.ibm.db2.jdbc.app.DB2Driver"; //or
String driver="com.ibm.as400.access.AS400JDBCDriver";

I don't get errors when I use Class.forName(driver);//method but I think that my url could be wrong

as Url I'm adding "jdbc:as400://server-name/library-name;errors=full"; but I think that I'm missing something cause many pages I have read set this kind of sintax "jdbc:as400://server-name/default-schema;properties"
I don't know wheather " default -schema" or "properties" might include

When I do a query using the ResultSet object I get a NullPointerException, I can deal with it , using a catch but the fact is that I don't get anything when I execute my query

Here I post the code (its a jsp just for testing purposes), please any help will be really welcome I'm increasing the Points values to 300

-------------------------------------------------------------------------------------------------------------
<%@ page import= "java.io.*" import= "java.util.*" import="java.sql.*" isErrorPage="true" %>

<%!

Connection con;
String userName;
String styleClass ;
String paramFunctionName;
private ResultSet getResultSet(String strSQL,Connection c) throws
SQLException,ClassNotFoundException
     {
      Statement locSt=null;
      ResultSet locRst=null;

     locSt=c.createStatement();
     locRst=locSt.executeQuery(strSQL);
     return locRst;
}

private Connection getConnection()
{
      String strServidor = "SULAMERI";
      String strLibrary ="SULDATA";
      String strConn = "//"+strServidor+"/"+strLibrary + ";errors=full" ;

       Connection conn=null;
String driver="com.ibm.db2.jdbc.app.DB2Driver";
//String driver="com.ibm.as400.access.AS400JDBCDriver";

       String protocol="jdbc:odbc:";
String url=protocol+ strConn ;
       System.out.println(url);
            try {
            Class.forName(driver);

            conn=DriverManager.getConnection(url,"","");
            System.out.println(url);
            }
            catch (SQLException e){System.out.println(e.getMessage());}
            catch (ClassNotFoundException e){System.out.println(e.getMessage());}

      return conn;
}

%>

<html>

<head>
%>

</head>
<body>
<span>Hi Buds</span>
<%con=getConnection();
ResultSet rst=null;

try
{

      rst=getResultSet("Select * from SULDATA.OFPOLIP",con);

      while(rst.next())
      {
                  System.out.println(rst.getString("E7R8NB"));

      }

}

catch (SQLException e) {System.out.println(e.getMessage());}
catch (ClassNotFoundException e) {System.out.println(e.getMessage());}
catch (java.lang.NullPointerException e){System.out.println(e.getMessage());}
%>

<tbody>
</body>
</html>

-------------------------------------
and This is what I get ( null refers to the null pointer)

jdbc:odbc://SULAMERI/SULDATA;errors=full
com.ibm.db2.jdbc.app.DB2Driver
null

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edwbear1976a

ASKER

Hi,
it's just what I said and nothing happens, I get a nullpointerException
I was wondering if somerthing of this sintax is not right for this case
maybe I should use other methods instead Class.forName() to register the Driver or something
or my URL is too short and I need to add my userd ID and pwd

String driver="com.ibm.as400.access.AS400JDBCDriver";
String strConn = "//"+strServer+"/"+strDatabase + "prompt=false;errors=full" ;
String protocol="jdbc:as400:";
String url=protocol+ strConn ;
try {
            Class.forName(driver);

            conn=DriverManager.getConnection(url,"","");
            System.out.println(url);
            }
            catch (SQLException e){System.out.println(e.getMessage());}
            catch (ClassNotFoundException e){System.out.println(e.getMessage());}

edwbear1976a

ASKER

Hi, and thank your for the advices and links that I've been reading
, but I still get the nullpointerException when I execute the Query using the ResulSet object, but its kind of odd cause if it was bad since the Connection I would had gotten an Exception at connecting, well anyHow I was thinking that my URL was a little short and I added (uid and pwd) but I still get the nullpointerexception
/*
com.ibm.as400.access.AS400JDBCDriver
null
*/
private Connection getConnection()
{
      String strConn = "//"+strServidor+"/"+strLibrary + ";Uid="+ strLoginId +";Pwd="+strPassword+";prompt=false;errors=full" ;
       Connection conn=null;
String driver="com.ibm.as400.access.AS400JDBCDriver";
       String protocol="jdbc:as400:";
String url=protocol+ strConn ;
             try {
            Class.forName(driver);

            conn=DriverManager.getConnection(url,"","");
            System.out.println(url);
            }
            catch (SQLException e){System.out.println(e.getMessage());}
            catch (ClassNotFoundException e){System.out.println(e.getMessage());}

      return conn;
}

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edwbear1976a

ASKER

Hi, thank you for your note
well about the null-pointer exception (as you can see I had to catch that exception cause the asp was falling down) :
---------------------------------------------------------------------------
<%!
private ResultSet getResultSet(String strSQL,Connection c) throws
SQLException,ClassNotFoundException
     {
      Statement locSt=null;
      ResultSet locRst=null;

     locSt=c.createStatement();
     locRst=locSt.executeQuery(strSQL);
     return locRst;
}

%>

//once I get "connected" :

<%con=getConnection();
ResultSet rst=null;

try
{

rst=getResultSet("Select * from SULDATA.OFPOLIP",con);

while(rst.next())
{
System.out.println(rst.getString("E7R8NB"));

}

}

catch (SQLException e) {System.out.println(e.getMessage());}
catch (ClassNotFoundException e) {System.out.println(e.getMessage());}
catch (java.lang.NullPointerException e){System.out.println(e.getMessage());}
%>
--------------------------------------------------------------------------------------------------------

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edwbear1976a

ASKER

Hi , and thanks for your advices, well I just found another way to connect and at least I got a error that tells me something this is the way I found

try {
            DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
       conn = DriverManager.getConnection("jdbc:as400://" + system, username, password);
             DatabaseMetaData dbMetaData = conn.getMetaData();
             conn.close();


            }
            catch (SQLException e){System.out.println(e.getMessage());e.printStackTrace () ;}
            catch (ClassNotFoundException e){System.out.println(e.getMessage());e.printStackTrace () ;}

      return conn;

--------------------------------------------------------------------------------

And I get this error
****************************************************************************
C:\jakarta-tomcat-5.0.28\work\Catalina\localhost\sisTextEditor\org\apache\jsp\txtEditorLauncher_jsp.java:56: package com.ibm.as400.access does not exist
            DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
^
*************************************************************************
I got the JTOPEN jar files in my classpath
%classpath%;C:\WINNT\java\Packages;C:\windows\system32;C:\jakarta-tomcat-5.0.28\common\lib\servlet-api;C:\jakarta-tomcat-5.0.28\common\lib\jsp-api;c:\sybase\ASEP\3pclass.zip;c:\sybase\ASEP\monclass.zip;C:\IBMJTOPEN\lib\jt400.jar;C:\IBMJTOPEN\lib\util400.jar;C:\IBMJTOPEN\lib\jt400Servlet.jar;

and When I use the NetBeans IDE I added the JTOPEN FILES to my "libraries" so I got the fullpath completed
jt400.jar:
com
ibm
as400
access
as400JDBCDRIVER.class

I know that there must be something really silly that I'm missing

SOLUTION

Mayank S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Giant2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial