Stored Procedure using Java

dsusendran · #1 (**permalink**) 06-25-07, 10:37

Environment: DB2 Connect Edition v8.1 fixpak 10 on Solaris 8

Hi all,

I am trying to get a stored procedure working, info from db2diag.log to pin point the error is not useful..

The Java Code:

import java.sql.*;

public class SPUTIL
{

public static void SPTRUNCATE(String databaseName, String tableName)
throws SQLException
{
int errorCode = 0;
String errorLabel = "";
boolean bFlag;
String sql;
PreparedStatement stmt = null;

try
{
Connection con = DriverManager.getConnection("jdbc:default:connecti on");

sql = "IMPORT FROM /dev/null OF DEL REPLACE INTO " + "'" + databaseName + "'.'"
+ tableName + "'";

stmt = con.prepareStatement( sql );

bFlag = stmt.execute();
}
catch ( SQLException sqle)
{
errorCode = sqle.getErrorCode();
throw new SQLException ( errorCode + " : " + errorLabel + " FAILED" );
}
}
}

I Compiled the java code to class file and put it in <instance>/sqllib/function folder..

I ran the following script

CREATE PROCEDURE DPS.SPTRUNCATE
(IN DBNAME VARCHAR(8) , IN TBLNAME VARCHAR(8))
SPECIFIC DPS.SPTRUNCATE
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'SPUTIL.SPTRUNCATE'
FENCED
THREADSAFE

The script ran successfully, but when i call the procedure

CALL SPTRUNCATE('TSTDB','TAB1')
SQL4302N Procedure or user-defined function "DPS.SPTRUNCATE", specific name "SPTRUNCATE" aborted with an exception "-104 : FAILED". SQLSTATE=38501

SQL4302N Procedure or user-defined function "DPS.SPTRUNCATE", specific name "SPTRUNCATE" aborted with an exception "-104 : FAILED ".

Explanation:

The procedure or user-defined function aborted with an exception.
The administration notification log contains a stack traceback
for the aborted routine.

User Response:

Debug the routine to eliminate the exception.

sqlcode : -4302

sqlstate : 38501

Any pointers?

Thanks in Advance,
Newbie

n_i · #2 (**permalink**) 06-25-07, 12:21

IMPORT is not an SQL statement, so you can't prepare or execute it. It's a CLP command.

dsusendran · #3 (**permalink**) 06-25-07, 13:46

Thanks n_j

could you tell me how to make a CLP connection instead of a JDBC connection in Java?

Connection con = DriverManager.getConnection("jdbc:default:connecti on");

Thanks!
Newbie

n_i · #4 (**permalink**) 06-25-07, 17:07

You can't do that, of course.

What you could do is to try invoking CLP via Runtime.getRuntime().exec() - I'm not sure if this will work thought. Even if it does, it might have certain performance and security implications.

stolze · #5 (**permalink**) 06-25-07, 20:13

Have a look at this article: http://tinyurl.com/9gnlo

It describes how to use a C stored procedure to invoke the respective API.

dsusendran · #6 (**permalink**) 06-26-07, 14:59

Thanks n_j! the following code works but, i dont want to create a temporary file to get this working.... any ideas?

Quote:

import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;

public class SPUTIL
{
public static void main(String argv[]) throws Exception
{
try
{
Class.forName("com.ibm.db2.jcc.DB2Driver");

Properties props = new Properties(); // Create Properties object
props.put("user", "db2inst"); // Set user ID for connection
props.put("password", "passwd"); // Set password for connection
props.put("currentSchema","schema");
String url = "jdbc:db2://23.23.23.23:55555/TSTDB"; // Set URL for data source
java.sql.Connection conn = DriverManager.getConnection(url, props); // Create connection

tbTrunc(conn);

conn.close();
}
catch(SQLException sqle) {
while(sqle != null) { // Check whether there are more
System.out.println("Message: " + sqle.getMessage());
System.out.println("SQLSTATE: " + sqle.getSQLState());
System.out.println("SQL error code: " + sqle.getErrorCode());
sqle=sqle.getNextException(); // Retrieve next SQLException
}
}
catch (ClassNotFoundException cnfe)
{
cnfe.printStackTrace();
}
}

//
static void tbTrunc(Connection conn) throws Exception
{

try
{
// store the CLP commands in a file and execute the file
File outputFile = new File("storImport.db2");
FileWriter out = new FileWriter(outputFile);

String cmd = "IMPORT FROM /dev/null OF DEL REPLACE INTO '" + tableName + "'";

out.write("CONNECT TO TSTDB user blah using passwd;\n");
out.write(cmd + ";\n");
out.write("CONNECT RESET;\n");

out.close();

Process p = Runtime.getRuntime().exec("db2 -vtf storImport.db2");

// open streams for the process's input and error
BufferedReader stdInput = new BufferedReader(new InputStreamReader(p.getInputStream()));
BufferedReader stdError = new BufferedReader(new InputStreamReader(p.getErrorStream()));
String s;

// read the output from the command and set the output variable with
// the value
while ((s = stdInput.readLine()) != null)
{
System.out.println(s);
}

// read any errors from the attempted command and set the error
// variable with the value
while ((s = stdError.readLine()) != null)
{
System.out.println(s);
}

// destroy the process created
p.destroy();

// delete the temporary file created
outputFile.deleteOnExit();
}
catch (IOException e)
{
e.printStackTrace();
System.exit(-1);
}
}
}

Thanks stolze!

Quote:

Have a look at this article: http://tinyurl.com/9gnlo

It describes how to use a C stored procedure to invoke the respective API.

That piece of information was a goldmine! however i think i dont have a C compiler installed on my Solaris box and i could not do the "bldrtn" part... any other suggestions? Do i have to download gcc and install it or is there a C compiler that exists on my machine that i dont know of?

Just curious... What kind of connection does this C code use?

Thanks!
Newbie

stolze · #7 (**permalink**) 06-29-07, 03:12

Don't use a temp file for the import operation. On Unix, you have "/dev/null" to read from and on Windows it is the file named "NUL".

I don't know about your compiler options on your Solaris box. You may want to ask this question your system administrator.

As for the connection: the stored procedure is executed by a DB2 process itself and, thus, it has the an implicit connection context. Therefore, the procedure doesn't have to worry about connections at all.

Internet Services

Web Development

Digital Marketing

Consumer Tech