Java Agent performing SQL query on AS400

HCL Domino Domino Forum
1

I have written the following agent. At the end of the agent run (which is successful), I am seeing, in the console, “Error cleaning up agent threads”. What can I do to ensure that any open threads are properly closed in this agent?import lotus.domino.*;

import java.sql.*;

import java.io.*;

public class JavaAgent extends AgentBase {

 String server		= "BSYS";

 String lib			= "ISPRDDTA";

 String file1			= "STORTBL00";

 String file2			= "DISTTBL00";

 String storenum	= "";

 

public void NotesMain() {



	String tmpDist = "";

	String tmpDistManager = "";

	String qry = "";

	String storenum = "1";

	

	try {

		System.out.println("Starting....");

		Session session = getSession();

		

		DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());

		Connection conn = DriverManager.getConnection("jdbc:as400://" + server, "ODBCUSER", "odbcsmrt");

           Statement stmt = conn.createStatement(); //error coming up here

           qry = "Select  DISTRICT FROM " + lib + "." + file1 +  " WHERE location =  '" + storenum + "'";

           ResultSet rs1 = stmt.executeQuery(qry);

// ResultSet rs1 = stmt.executeQuery(“Select DISTRICT FROM ISPRDDTA.STORTBL00 WHERE location = ‘1’”);

           System.out.println("ResultSet1 created");

		while (rs1.next()) {

			System.out.println("populate the variable");

 	          tmpDist = rs1.getString(1).trim();

               System.out.println("String object tmpDist created");

		}               

		System.out.println("close the result set");

		rs1.close(); 

		System.out.println("First result returned = " + tmpDist);



           ResultSet rs2 = stmt.executeQuery("Select  dstmgr FROM " + lib + "." + file2 +  " WHERE district = '" + tmpDist + "'");

// ResultSet rs2 = stmt.executeQuery(“Select dstmgr FROM ISPRDDTA.DISTTBL00 WHERE district = ‘1’”);

           System.out.println("ResultSet2 created");

		while (rs2.next()) {

			System.out.println("populate the variable");

 	          tmpDistManager = rs2.getString(1).trim();

               System.out.println("String object tmpDistManager created");

		}               

		System.out.println("close the result set");

		rs2.close(); 

		System.out.println("Second result returned = " + tmpDistManager);

		

		rs1 = null;

		rs2 = null;

		stmt = null;

		conn = null;

		

		session.recycle();

		

	}catch(Exception e){

			//e.printStackTrace();

			System.err.println("ERROR: " + e.getMessage());

			System.gc();

	}

}

}

2

Subject: Java Agent performing SQL query on AS400

Joel -

The error re: cursor state is because of the way you have the two loops constructed. If you’re only interested in the first row, use an if/then instead of a loop (which causes the cursor to be left in an invalid state when you prematurely close the resultset without breaking the loop).

The memory leak is probably because you’re leaving “heavy” objects like the connection and the statement open. Here’s a revised version of your code:

import lotus.domino.*;

import java.sql.*;

public class JavaAgent extends AgentBase {

private static final String SERVER = “server”;

private static final String USER = “ODBCUSER”;

private static final String WORD = “odbcsmrt”;

public void NotesMain() {

Session session = null;

Connection conn = null;

Statement stmt = null;

ResultSet rez = null;

try {

System.out.println(“Starting . . .”);

session = super.getSession();

Class.forName(“com.ibm.as400.access.AS400JDBCDriver”);

conn = DriverManager.getConnection(“jdbc:as400://” + JavaAgent.SERVER, JavaAgent.USER, JavaAgent.WORD);

conn.setReadOnly(true); // this is good for performance and a good idea if you’re only reading

stmt = conn.createStatement();

rez = stmt.executeQuery(“select district from isprddta.stortbl00 where location = ‘1’ for read only”);

System.out.println(“ResultSet #1 created”);

if (rez.next()) {

// trimming a resultset could throw a NullPointerException - do it on a variable instead

String tmpDist = rez.getString(1);

if (tmpDist == null) {

System.out.println(“No district found . . .”);

} else {

System.out.println("First result returned = " + tmpDist);

}

}

rez.close();

rez = stmt.executeQuery(“select dstmgr from isprddta.disttbl00 where district = ‘1’ for read only”);

System.out.println(“ResultSet #2 created”);

if (rez.next()) {

String distManager = rez.getString(1);

if (distManager == null) {

System.out.println(“No district manager found . . .”);

} else {

System.out.println("Second result returned = " + distManager);

}

}

rez.close();

} catch (Throwable th) {

th.printStackTrace();

} finally {

try {

if (rez != null) {

System.out.println(“Resultset closed . . .”);

rez.close();

rez = null;

}

if (stmt != null) {

System.out.println(“Statement closed . . .”);

stmt.close();

stmt = null;

}

if (conn != null) {

System.out.println(“Connection closed . . .”);

conn.close();

conn = null;

}

if (session != null) {

System.out.println(“Session recycled . . .”);

session.recycle();

session = null;

}

} catch (Throwable th) {

th.printStackTrace();

}

}

System.runFinalization();

System.gc();

}

}