Connect to MySql with Java on iSeries

[Sascha]

Dear community,

I'd like to push some data from iSeries to a MySql Database or backwards.
I have installed the JDBC driver by uploading it via ftp to the following directory:
\QIBM\UserData\Java400\ext.


When I try to compile a java-file via javac (QSH) it stucks at the line:

 Class.forName("com.mysql.jdbc.Driver");

and prompts ClassNotFoundException.

I use the following import statements, which seems to be ok:

import java.sql.Connection;
import java.sql.DriverManager;

I have read that i only need to upload the jar file to install the driver.

What can I do else?

Thank you very much.

Kind regards,

Sascha

[dhanuxp]

Re: Connect to MySql with Java on iSeries

have you set classpath ? ADDENVVAR ENVVAR(CLASSPATH) VALUE('/QIBM/UserData/Java400/ext/xx.jar')

BTW, are you RPG programer? If so, there's a great FOS tool/utility to achieve your goal. Here it is.

[Sascha]

Re: Connect to MySql with Java on iSeries

Hello dhanuxp,
thank you for your reply.
I read this would not be necessary when using the path i used.
Now I have add the ENVVAR to *JOB and also to *SYS. But when i try to compile a java programm it still says:

simplejava.java:14: unreported exception java.lang.ClassNotFoundException; must be caught or declared to be thrown  
          Class.forName("com.mysql.jdbc.Driver");

Any other idea?

[Sascha]

Re: Connect to MySql with Java on iSeries

When using the Driver version 5.1.25 it looks fine so far - no error when compiling

[Scott Klement]

Re: Connect to MySql with Java on iSeries

You should never put the /QIBM/UserData/Java400/ext directory into your classpath. This is a special directory that's for adding "extensions" to the Java runtime, and any JAR or CLASS file in that directory will be loaded into Java automatically for all Java programs running on your system.

Placing JDBC drivers in /QIBM/UserData/Java400/ext will work without modifying the classpath, but it is not a good idea, in my opinion. The reason is that you may want to have Java programs on the system that do not load your JDBC driver into memory. Or, you may want to have Java software that uses a different version of the driver (possibly different applications using a different version -- or possibly you'll want to test a new version in a test environment while still using the older version in production.) If you use the /QIBM/UserData/Java400/ext ("extensions") directory, you cannot do that.

So, a much better idea is to create a separate directory. Maybe call it /java/jdbc, for example, as a place to put JDBC drivers. Then put /java/jdbc/thefile.jar into your CLASSPATH, and do not use /QIBM/UserDAta/Java400/ext at all. This is a much smarter way to do it.

--

As for the error you're getting... I think you are misinterpreting the message! Especially, if you're getting this error at compile-time! It's telling you that your program code must either throw or catch a "NoClassDefFound" exception. It's not saying that you've received an exception -- ti's saying that your program has forgotten to code for the possibility that an error might occur. If a routine (such as Class.forName) can potentially throw an error, Java requires that you MUST provide some way to handle that error. Either you have to use a try/catch construct to handle the error in your code, or you must declare that your method can throw the error back to it's caller.

[Sascha]

Re: Connect to MySql with Java on iSeries

You should never put the /QIBM/UserData/Java400/ext directory into your classpath. This is a special directory that's for adding "extensions" to the Java runtime, and any JAR or CLASS file in that directory will be loaded into Java automatically for all Java programs running on your system.

Placing JDBC drivers in /QIBM/UserData/Java400/ext will work without modifying the classpath, but it is not a good idea, in my opinion. The reason is that you may want to have Java programs on the system that do not load your JDBC driver into memory. Or, you may want to have Java software that uses a different version of the driver (possibly different applications using a different version -- or possibly you'll want to test a new version in a test environment while still using the older version in production.) If you use the /QIBM/UserData/Java400/ext ("extensions") directory, you cannot do that.

So, a much better idea is to create a separate directory. Maybe call it /java/jdbc, for example, as a place to put JDBC drivers. Then put /java/jdbc/thefile.jar into your CLASSPATH, and do not use /QIBM/UserDAta/Java400/ext at all. This is a much smarter way to do it.

--

As for the error you're getting... I think you are misinterpreting the message! Especially, if you're getting this error at compile-time! It's telling you that your program code must either throw or catch a "NoClassDefFound" exception. It's not saying that you've received an exception -- ti's saying that your program has forgotten to code for the possibility that an error might occur. If a routine (such as Class.forName) can potentially throw an error, Java requires that you MUST provide some way to handle that error. Either you have to use a try/catch construct to handle the error in your code, or you must declare that your method can throw the error back to it's caller.

Hello Scott,
thank you for your hint using a seperate path. I will implement it the way you suggested. Good to know that the compiler just warns when i haven't implemented a try/catch construct.
I'm new to Java and also the iSeries World So am thankful for every hint i can get

[tomliotta]

Re: Connect to MySql with Java on iSeries

We had a customer problem that was really causing us trouble trying to track down. Classes simply refused to behave as they should for some reason and no pattern was apparent. After tracking back through everything we could think of, I found an older copy of the Java ToolKit in the /ext directory.

Some customer developer had thought it was a good place for it; and because it worked for what they were doing, they never gave another thought.

Tom

[Sascha]

Re: Connect to MySql with Java on iSeries

So I have written a JAVA programm for transfer. All works fine apart from the insert statement.
It throws a DB2DBException which says that the file specified is not valid for this operation.
OBJAUTs seem to be ok (*PUBLIC -> *CHANGE).
Does somebody know how to fix this?

Thank you in advance.

Sascha

-----

Information

The Main-Class (IT002J)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import com.ibm.db2.jdbc.app.*;
import java.sql.CallableStatement;

public class IT002J
{
	
	public void importData()
	{
         Product myProduct = null;
	 ResultSet rs = null;
	 CallableStatement cst = null;
	
	 java.sql.Connection connect = null;
	 java.sql.Connection connectDB2 = null;

	 //DB2
	 String db2url = "jdbc:db2:odbc";
	 String useriddb2 = "xxxxx";
	 String passworddb2 = "xxxxx";
	 
	 //MySQL
	 
	 String mysqlurl ="jdbc:mysql://xx.xxx.1.x/productlabel?";
	 String userid   = "root";
	 String password = "xxxxxx";
	 System.out.println(mysqlurl + "user=" + userid + "&password=" + password);
	 	 
	 try
	 {
	    connectDB2 = DriverManager.getConnection(db2url, useriddb2, passworddb2);
		Product.connection = connectDB2;
		Product.connection.setAutoCommit(false);
		connect = DriverManager.getConnection(mysqlurl + "user=" + userid + "&password=" + password);
           
		cst = connect.prepareCall("{call Get_ProductExportView}") ;     
		rs = cst.executeQuery();
		 while(rs.next())
		 {
			 //System.out.println(rs.getString(1));
			 myProduct = new Product();
			 int i = 1;
			 myProduct.P_ID = rs.getInt(i++);
			 
			 myProduct.setPG_Name(rs.getString(i++));
	
			 myProduct.setPtn_Name(rs.getString(i++));

			 myProduct.setWt_Name(rs.getString(i++));
	
			 myProduct.setWg_Name(rs.getString(i++));
			
			 myProduct.Charge = rs.getInt(i++);
			 myProduct.setDimension(rs.getString(i++));
			 myProduct.Standard = rs.getString(i++);
			 myProduct.Content = rs.getDouble(i++);
			 myProduct.Units = rs.getInt(i++);
			 myProduct.Origin = rs.getString(i++);
			 myProduct.FSC = rs.getBoolean(i++);
			 myProduct.CE_ProductNo = rs.getString(i++);
			 myProduct.CE_ProductionYear = rs.getInt(i++);
			 myProduct.CE_FireBehaviour = rs.getString(i++);
			 myProduct.CE_Density = rs.getString(i++);
			 myProduct.CE_CH2OEmission = rs.getString(i++);
			 myProduct.CE_ThermalConductivity = rs.getString(i++);
			 myProduct.CE_Rating = rs.getString(i++);
			 myProduct.CE_No = rs.getString(i++);
			 myProduct.CE_SlippingBehaviour = rs.getString(i++);
			 myProduct.SurfaceExFactory = rs.getBoolean(i++);
			 myProduct.LabelTC  = rs.getString(i++);
			 myProduct.Wt_Code = rs.getString(i++);
			 
			 myProduct.insert();
			 System.out.println(myProduct.Wt_Code);
		 }
		 
		 Product.connection.setAutoCommit(true);
		 
		
	 }
	 catch(SQLException e)
	 {
		System.err.println("Unable to connect");
		e.printStackTrace();
	 
	 }
	 
	 catch(Exception x)
	 {
		x.printStackTrace();
	 }
	 
	 finally
	 {
		try{connectDB2.close();} catch(Exception ex) {};
		try{connect.close();} catch(Exception ex) {};
		try{Product.connection.close();} catch(Exception ex) {};
	 }
	}
	
	public static void main(String[] args)
	{

	  IT002J myPGM = new IT002J();
	  myPGM.importData();
	 
	 
	 }
 
}

The Model-Class (Product)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import com.ibm.db2.jdbc.app.*;
import java.sql.CallableStatement;
	 
	 public class Product{
	    public static Connection connection;
		public String Pg_Name = "";
		public String Ptn_Name = "";
		public String Wt_Name = "";
		public String Wg_Name = "";
		public int    Charge = 0;
		public String BotanicName = "";
		public String Dimension = "";
		public String Standard = "";
		public double Content = 0;
		public int Units = 0;
		public String Origin = "";
		public boolean FSC = false;
		public String CE_ProductNo ="";
		public int    CE_ProductionYear = 13;
		public String CE_FireBehaviour  = "";
		public String CE_Density  = "";
		public String CE_CH2OEmission  = "";
		public String CE_ThermalConductivity  = "";
		public String CE_Rating  = "";
		public String CE_No  = "";
		public String CE_SlippingBehaviour  = "";
		public boolean SurfaceExFactory = false;
		public String LabelTC  = "";
		public String Wt_Code = "";
		public int    P_ID = 0;
		
		public void  setPG_Name(String pValue)
		{
		    int len = pValue.length() > 40 ? 40 : pValue.length();
			System.out.println(len);
			Pg_Name = pValue.substring(0,len);
			System.out.println(Pg_Name);
		}
		
		public void  setPtn_Name(String pValue)
		{
		    int len = pValue.length() > 40 ? 40 : pValue.length();
			this.Ptn_Name = pValue.substring(0,len);
		}
		
		public void  setWt_Name(String pValue)
		{
		    int len = pValue.length() > 40 ? 40 : pValue.length();
			this.Wt_Name = pValue.substring(0,len);
		}
		
		public void  setWg_Name(String pValue)
		{
		    int len = pValue.length() > 40 ? 40 : pValue.length();
			this.Wg_Name = pValue.substring(0,len);
		}
		
		public void  setDimension(String pValue)
		{
		    int len = pValue.length() > 25 ? 25 : pValue.length();
		   this.Dimension = pValue.substring(0,len);
		}
		
	
	 
	  public void insert()
	  {
		PreparedStatement inscmd = null;
		
		try
		{
	
		String cmd = "Insert Into BEMDTA.MATLBL00("+ 
					"PID, PG, PTN, WT, WG, CHARGE, DIM, STANDARD, " +
					"PKGCNT, UNITS,ORIGIN,FSC,CEPNO, CEPY, CEFB, " +
					"CED, CECH2O, CETC, CER, CENO, CESB, SEF, " +
					"TCLBL, WTCODE)" +			
					" VALUES(" +
		                        "?,?,?, ?,?,? ,?,?,?, ?,"+
				        "?,?,?, ?,?,? ,?,?,?, ?,"+
				         "?,?,?, ?);" ;
		inscmd = connection.prepareStatement(cmd);
		inscmd.setInt(1,P_ID);
                inscmd.setString(2, Pg_Name);
		inscmd.setString(3, Ptn_Name);
		inscmd.setString(4, Wt_Name);
		inscmd.setString(5, Wg_Name);
		inscmd.setInt(6, Charge);
		inscmd.setString(7, Dimension);
		inscmd.setString(8, Standard);
		inscmd.setDouble(9, Content);
		inscmd.setInt(10, Units);
		inscmd.setString(11, Origin);
		inscmd.setBoolean(12,FSC);
		inscmd.setString(13,CE_ProductNo);
		inscmd.setInt(14, CE_ProductionYear);
		inscmd.setString(15, CE_FireBehaviour);
		inscmd.setString(16, CE_Density);
		inscmd.setString(17, CE_CH2OEmission);
		inscmd.setString(18, CE_ThermalConductivity);
		inscmd.setString(19, CE_Rating);
		inscmd.setString(20, CE_No);
		inscmd.setString(21, CE_SlippingBehaviour);
		inscmd.setBoolean(22, SurfaceExFactory);
		inscmd.setString(23,LabelTC);
		inscmd.setString(24,Wt_Code);
		inscmd.executeUpdate();
		
	
		connection.commit();
		}
		catch (SQLException e)
		{
			System.err.println("Unable to insert");
			e.printStackTrace();
		}
				   
	  
	  }
		
	 
	 }

[Sascha]

Re: Connect to MySql with Java on iSeries

I have found out when using autocommitment all works fine. But why doesn't it work manually?