JDBC Connection sharing between java and cobol

[mbieren]

Hello,

I have the following java code :

Connection connection = null;
try {

    /* DB2 */
    Class.forName("com.ibm.db2.jcc.DB2Driver");
    connection = DriverManager.getConnection ("jdbc:db2://db2server:50000/mycooldb2

}
catch (ClassNotFoundException e) {
    e.printStackTrace();
}
catch (SQLException e) {
    e.printStackTrace();
}

RunUnit runUnit = new RunUnit();
XVsd138l vsd138L = (XVsd138l) runUnit.GetInstance(XVsd138l.class);

vsd138l.XVsd138l(sl_ext_uebergabe, sextuebergabe, sl_uebergabevsdl18l, suebergabevsdl18l);

XVsd138L is a Cobol Program as Bytecode with ILSMARTLINKAGE. It does some SELECTS from "mycooldb2" and returns the values. I would like to use the database connection that has been established with java. The Documentation speaks about using "DB2 BIND CONNECTION" inside COBOL. But I have no clue what to do in java in order to get this working.

Some Code examples would be great

Kind regards

Michael

[Chris Glazier]

Please see the documentation on sharing a JDBC connection between Java and COBOL here:

I believe that this is a new feature in Visual COBOL 2.2 so if you do not have that version you will have to download it from the supportline web site.

Thanks.

[mbieren]

Hello,

I know this documentation. It just says the following :

1. Pass the connection object to COBOL.
2. Use EXEC SQL BIND CONNECTION to establish the connection.
3. When done using the connection, use EXEC SQL UNBIND CONNECTION if Java still requires access to it, or use EXEC SQL DISCONNECT if Java no longer requires access.

I fully agree with that and I do understand it. But its far away from being sufficient.

In my Java Code I do open the database connection and now I should pass it to cobol.

?? How should I do this. .... ?? Based on the Term :

1. Pass the connection object to COBOL.

I could :

- Print It, put it in a envelopen and FedEx it.

- Store It in the drive Bay ...

- Send It via a pidgeon

Sorry for my bad english but what I want to say ist that the doc is not engough to be used for any implementation. Some examples would be helpful

Kind regards

Michael

[Chris Glazier · Accepted solution]

I threw together this little example that has a java main that connects to sqlserver using jdbc, executes a query and then passes the connection object to COBOL as a parameter in linkage.

COBOL then binds to the connection and executes a query itself using embedded sql and then unbinds from the object and returns to Java.

I hope it conveys the general idea of how this can be done...

Java program: 

import java.sql.*;

public class javamain {

	/**
	 * @param args
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws SQLException {
	     // Create a variable for the connection string.
	     
		  String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
	         "databaseName=Northwind;user=chris;password=mypassword";

	      // Declare the JDBC objects.
	      Connection con = null;
	      Statement stmt = null;
	      ResultSet rs = null;

	      try {
	         // Establish the connection.
	         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	         con = DriverManager.getConnection(connectionUrl);

	         // Create and execute an SQL statement that returns some data.
	         String SQL = "SELECT TOP 10 * FROM Customers";
	         stmt = con.createStatement();
	         rs = stmt.executeQuery(SQL);

	         // Iterate through the data in the result set and display it.
	         while (rs.next()) {
	            System.out.println(rs.getString(1) + " " + rs.getString(2));
	         }
	         cobolclass cc = new cobolclass();
	    	 cc.cobolclass(con);
	      }

	      // Handle any errors that may have occurred.
	      catch (Exception e) {
	         e.printStackTrace();
	      }
          
	      finally {
	    	  
	    	  if (rs != null) try { rs.close(); } catch(Exception e) {}
	         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	         if (con != null) try { con.close(); } catch(Exception e) {}
	      }
	   }
}

cobol program:

      $set sql(dbman=jdbc) ilsmartlinkage
       program-id. cobolclass as "cobolclass".

       data division.
       working-storage section.
       exec sql include sqlca end-exec.
       01 contact-name   string.
       01 company-name string.
       linkage section.
       01 myconnection object.
       procedure division using by value myconnection.
           exec sql whenever sqlerror
              perform 999-sql-error
           end-exec
           exec sql bind connection to :myconnection end-exec
           exec sql declare mycursor cursor for select ContactName, CompanyName from Customers end-exec
           exec sql open mycursor end-exec
           perform until exit
              exec sql fetch mycursor into :contact-name, :company-name end-exec
              if sqlcode = 100
                 display "no more"
                 exit perform
              else
                 display contact-name
                 display company-name
              end-if
           end-perform
           exec sql close mycursor end-exec
           exec sql unbind connection :myconnection end-exec
           goback.

       999-sql-error.
       
           display "error = " sqlcode
           display sqlerrmc.
       end program cobolclass.

[mbieren]

Btw. This example could be placed in the documentation for your product as well

[mbieren · Accepted solution]

And you have to name your connection explicitly with

exec sql bind connection myconnection to :myconnection end-exec

so that you can release it with :

exec sql unbind connection myconnection end-exec

Otherwise you will receive :

-0000019701 SQLERRMC : Connection ? not found

[Mike Wilkin]

Thanks for the suggestion. We're already working (with Chris) to improve the documentation that was mentioned earlier in this thread, and an example is something we'll be looking at.

Thanks