3

I am using a connection pool to connect to an AS/400. I am trying to fetch a dynamic number of orders with a prepared statement. When I use a statement parameter to define the maximum number of orders to fetch, there seems to be some kind of caching taking place. On the first call, I get the correct number of orders, but on the following calls, even if I change the maximum number of orders to fetch, I get the same count as the first call. If I open a new connection on every call, everything works fine. If I call setReuseConnections(false) on the data source, everything works fine. If I remove the parameter for the "FETCH FIRST [x] ROWS ONLY" statement and use a placeholder in the SQL text that I replace with the maximum number of order to fetch on every call, everything works fine.

What kind of caching is taking place? Can I go around it another way? Is this a bug?

Here is a sample app, all you need is a AS/400 database to connect to!

package com.richelieu.web.test.as400;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.ibm.as400.access.AS400JDBCManagedConnectionPoolDataSource;

public class CachingPreparedStatement {

    // Database connection info
    private static final String SERVER_NAME = "<server ip address>";
    private static final String LIBRAIRIES = "<library name>";
    private static final String USER = "<user name>";
    private static final String PASSWORD = "<password>";

    // Query parameters
    private static final BigDecimal DATE_FROM = new BigDecimal("20170701000000"); //(format: YYYMMDDHHMMSS)
    private static final int MAX_ORDER_COUNT_1 = 1;
    private static final int MAX_ORDER_COUNT_2 = 2;
    private static final int MAX_ORDER_COUNT_3 = 3;

    private static final String SELECT_WITH_ORDER_COUNT_PARAM =
            "SELECT <column> FROM <table> WHERE <tsinsert> >= ? ORDER BY SCCONO FETCH FIRST ? ROWS ONLY";

    public static void main(String[] args) {
        DataSource ds = initDataSource();
        System.out.println("Test with param:");
        testFetchOrdersWithMaxOrderCountParam(ds, MAX_ORDER_COUNT_1); 
        testFetchOrdersWithMaxOrderCountParam(ds, MAX_ORDER_COUNT_2);
        testFetchOrdersWithMaxOrderCountParam(ds, MAX_ORDER_COUNT_3);
        testFetchOrdersWithMaxOrderCountParam(ds, MAX_ORDER_COUNT_1);
        testFetchOrdersWithMaxOrderCountParam(ds, MAX_ORDER_COUNT_2);
        testFetchOrdersWithMaxOrderCountParam(ds, MAX_ORDER_COUNT_3);
    }

    private static void testFetchOrdersWithMaxOrderCountParam(DataSource ds, int maxOrderCount) {
        try (Connection connection = ds.getConnection()) {
            int actualOrderCount = fetchOrdersWithMaxOrderCountParam(connection, DATE_FROM, maxOrderCount);
            System.out.println(String.format("Order count: %d", actualOrderCount));
        } catch (SQLException pEx) {
            throw new RuntimeException(pEx.getMessage(), pEx);
        }
    }

    public static int fetchOrdersWithMaxOrderCountParam(Connection pConnection, BigDecimal dateFrom, int maxOrderCount) {
        try (PreparedStatement statement = pConnection.prepareStatement(SELECT_WITH_ORDER_COUNT_PARAM)) {
            statement.setBigDecimal(1, dateFrom);
            statement.setInt(2, maxOrderCount);
            int orderCount = 0;
            try (ResultSet rs = statement.executeQuery()) {
                while(rs.next()) {
                    orderCount++;
                }
            }
            return orderCount;
        } catch (SQLException pEx) {
            throw new RuntimeException(pEx.getMessage(), pEx);
        } 
    }

    private static AS400JDBCManagedConnectionPoolDataSource initDataSource() {
        AS400JDBCManagedConnectionPoolDataSource ds = new AS400JDBCManagedConnectionPoolDataSource(SERVER_NAME, USER, PASSWORD);
        ds.setLibraries(LIBRAIRIES);
        ds.setMinPoolSize(1);
        ds.setMaxPoolSize(2);
        //ds.setBlockCriteria(0);
        //ds.setBlockSize(0);
        //ds.setBlockCriteria(0);
        //ds.setExtendedDynamic(false);
        //ds.setReuseConnections(false); // only setting I found to resolve my "caching" problem
        return ds;
    }
}

Thanks in advance.

  • If I understand correctly, you prepare a statement with maxOrderCount equals to 10, execute the statement and get 10 resutls back but if you prepare the statement again specifying 5 you still get 10 resluts back? – cquezel Jul 06 '17 at 13:42
  • Exactly! It's like if the "FETCH FIRST ... ROWS ONLY" part of the statement is not considered to tell if the second statement is different from the first. So it thinks the first statement is good... – Ian Giguère Jul 06 '17 at 13:51

0 Answers0