I am trying to test HxTT using a simple join. I want to join two tables and extract records on given WHERE condition. I issue the query as follows,
SELECT bill_no, bill_date, net_amt, category, class FROM bill b LEFT JOIN(bill b JOIN deli d ON (b.bill_no != d.bill_no)) WHERE b.category != 'O' and TRIM(b.class) != 'igb' and LEFT(TRIM(b.nill_no),2) != 'GB')
This query gives me 'OutOfMemoryError' error. Can anyone pleaes guide me on this query asap??
Thanks and regards
OMkar
|
You can try:
SELECT bill_no, bill_date, net_amt, category, class FROM bill b, deli d WHERE b.bill_no != d.bill_no and b.category != 'O' and TRIM(b.class) != 'igb' and LEFT(TRIM(b.nill_no),2) != 'GB'
What're the row counts in bill and deli?
|
SELECT bill_no, bill_date, net_amt, category, class FROM bill b, deli d WHERE b.bill_no != d.bill_no and b.category != 'O' and TRIM(b.class) != 'igb' and LEFT(TRIM(b.bill_no),2) != 'GB'
ALSO GIVES THE SAME 'OutOfMemoryError'
Any other type of join???
OMkar
|
In Bill.dbf there are 6191 records and in deli.dbf there are 8290 records
Waiting for your reply... This is the last week.. I need one successfull join query on this size of database to make comments in my project. And the expression in this above query is for some important report generation.
PLease help!
OMkar
|
Don't worry. Please download the latest package, and retry. In my test, your issue failed to recur.
/* Used to recur a reported OutOfMemoryError issue, but failed to recure */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSetMetaData;
import java.sql.ResultSet;
public class testJoin {
public static void main(String[] args) {
try {
Class.forName("com.hxtt.sql.dbf.DBFDriver").newInstance();
String url = "jdbc:dbf:/c:/test/";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
String sql = "create table bill (bill_no varchar(10),bill_date date, net_amt double, category varchar(2), class varchar(10)); create index bill_no,category on bill (bill_no,category);"
+ "create table deli (bill_no varchar(10)); create index bill_no on deli (bill_no);"
;
stmt.executeUpdate(sql);
sql =
"INSERT INTO bill (bill_no,bill_date,net_amt,category,class)VALUES (?,?,?,?,?)";
PreparedStatement ps = con.prepareStatement(sql);
for (int i = 0; i < 10000; i++) {
ps.setString(1, "no" + (i % 1000));
ps.setString(2, "2006-01-24");
ps.setDouble(3, (i + 1.0) / 5);
ps.setString(4, ('a' + (i % 27)) + "");
ps.setString(5, i % 88 == 0 ? "igb" : "class" + i);
ps.executeUpdate();
}
ps.close();
sql = "INSERT INTO deli (bill_no)VALUES (?)";
ps = con.prepareStatement(sql);
for (int i = 0; i < 10000; i++) {
ps.setString(1, "no" + (i % 100));
ps.executeUpdate();
}
ps.close();
sql = "SELECT bill_no, bill_date, net_amt, category, class FROM bill b, deli d WHERE b.bill_no != d.bill_no and b.category != 'O' and TRIM(b.class) != 'igb' and LEFT(TRIM(b.bill_no),2) != 'GB'";
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int j = 1; j <= iNumCols; j++) {
System.out.println(resultSetMetaData.getColumnLabel(j)
+ " " +
resultSetMetaData.getColumnTypeName(j)
+ " " + resultSetMetaData.getColumnType(j)
+ " " + resultSetMetaData.getPrecision(j)
+ " " + resultSetMetaData.getScale(j)
);
}
Object colval;
rs.beforeFirst();
long ncount = 0;
while (rs.next()) {
ncount++;
for (int j = 1; j <= iNumCols; j++) {
colval = rs.getObject(j);
System.out.print(colval + " ");
}
System.out.println();
}
System.out.println("row count:" + ncount);
rs.close();
stmt.close();
con.close();
}
catch (SQLException sqle) {
do {
System.out.println(sqle.getMessage());
System.out.println("Error Code:" + sqle.getErrorCode());
System.out.println("SQL State:" + sqle.getSQLState());
sqle.printStackTrace();
}
while ( (sqle = sqle.getNextException()) != null);
}
catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
|
I created new testJoin and tried your code, but now error says 'For evaluation purpose.....queries more than 25 not allowed.. ' And I have downloaded the evaluation copy on 20/01/2006.
What next?
OMkar
|
>What next?
You can try http://www.hxtt.com/test/DBF_JDBC30.jar .
|
Main
webmaster@hxtt.com