enterpriseddb stored procedure

[olgas1]

Hi,
I am running application on top of enterprisedb data base.
I am trying to call stored procedure by using spring API.
Here is the code:
private class UpdateFreuqenciesCounterStoredProcedure extends StoredProcedure {
private final static String PROCEDURE_NAME = "Da_Querygateway_Pg.update_frequencies";

private final static String PARAM_CUSTOMER_ID = "customer_id";
private final static String PARAM_EVENT_DATE = "event_date";

private final static String PARAM_ID_ARRAY = "custAdFreqTbl";
private final static String PARAM_FREQUENCY_ID_ARRAY = "freqIdTbl";
private final static String PARAM_FREQ_TYPE_ID_ARRAY = "freqTypeIdTbl";
private final static String PARAM_FREQ_INVERVAL_ARRAY = "freqMinIntervalTbl";
private final static String PARAM_AD_ID_ARRAY = "adIdTbl";



public UpdateFreuqenciesCounterStoredProcedure(DataSource ds) throws SQLException {
super(ds,PROCEDURE_NAME);
declareParameter(new SqlParameter(PARAM_CUSTOMER_ID, java.sql.Types.BIGINT));
declareParameter(new SqlParameter(PARAM_EVENT_DATE, java.sql.Types.TIMESTAMP));
declareParameter(new SqlParameter(PARAM_ID_ARRAY,java.sql.Types.VARCHAR ));
declareParameter(new SqlParameter(PARAM_FREQUENCY_ID_ARRAY,java.sql.Typ es.VARCHAR ));
declareParameter(new SqlParameter(PARAM_FREQ_TYPE_ID_ARRAY, java.sql.Types.VARCHAR));
declareParameter(new SqlParameter(PARAM_FREQ_INVERVAL_ARRAY, java.sql.Types.VARCHAR));
declareParameter(new SqlParameter(PARAM_AD_ID_ARRAY, java.sql.Types.VARCHAR));
compile();
}

public void execute(Long customerId, Date eventDate, List<Long> adIdList, List<CustomerAdFrequency> frequencyList) throws SQLException {
HashMap<String, Object> paramters = new HashMap<String, Object>(7);

StringBuffer adIdBuf = new StringBuffer();
StringBuffer idBuf = new StringBuffer();
StringBuffer freqTypeBuf = new StringBuffer();
StringBuffer freqIntervalBuf = new StringBuffer();
StringBuffer freqIdBuf = new StringBuffer();

//int index = 0;
for (Long adId: adIdList) {
//adIdArray[index++] = adId;
adIdBuf.append(','+ adId);
}
String adIdStr = adIdBuf.substring(1);

//index = 0;
for (CustomerAdFrequency adFrequency: frequencyList) {

idBuf.append(','+ adFrequency.getId());
freqTypeBuf.append(','+ adFrequency.getFrequency().getFrequencyType().getI d());
freqIntervalBuf.append(','+ adFrequency.getFrequency().getMinimumIntreval());
freqIdBuf.append(',' + adFrequency.getFrequency().getId());
//index++;
}

String idStr = "";
String freqTypeStr = "";
String freqIntervalStr = "";
String freqIdStr = "";
if(idBuf.length()>0)
idStr = idBuf.substring(1);
if(freqTypeBuf.length() >0)
freqTypeStr = freqTypeBuf.substring(1);
if(freqIntervalStr.length() >0)
freqIntervalStr = freqIntervalBuf.substring(1);
if(freqIdStr.length() >0)
freqIdStr = freqIdBuf.substring(1);

paramters.put(PARAM_CUSTOMER_ID, customerId);
paramters.put(PARAM_EVENT_DATE, eventDate);
paramters.put(PARAM_ID_ARRAY, idStr);
paramters.put(PARAM_FREQUENCY_ID_ARRAY,freqIdStr );
paramters.put(PARAM_FREQ_TYPE_ID_ARRAY, freqTypeStr);
paramters.put(PARAM_FREQ_INVERVAL_ARRAY, freqIntervalStr);
paramters.put(PARAM_AD_ID_ARRAY, adIdStr);
super.execute(paramters);
}


}

When I am executing it I am getting an endless loop that prints the following:
2008-08-05 18:02:30,354 INFO core.JdbcTemplate - Added default SqlReturnUpdateCount parameter named #update-count-xxx1
2008-08-05 18:02:30,354 INFO core.JdbcTemplate - Added default SqlReturnUpdateCount parameter named #update-count-xxx2
etc..
It loops and doesn't execute the stored procedure.
What can be the reason?
Thanks.

[olgas1]

anybody?
Where can I at least find list of data bases spring jdbc supports?

[trisberg]

There is a bug in the EnterpriseDB JDBC driver. I hit the same issue about a year ago and posted on their forum - see http://forums.enterprisedb.com/posts/list/1056.page. Doesn't seem as if they fixed the issue though. If you have a support contract you could try raising a support request.

In the meantime you can use a custom JdbcTemplate for your StoredProcedure. Set the JdbcTemplate to skip the result processing - setSkipResultsProcessing(true) and then pass the JdbcTemplate in to the constructor instead of the DataSource.

Spring supports using any JDBC driver that implements the JDBC 3.0 or later specifications.

[olgas1]

Thanks!
One more question about enterprisedb: I couldn't figure out how to pass array to the stored procedure, I saw many Oracle examples that use Oracle's Array and ArrayDescriptor, but don't know how to do it without Oracle's APIs.
I tried jdbcTemplate.execute("call procName {?,?}", new Object[]{arr1, arr2})
but I got an exception that in or out parameters are not specified(something like this).
Any hint will be welcome.
Thanks again.