StoredProcedure

[djf57]

I've wasted a large part of this week getting nowhere trying to invoke a stored procedure that takes an array of Longs. I wouldn't have thought it ought to be too difficult but having trawled the web I am still no closer. I begin to suspect that this doesn't work without apalling hacks.

class Xxx extends StoredProcedure {

Xxx(JdbcTemplate) {
super(template, schema.package.procedureName)
declareParameter(new SqlParameter("arg name", TYPES.ARRAY, "LONG_ARRAY")
}

// where LONG_ARRAY is defined in the schema

Map execute(Long[] data) {
Map inputs = new HashMap()
inputs.put("arg name", new AbstractSqlTypeValue(Connection c, int type, String typeName) {
ArrayDescriptor ad = new ArrayDescriptor(typeName, c);
return new ARRAY(ad, c, data)

Map outputs = execute(inputs)
}

Above is a cut down version of one of the many code variants I have tried.

Could someone kindly point out what I am doing wrong, or confirm that it is just not a good idea to invoke Stored Procedures via Spring.


Thanks.

[djf57]

Sorry, my original cut and paste lost some content:

inputs.put("an_array", new AbstractSqlTypeValue() {
@Override
protected Object createTypeValue(Connection c, int type, String typeName) {

[djf57]

! and the previous attempt at a correction sent the reply before I intended (tab key seems to have undesired power)

inputs.put("an_array", new AbstractSqlTypeValue() {
@Override
protected Object createTypeValue(Connection c, int type, String typeName) {
ArrayDescriptor ad = new ArrayDescriptor (<SCHEMA_NAME> + "." + typeName, c);
return new ARRAY(ad, c, <array of Longs>);
}});

return execute(inputs);

It seems that the call to super in the constructor must be of the form: super(jdbcTemplate, <schema>.<package>.<procedure>)
but schema name is not case sensitive here.

Ctr requires declareParameter(new SqlParameter(<param name>, Types.ARRAY, "LONG_ARRAY");

and, most confusingly,

the ArrayDescriptor requires the typeName to be prefixed with the upper case schema name - this is case sensitive.

All in all a very non-intuitive and error-prone pain, hence the post in the hope that it will spare someone else trawling through this fog.