Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
Need URGRNT help on this simple query
OMkar
2006-01-22 00:00:00.0
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
Re:Need URGRNT help on this simple query
HXTT Support
2006-01-24 00:00:00.0
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?
Re:Re:Need URGRNT help on this simple query
OMkar
2006-01-24 00:00:00.0
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
Re:Re:Re:Need URGRNT help on this simple query
OMkar
2006-01-24 00:00:00.0
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
Re:Re:Re:Re:Need URGRNT help on this simple query
HXTT Support
2006-01-24 00:00:00.0
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();
}
}
}
Re:Re:Re:Re:Re:Need URGRNT help on this simple query
OMkar
2006-01-24 00:00:00.0
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
Re:Re:Re:Re:Re:Re:Need URGRNT help on this simple query
HXTT Support
2006-01-26 00:00:00.0
>What next?
You can try http://www.hxtt.com/test/DBF_JDBC30.jar .

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap