Our question was what happens to the rows in a global temporary table when populated from a session obtained from a connection pool. Are they still there for the next application user to borrow the same session from the pool?
The rows are retained across connection pool reserved sessions if the table is configured as “on commit preserve rows”.
In other words, if user A checks connection 4 out of the pool, adds rows to a global temporary table, checks the connection back in, and user B then checks connection 4 out of the pool, the rows will be visible to user B by default. The only way to get around this is either configure the table as “on commit delete rows” (the default), or otherwise remove the rows, such truncate the table at the start of user B’s session.
The page below will prove this. Repeated requests will show an increasing number of rows in the pool, even though the connection was “closed” when it was returned to the pool. Perhaps other connection pool implementations allow you to physically close the connection.
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%@ page import="weblogic.logging.*"%>
<%
try {
InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup("jdbc/test");
Connection conn = ds.getConnection();
Statement stm = conn.createStatement();
ResultSet rst = stm.executeQuery("select * from t2");
while (rst.next()) {
out.println("before t2 has this row " + rst.getString(1) + "
");
}
stm.execute("insert into t2 values(1)");
conn.close();
conn = ds.getConnection();
stm = conn.createStatement();
rst = stm.executeQuery("select * from t2");
while (rst.next()) {
out.println("before commit t2 has this row " + rst.getString(1) + "
");
}
conn.commit();
rst = stm.executeQuery("select * from t2");
while (rst.next()) {
out.println("after commit t2 has this row " + rst.getString(1) + "
");
}
conn.close();
}
catch (Exception e) {
out.println(e.getMessage());
}
%>