"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:NO****************@newssvr17.news.prodigy.com ...
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:38*************@individual.net... Rhino wrote: "Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:38*************@individual.net...
>Rhino wrote:
>
>>I was reading the DB2 for Linux/Unix/Windows (V7.2) manuals just now
to
get
>>some background information about UDFs when I came across this:
>>
>>"Note that valuable debugging tools such as printf() do not normally
work as
>>debugging aids for your UDF, because the UDF normally runs in a
background
>>process where stdout has no meaning. As an alternative to using
printf(), it
>>may be possible for you to instrument your UDF with file output
logic,
and
>>for debugging purposes write indicative data and control information
to
a
>>file.
>>Another technique to debug your UDF is to write a driver program for
>>invoking the UDF outside the database environment. With this
technique,
you
>>can invoke the UDF with all kinds of marginal or erroneous input
arguments
>>to attempt to provoke it into misbehaving. In this environment, it is
not a
>>problem to use printf() or a source level debugger. "
>>
>>I could certainly do the File I/O approach described in the first
paragraph
>>but I'd really like to try the technique described in the second
paragraph.
>>Unfortunately, I'm not at all clear on how to develop a UDF driver of
the
>>kind described. Does anyone have a version of such a program, even
just
a
>>basic one that I could adapt? Could you possibly post it here or send it
to
>>me directly?
>>
>>Please note that I'm using DB2 V7.2 so I'm a little bit "back-level".
>>
>
>I guess all you need is a main() and then you invoke the UDF from C
(or>whatever).
>
I tried this but I can't get debugger (Eclipse 3.0.1) to see the breakpoints in the UDF so that I can step through the UDF. Is there some special
technique I need to use? My code is written in Java. I have no problem
getting my debugger to see breakpoints set in other classes but the
same techniques don't work for the UDF....
Rhino
Sorry, I'm (still :-0) Java illiterate. I hope someone else jumps in
here. =========
I am not Java guy. But can you say more clearly?
(1) The UDF code is written in C language, right?
(2) The testing program is written in Java lanaguage, right?
The UDF code and the testing program are both written in Java.
I'm using Eclipse 3.0.1 in Windows XP.
Here is the UDF:
================================================== ======
import java.sql.SQLException;
import COM.ibm.db2.app.UDF;
public class TextFuncs extends UDF {
public static String reverse (String input)
throws SQLException {
try {
StringBuffer reversedStringBuffer = new StringBuffer();
for (int ix=input.length(); ix>0; ix--) {
reversedStringBuffer.append(input.substring(ix-1, ix));
}
/* Set the output string to be the reverse of the input string. */
return(reversedStringBuffer.toString());
}
catch (Exception excp) {
throw new SQLException("Invalid Operation", "38702");
}
}
}
================================================== ======
Here is the UDFDriver program:
================================================== ======
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javadb2.JDBC01;
public class UDFDriver {
private final String CLASS_NAME = getClass().getName();
private Connection conn01 = null;
public static void main(String[] args) {
new UDFDriver();
}
/**
* Constructor
*/
public UDFDriver() {
super();
System.out.println("Welcome to " + CLASS_NAME + "!\n");
System.out.println("\nLoad the JDBC driver....");
loadDriver();
System.out.println("\nConnect to the database....");
connectToDatabase();
System.out.println("\nExecute functions....");
executeFunctions();
}
/**
* Load the JDBC driver.
*/
private void loadDriver() {
String METHOD_NAME = "loadDriver()";
String jdbcDriverName = "COM.ibm.db2.jdbc.app.DB2Driver";
/* Load the JDBC driver. */
try {
Class.forName(jdbcDriverName);
} catch (ClassNotFoundException excp) {
System.err
.println(CLASS_NAME
+ "."
+ METHOD_NAME
+ " - Encountered ClassNotFoundException while
attempting to load JDBC driver "
+ jdbcDriverName + ". Error: " + excp);
excp.printStackTrace();
System.exit(16);
}
}
/**
* Get a connection to the database.
*
*/
private void connectToDatabase() {
String METHOD_NAME = "connectToDatabase()";
/* Initialize the variables used to get the connection. */
String databaseName = "sample";
String url = "jdbc:db2:" + databaseName;
String loginName = "db2admin";
String password = "db2admin";
/* Connect to the database. */
try {
conn01 = DriverManager.getConnection(url, loginName, password);
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException on connect to URL " + url
+ ". Error: "
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
/* Set autocommit off. */
try {
conn01.setAutoCommit(false);
System.out.println("Turn off autocommit...");
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException on attempt to turn
autocommit off. Error: "
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}
private void executeFunctions() {
String METHOD_NAME = "executeFunctions()";
String queryTableSQL =
"select lastname, rhino.reverse(lastname) " +
"from rhino.employee " +
"where workdept = 'D21'";
/*
* Query the demonstration table to get information about certain
* employees.
*/
Statement queryTableStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = conn01.createStatement();
rs01 = queryTableStmt.executeQuery(queryTableSQL);
} catch (SQLException excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while trying to get
information from "
+ "Employee table. Error: " + excp);
excp.printStackTrace();
System.exit(16);
}
/*
* Print a title line above the result set. The static method pad()
is
* used to align the column titles and underlines.
*/
String spaces = " ";
System.out.println(JDBC01.pad("LASTNAME", ' ', 'T', 15) + spaces
+ JDBC01.pad("REVERSED", ' ', 'T', 15));
System.out.println(JDBC01.pad("--------", ' ', 'T', 15) + spaces
+ JDBC01.pad("--------", ' ', 'T', 15));
/* Initialize the host variables used for handling the result set.
*/
String lastname = null;
String reversed = null;
/*
* Print each line of the result set. The static method pad() is
again used
* to align the data values with the column titles.
*/
try {
while (rs01.next()) {
lastname = rs01.getString(1);
reversed = rs01.getString(2);
System.out.println(JDBC01.pad(lastname, ' ', 'T', 15) +
spaces + JDBC01.pad(reversed, ' ', 'T', 15));
}
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while reading Employee "
+ "table. Error: " + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
/* Close the result set, dispose of the statement, and commit. */
try {
rs01.close();
queryTableStmt.close();
conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while closing Employee"
+ " result set, closing statement, or committing. Error:
" + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}
}
================================================== ======
Please note that I'm not actually having any problems with my UDF; it is
very simple and works perfectly. I just want to figure out how to step
through the code in the Eclipse debugger in case I have problems with more
complex UDFs later on.
Rhino