BALDWIN, ALAN J [AG-Contractor/1000] wrote:
Michael,
Thanks for the responses. Here is what I know so far:
** There is no other finally block that would close that result set. The
only operations that happen on that result set (other than the finally block
you saw) are rs.getString(), rs.getInt(), etc...
** I am quite certain that this is not your run-of-the mill jdbc error. I
would not be posting here if I thought it were.
** I can switch the order of the inner join clauses and reproduce this on
datasets that previously had no problem. For example:
//This returns data:
SELECT * FROM DeliveryNotification dn
INNER JOIN DealerTransaction dt ON dn.InventoryTransactionID =
dt.TransactionId
INNER JOIN Product p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =
dn.SeedYear
//more joins here...
//This throws the exception:
SELECT * FROM DealerTransaction dt
INNER JOIN DeliveryNotification dn ON dn.InventoryTransactionID =
dt.TransactionId
INNER JOIN Product p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =
dn.SeedYear
//more joins here...
We have one DealerTransaction row to many DeliveryNotification rows. The
joins following those three tables are mostly just lookup data (status
tables and such... our database is very normalized), and they remain
unchanged in both versions of this query.
To me, this smells like something out of my control (jdbc driver, database
engine?) is running out of resources. This seems to be more frequent the
larger the dataset gets. This dataset for example, contains about 250
DealerTransaction rows, ~1000 DeliveryNotification rows, and ~5000 products.
Is this large by Derby standards?
Regards,
-Alan-
Hi Alan,
The amount of data that you have mentioned is really minimal. We have
run tests on Derby using joins of multiple tables (~64) and views with
huge amount of data - 500K ( see
http://issues.apache.org/jira/browse/DERBY-805 in JIRA, for example). I
am sure there are many users out there
who are using Derby is much complex scenarios. It is really strange you
are hitting this error.
I assume your app is a simple JDBC one without any object-relational
mapper (Hibernate etc.) in between.
Following your email thread and based on the details you have provided I
tried re-creating the issue but was not successful. Attached is my
simple app. I noticed the data types you are using are pretty straight
forward so I have stuck to those. However I keep coming
across the 'data-dependent' issue in your thread, which seems unclear to
me as to what data to use in the repro. Moroever it is not clear
what type of isolation level, type of ResultSet (scrollable etc.) is
being used.
Is it possible that you can create a simple reproduction (could modify
the attached script to suit your needs) and create a JIRA issue.
This would be really really benefical in getting to the core of the problem.
-Rajesh
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class InnerJoinTest {
public static String jdbcurl="jdbc:derby:transdb;create=true";
public static String driverClass="org.apache.derby.jdbc.EmbeddedDriver";
static Connection conn=null;
/**
* @param args
*/
public static void main(String[] args) {
try{
Class.forName(driverClass);
conn=DriverManager.getConnection(jdbcurl);
//createTables();
//insertData();
doSelect();
}catch(ClassNotFoundException cne){
cne.printStackTrace();
}catch(SQLException sqe){
sqe.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
}
public static void doSelect() throws SQLException{
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery("SELECT * FROM DeliveryNotification dn INNER JOIN DealerTransaction dt ON dn.transactionId=dt.transactionId INNER JOIN Products p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =dn.SeedYear");
ResultSetMetaData rsmd=rs.getMetaData();
int rows=0;
while(rs.next()){
for(int i=1;i<=rsmd.getColumnCount();i++){
System.out.print(rs.getString(i)+"\t");
}
System.out.println("\n");
rows++;
}
System.out.println("Total rows obtained "+rows);
//Query # 2
rs.close();
rs=stmt.executeQuery("SELECT * FROM DealerTransaction dt INNER JOIN DeliveryNotification dn ON dn.transactionId =dt.transactionId INNER JOIN ProductS p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =dn.SeedYear");
rsmd=rs.getMetaData();
rows=0;
while(rs.next()){
for(int i=1;i<=rsmd.getColumnCount();i++){
System.out.print(rs.getString(i)+"\t");
}
System.out.println("\n");
rows++;
}
System.out.println("Total rows obtained "+rows);
rs.close();
stmt.close();
}
public static void createTables() throws SQLException{
Statement stmt=conn.createStatement();
stmt.executeUpdate("Create table PRODUCTS(productUPC int, productCode char(15), productName varchar(25), seedYear int)");
System.out.println("Products table created");
stmt.executeUpdate("Create table DeliveryNotification (deliveryId int, productUPC int , seedYear int, transactionId bigint, dealerName varchar(25))");
System.out.println("DeliveryNotification table created");
stmt.executeUpdate("Create table DealerTransaction (transactionId bigint, dealerName varchar(25), TransactionDate Date, seedyear int, ShipToName varchar(255), ShipFromName varchar(255), status char(15))");
System.out.println("DealerTransaction table created");
stmt.close();
}
public static void insertData() throws SQLException{
PreparedStatement ps=conn.prepareStatement("Insert into PRODUCTS values(?,?,?,?)");
conn.setAutoCommit(false);
for(int i=0;i<5000;i++){
ps.setInt(1,i);
ps.setString(2,"P#"+i);
ps.setString(3,"PRODUCT NAME - "+i);
ps.setInt(4,i);
ps.addBatch();
}
System.out.println("Insert into PRODUCTS table successful - "+ps.executeBatch().length);
conn.commit();
ps=conn.prepareStatement("Insert into DeliveryNotification values(?,?,?,?,?)");
int dlrId=1;
for(int i=0;i<1250;i++){
ps.setInt(1,i);
ps.setInt(2,1000+i);
ps.setInt(3,1000+i); //matching info as PRODUCTS
ps.setLong(4,dlrId);
ps.setString(5,"DEALER - "+dlrId);
if(i%5==0){
dlrId+=1; //5 entries for each Dealer
}
ps.addBatch();
}
System.out.println("Insert into DeliveryNotification table successful - "+ps.executeBatch().length);
conn.commit();
ps=conn.prepareStatement("Insert into DealerTransaction values(?,?,?,?, ? , ?, ?)");
for(int i=1;i<=250;i++){
ps.setLong(1,i);
ps.setString(2,"DEALER - "+i);
ps.setDate(3,new Date(System.currentTimeMillis()));
ps.setInt(4,i);
ps.setString(5,"SHIPPED TO ADDRESS "+i);
ps.setString(6,"SHIPPED FROM ADDRESS "+i);
ps.setString(7,"SHIPPED");
ps.addBatch();
}
System.out.println("Insert into DealerTransaction table successful - "+ps.executeBatch().length);
conn.commit();
conn.setAutoCommit(true);
ps.close();
}
}
