Ian Giguère
Ian Giguère

Reputation: 31

JDBC AS/400 FETCH FIRST [x] ROWS ONLY not used to discriminate preparedStatement caching

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.

Upvotes: 3

Views: 548

Answers (0)

Related Questions