SimpleJdbcInsert.executeAndReturnKey() SQLException under Websphere and SQLServer

[easternwahoo]

Any help with the following problem would be greatly appreciated...We are using Spring 2.5.6 with Websphere 6.1 and MS SqlServer 2005. We have the following DAO code:

public void init() {			
		insert = new SimpleJdbcInsert(getDataSource())
				.withTableName(TABLENAME)
				.usingGeneratedKeyColumns(ID);
	}

	public long insert(...) {
		Number generatedKey = insert.executeAndReturnKey(
				new BeanPropertySqlParameterSource(myobj));
		return generatedKey.longValue();
	}

This works fine when executed within a junit. But when insert() executes in websphere, we get the following exception:

PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [HY000]; error code [0]; [IBM][SQLServer JDBC Driver]Unsupported method: Connection.prepareStatement; nested exception is java.sql.SQLException: [IBM][SQLServer JDBC Driver]Unsupported method: Connection.prepareStatement
SOM
org.springframework.jdbc.UncategorizedSQLException : PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [HY000]; error code [0]; [IBM][SQLServer JDBC Driver]Unsupported method: Connection.prepareStatement; nested exception is java.sql.SQLException: [IBM][SQLServer JDBC Driver]Unsupported method: Connection.prepareStatement
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:607)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:824)
at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.executeInsertAndReturnKeyHolderInternal(Abst ractJdbcInsert.java:421)
at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.executeInsertAndReturnKeyInternal(AbstractJd bcInsert.java:402)
at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.doExecuteAndReturnKey(AbstractJdbcInsert.jav a:369)
at org.springframework.jdbc.core.simple.SimpleJdbcIns ert.executeAndReturnKey(SimpleJdbcInsert.java:110)

[DaveyS]

Looks like your driver doesn't support Connection.prepareStatement.

Have you tried using the Microsoft SQL Server drivers instead of the IBM ones?

[easternwahoo]

... but I don't want to have to supply the sql for the insert - I want SimpleJdbcTemplate to do that for me. Here's the code that does work:

 SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(
				myObj);
KeyHolder keyHolder = createKeyHolder();
getSimpleJdbcTemplate().getNamedParameterJdbcOperations().update(
				mySql, namedParameters, keyHolder);
return keyHolder.getKey().longValue();

[DaveyS]

I'm not suggesting you change the SQL, just try using the Microsoft drivers.

[easternwahoo]

Thanks for your help. With the MS drivers, it works fine. IBM's driver works fine for this (from NamedParameterJdbcTemplate):
ps = con.prepareStatement(this.actualSql, PreparedStatement.RETURN_GENERATED_KEYS);

.. but it fails when AbstractJdbcInsert, the superclass for SimpleJdbcInsert, invokes this:
ps = con.prepareStatement(getInsertString(), getGeneratedKeyNames());

[DaveyS]

I'm glad it works with the MS drivers.

I'd recommend using them all the time when connecting to SQL Server as they always seem to work the best.