"Fan Ruo Xin" <fa*****@sbcglo bal.net> wrote in message
news:NO******** ********@newssv r17.news.prodig y.com...
"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:38******** *****@individua l.net... Rhino wrote: "Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:38******** *****@individua l.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.
>>Unfortunatel y, 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.SQLExc eption;
import COM.ibm.db2.app .UDF;
public class TextFuncs extends UDF {
public static String reverse (String input)
throws SQLException {
try {
StringBuffer reversedStringB uffer = new StringBuffer();
for (int ix=input.length (); ix>0; ix--) {
reversedStringB uffer.append(in put.substring(i x-1, ix));
}
/* Set the output string to be the reverse of the input string. */
return(reversed StringBuffer.to String());
}
catch (Exception excp) {
throw new SQLException("I nvalid Operation", "38702");
}
}
}
=============== =============== =============== ===========
Here is the UDFDriver program:
=============== =============== =============== ===========
import java.sql.Connec tion;
import java.sql.Driver Manager;
import java.sql.Result Set;
import java.sql.SQLExc eption;
import java.sql.Statem ent;
import javadb2.JDBC01;
public class UDFDriver {
private final String CLASS_NAME = getClass().getN ame();
private Connection conn01 = null;
public static void main(String[] args) {
new UDFDriver();
}
/**
* Constructor
*/
public UDFDriver() {
super();
System.out.prin tln("Welcome to " + CLASS_NAME + "!\n");
System.out.prin tln("\nLoad the JDBC driver....");
loadDriver();
System.out.prin tln("\nConnect to the database....");
connectToDataba se();
System.out.prin tln("\nExecute functions....") ;
executeFunction s();
}
/**
* Load the JDBC driver.
*/
private void loadDriver() {
String METHOD_NAME = "loadDriver ()";
String jdbcDriverName = "COM.ibm.db2.jd bc.app.DB2Drive r";
/* Load the JDBC driver. */
try {
Class.forName(j dbcDriverName);
} catch (ClassNotFoundE xception excp) {
System.err
.println(CLASS_ NAME
+ "."
+ METHOD_NAME
+ " - Encountered ClassNotFoundEx ception while
attempting to load JDBC driver "
+ jdbcDriverName + ". Error: " + excp);
excp.printStack Trace();
System.exit(16) ;
}
}
/**
* Get a connection to the database.
*
*/
private void connectToDataba se() {
String METHOD_NAME = "connectToDatab ase()";
/* 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.g etConnection(ur l, loginName, password);
} catch (SQLException sql_excp) {
System.err.prin tln(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException on connect to URL " + url
+ ". Error: "
+ sql_excp);
sql_excp.printS tackTrace();
System.exit(16) ;
}
/* Set autocommit off. */
try {
conn01.setAutoC ommit(false);
System.out.prin tln("Turn off autocommit...") ;
} catch (SQLException sql_excp) {
System.err.prin tln(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException on attempt to turn
autocommit off. Error: "
+ sql_excp);
sql_excp.printS tackTrace();
System.exit(16) ;
}
}
private void executeFunction s() {
String METHOD_NAME = "executeFunctio ns()";
String queryTableSQL =
"select lastname, rhino.reverse(l astname) " +
"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.createSt atement();
rs01 = queryTableStmt. executeQuery(qu eryTableSQL);
} catch (SQLException excp) {
System.err.prin tln(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while trying to get
information from "
+ "Employee table. Error: " + excp);
excp.printStack Trace();
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.prin tln(JDBC01.pad( "LASTNAME", ' ', 'T', 15) + spaces
+ JDBC01.pad("REV ERSED", ' ', 'T', 15));
System.out.prin tln(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.prin tln(JDBC01.pad( lastname, ' ', 'T', 15) +
spaces + JDBC01.pad(reve rsed, ' ', 'T', 15));
}
} catch (SQLException sql_excp) {
System.err.prin tln(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while reading Employee "
+ "table. Error: " + sql_excp);
sql_excp.printS tackTrace();
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.prin tln(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while closing Employee"
+ " result set, closing statement, or committing. Error:
" + sql_excp);
sql_excp.printS tackTrace();
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
