Reputation: 31
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