WebSphere Application Server - SQL exception not reaching Java layer while using executeUpdate

Author

SQL exception not reaching Java layer while using executeUpdate

2007-03-27, 7:28 pm

We have defined a data source in
IBM WebSphere Application Server, 6.0.2.13
Build Number: cf130631.22
Build Date: 8/4/06

Implementation class name
com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource

The Data Source is Version 4

I wrote a small test program which calls a SP in a MS SQL Server 2005.
The sole purpose of the stored procedure is to raise an exception.
Now if I use executeUpdate I don't receive the exception back at the Java level.
If I change the code to excuteQuery I do get the exception.

The Test code

package com.claimiq.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class TestDB {
public static void main(String[] args) {
System.out.println("JDBC Driver (new Websphere Driver)");
testSPinDB("jdbc/aigdb");
System.out.println("\n\nTest Complete\n");
} //end of main()

public static void testSPinDB(String jdbcJNDIname) {
Connection l_conn = null;
String SP = "{call textexception()}";
List l_variables = new ArrayList();

try {
l_conn = getConnectionViaWebsphereDataSource(jdbc
JNDIname);
if(l_conn != null) {
CallableStatement l_callableStatement = l_conn.prepareCall(SP);
Object value = null;
for(int i = 0 ; i < l_variables.size() ; i++){
value = l_variables.get(i);
if(!(value instanceof SqlNull))
l_callableStatement.setObject(i+1, l_variables.get(i));
else
l_callableStatement.setNull(i+1, ((SqlNull)value).getSqlType());
}

System.out.println("Executing statement.");
long l_startTime = System.currentTimeMillis();
l_callableStatement.executeUpdate();
//l_callableStatement.executeQuery();
System.out.println("Execution Time: " + (System.currentTimeMillis() - l_startTime) + " ms");
} else {
System.out.println("Databse connection could not be obtained");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(l_conn != null) { l_conn.close(); }
} catch (Exception e) { e.printStackTrace(); }
}
}

private static Connection getConnectionViaWebsphereDataSource(Stri
ng p_jndiName) throws SQLException {
Connection conn = null;
try {
Properties l_contextProps = new Properties();
l_contextProps.put(Context.INITIAL_CONTEXT_FACTORY,"com.ibm.websphere.naming.WsnInitialContextFactory");
l_contextProps.put(Context.PROVIDER_URL, "iiop://localhost:2809");

Context l_context = new InitialContext(l_contextProps);

if (l_context != null) {
DataSource l_dataSource = (DataSource)l_context.lookup(p_jndiName);
if(l_dataSource != null) {
conn = l_dataSource.getConnection();
} else {
System.out.println("Datasource not found");
}
}
} catch (NamingException e) {
e.printStackTrace();
}
return conn;
}
}

Test SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[textexception]
as
set nocount on
select getdate()
raiserror 22222 'Dummy error for testing'

2007-03-27, 7:28 pm

Sorry the SP attached is the wrong one.
I am going to update the SP.