hi all,
Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.
I wanted to implemented a generic "Helper" class like this:
/**
* Helper
*
* Has the various common functions shared by the DB2 Stored
Procedures,
* such as for handling logging (printWriter), create / dropping
tables,
* performing SQL updates, inserts (executeUpdate) and selects
(executeQuery)
*
*/
package com.mycompany.db2;
import lotsofstuff;
public class Helper {
public static Connection getConnection(String databaseAlias,
String userid, String password) throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException
{
//driver for Db2 v7
//String driver = System.getProperty("driver",
"COM.ibm.db2.jdbc.app.DB2Driver");
//driver for Db2 v8
String driver = System.getProperty("driver",
"com.ibm.db2.jcc.DB2Driver");
System.out.println("jdbcDriver: " + driver);
String jreVersion = System.getProperty("java.version");
System.out.println("jreVersion: " + jreVersion);
System.out.println("Helper.getConnection: jdbcDriver=" +
driver + ", databaseAlias=" + databaseAlias + "userid=" + userid + ",
password=" + password);
Class.forName(driver).newInstance();
String url = "jdbc:db2:" + databaseAlias;
Connection connection = DriverManager.getConnection(url,
userid, password);
connection.setTransactionIsolation(Connection.TRAN SACTION_READ_COMMITTED);
connection.setAutoCommit(true);
return connection;
}
public static long getElapsedTime(long previousTime) {
long currentTime = System.currentTimeMillis();
long lDifference = (currentTime - previousTime);
return lDifference;
}
public static String getTimeStamp() {
Calendar cal = new
GregorianCalendar(TimeZone.getTimeZone("ECT"));
SimpleDateFormat formater = new SimpleDateFormat();
String timeStamp = formater.format(cal.getTime()); //
02.03.03 23:41
return timeStamp;
}
etc. etc.
--- (end of Helper code) ---
So I first create 2 or 3 different classes, where the "Helper" class
has the "util" methods I want to re-use (like "getElapsedTime()"
etc.). Then I compile and "jar" all the classes together and then
call on one of the above Helper methods from another class in the same
package.
However, good old DB2 so far has only been giving me errors when I try
to do this, like the following:
D:\DB2 - Stored Procedures\build>java -cp
".;D:\SQLLIB\java\db2java.zip;D:\SQLLI
B\java\runtime.zip;D:\SQLLIB\java\sqlj.zip;D:\SQLL IB\java\db2jcc.jar;D:\SQLLIB\j
ava\db2jcc_license_cu.jar;CleanCompany.jar" -Ddatabase=xxx
com.calcucare.db
2sp.Driver
jdbcDriver: com.ibm.db2.jcc.DB2Driver
jreVersion: 1.4.2_05
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -1131,
SQLSTATE: 38503,
SQLERRMC: -2029060040;*;*;*;*;;SEMAPHORE WAIT;
at com.ibm.db2.jcc.c.yc.d(yc.java:1257)
at com.ibm.db2.jcc.a.db.l(db.java:365)
at com.ibm.db2.jcc.a.db.e(db.java:101)
at com.ibm.db2.jcc.a.r.e(r.java:108)
at com.ibm.db2.jcc.a.sb.i(sb.java:191)
at com.ibm.db2.jcc.c.yc.o(yc.java:1225)
at com.ibm.db2.jcc.c.zc.d(zc.java:2315)
at com.ibm.db2.jcc.c.ad.pb(ad.java:139)
at com.ibm.db2.jcc.c.ad.execute(ad.java:122)
at com.calcucare.db2sp.CleanCompany_Driver.main(Clean Company_Driver.java
:22)
this error (from 22 in the class "CleanCompany_Driver") is raised in
the following line of code ("callableStatement.execute()"):
CallableStatement callableStatement =
connection.prepareCall(statement);
callableStatement.execute();
can any of you explain this? is it related to some kind of limitation
in db2 that prevents me from implementing my jdbc stored procedures
using java packages?
I did find the following comment in "Db2 java stored procedures -
learning by example" (pdf), section 6.5.1:
----
One common technique when writing stored procedures in any language
has
the developer start by writing the code as a standalone program. Write
the
stored procedure as ONE CLASS and the driver as a separate class. When
you
complete the draft version you customize the stored procedure class to
fit
within the database. One item that must change will be the Connection,
which
would have been established by the driver in the standalone program.
----
=> this indicates to me ibm is not recommending the "packaged java
class" approach.
Obviously, I could research the documentation in DB2 more for the
answers to this question, but so far haven't had any luck with this.
If any of you have already dealt with similar problems I would
appreciate your thoughts.
Kent
