Announcement Announcement Module
No announcement yet.
enterpriseddb stored procedure Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • enterpriseddb stored procedure

    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 {
    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));

    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());

    String idStr = "";
    String freqTypeStr = "";
    String freqIntervalStr = "";
    String freqIdStr = "";
    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);


    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
    It loops and doesn't execute the stored procedure.
    What can be the reason?

  • #2
    Where can I at least find list of data bases spring jdbc supports?


    • #3
      There is a bug in the EnterpriseDB JDBC driver. I hit the same issue about a year ago and posted on their forum - see 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.


      • #4
        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.