Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.0
  HXTT Cobol v4.0
  HXTT DBF v7.0
  HXTT Excel v6.0
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Paradox v7.0
  HXTT PDF v1.1
  HXTT Text(CSV) v7.0
  HXTT Word v1.0
  HXTT XML v3.0
Offshore Outsourcing
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
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT Excel
Changing resultset size
Srikanth
2012-07-16 12:23:09.0
Hi,
I am loading an excel file from client side and processing it. So, i have used java.io.InputStream and created database in _memory_. I can able to read data but, the problem is when i have records which are more than 100 let us suppose 110 records. When i use plain SQL and load the data the ResultSet is loading 99 recorsd only. So, remaining records are not available in ResultSet. I tried to check the total record count of the table in memory and it says there are 110 records, but why my ResultSet is returning 99 only. I even tried by setting stmt.setFetchSize(1000); But, it is not displaying. Please help.

Note: I am using JDK 1.2, HXTT_Common_JDBC20.jar and Excel_Core_JDBC20.jar
Re:Changing resultset size
Srikanth
2012-07-16 13:11:42.0
Here i found something interesting while debugging.

PreparedStatement stmt1=con.prepareStatement(sql);
stmt1.setMaxRows(200);
System.out.println(stmt1.getMaxRows()); Result is 200
rs = stmt1.executeQuery(sql);
System.out.println(stmt1.getMaxRows()); Result is 100

this is why i am getting 100 results in ResultSet.So, while executing the Query somewhere we are overwriting the max rows as 100. Someone help me regarding this?

Re:Re:Changing resultset size
HXTT Support
2012-07-16 16:03:41.0
It's at evaluation limitation at http://www.hxtt.com/excel/introduction.html
Re:Re:Re:Changing resultset size
Srikanth
2012-07-17 04:48:54.0
oh.. fine, then once we get the licensed copy, this issue will be solved right? please confirm. so that i can go forward
Re:Re:Re:Re:Changing resultset size
HXTT Support
2012-07-17 05:04:54.0
Yeah.
Re:Re:Re:Re:Re:Changing resultset size
Srikanth
2012-07-17 06:01:18.0
Thanks for quick response. I will go forward and speak to our team for buying license copy. Please make a note that we need this fix to be available in HXTT_Common_JDBC20.jar and Excel_Core_JDBC20.jar only because we are working on JDK 1.2 and we can use the above given jar files which are compatible with our application. we cannot upgrade to any other new version.
Re:Re:Re:Re:Re:Re:Changing resultset size
HXTT Support
2012-07-17 16:55:42.0
You can download a simple Excel_JDBC20.jar or Excel_Embedded_JDBC20.jar to avoid too much jar files in your directory.
Re:Re:Re:Re:Re:Re:Re:Changing resultset size
Srikanth
2012-07-18 06:06:16.0
Yes, i have done that before. But, due to some reason...Excel_JDBC20.jar is not creating table for XLSX files. So i have downloaded these 2 jar files and tried... and it worked for me
Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
HXTT Support
2012-07-18 16:42:37.0
>due to some reason...Excel_JDBC20.jar is not creating table for XLSX files.
What's your error message? The Excel_JDBC20.jar should support XLSX too.
Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
Srikanth
2012-07-23 07:01:45.0
Sorry for late reply. I was busy in getting approvals to buy this software.

We have 2 issues here
1. Even i put properties.setProperty("versionNumber","XLSX"); the driver is trying to create .xls

2. There was no error while creating the database. But, the database is not created. I tried the below query

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
there is no value for this column.
And this is working fine when i use HXTT_Common_JDBC20.jar and Excel_Core_JDBC20.jar
Please check and give your comments.
Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
HXTT Support
2012-07-23 17:10:50.0
Passed test with Access_JDBC20.jar (1,222,745 bytes);

For instance, click Access_JDBC20.jar, then a "Config the url setting" window will show.
Input
URL Name:testXLSX2
URL URL Info: jdbc:excel:////d:/test/excel/testxlsx?versionNumber=XLSX
Click "Add Config"

Choose testXLSX2, Click "View Monitor".
Choose "Server Catalogs", Click "Build conn".
Input "create database if not exists axlxs; ", Click "Execute". Now an axlxs.xlsx will be listed at d:/test/excel/testxls directory.
Input "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ", Clicke "Execute", a Sheet1 is showed.

Through code, you can do more.



Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
Srikanth
2012-07-23 19:10:24.0
Thanks a lot... i was trying with Excel_JDBC20.jar file..i will try with Access_JDBC20.jar and let you know....
Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
HXTT Support
2012-07-23 19:15:28.0
Sorry. Wrong typo. It's Excel_JDBC20.jar (1,222,745 bytes);
Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
Srikanth
2012-07-24 16:27:39.0
I have worked with Excel_JDBC20.jar file under JDK 1.2 and Websphere 4.
The table is not creating properly.
Here is the server code. Please try and confirm

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
InputStream in = request.getInputStream();

try {
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
String url="jdbc:excel:///_memory_/";//Enabled it if you have not disk access right.
Properties properties=new Properties();
properties.setProperty("tmpdir","_memory_");//Enabled it if you have not disk access right.
properties.setProperty("delayedClose","-1");//Release database at once.
properties.setProperty("versionNumber","XLSX");
Connection con = DriverManager.getConnection(url,properties);
PreparedStatement pstmt;
String sql = "create database if not exists test ?";
pstmt = con.prepareStatement(sql);
pstmt.setObject(1,in); // Please load a file from jsp and get the input stream.
pstmt.executeUpdate();
pstmt.close();
in.close();
sql = "Select * from test.Sheet1";
//sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES ";
Statement stmt=con.createStatement();
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.getColumnDisplaySize(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.execute("drop database test");
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:Re:Re:Re:Changing resultset size
HXTT Support
2012-07-24 17:38:07.0
Your code is correct.
After I change
InputStream in = request.getInputStream();
into
URL httpurl = new URL("http://www.hxtt.com/test/testxls.xlsx");
URLConnection urlConnection = httpurl.openConnection();
InputStream in = urlConnection.getInputStream();
Your code can print the data in new xlsx file.

>The table is not creating properly.
It create in memory, and drop by youself. If you change
String url="jdbc:excel:///_memory_/";//Enabled it if you have not disk access right.
into a file path, and remove "drop database test", you will find such a xlsx file on your disk.

Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
Srikanth
2012-07-24 18:36:02.0
No, for
URL httpurl = new URL("http://www.hxtt.com/test/testxls.xlsx");
URLConnection urlConnection = httpurl.openConnection();
InputStream in = urlConnection.getInputStream();
The table is creating fine...

When I use InputStream in = new FileInputStream("C:\test.xlsx");
then also it is creating fine...

I am upload a file from jsp, and in java using apache-fileupload api, i am getting the FileItem and from FileItem, i am getting the inputstream.
In this case, the database is not creating. I have tested this.

Please give your comments.
Re:Re:Re:Re:Re:Re:Re:Re:Changing resultset size
HXTT Support
2012-07-24 19:09:47.0
>I am upload a file from jsp, and in java using apache-fileupload api, i am
> getting the FileItem and from FileItem, i am getting the inputstream.
>In this case, the database is not creating.
Please write that inputstream to a file first, then email us it, or check youself to see whether it's a normal XLSX file.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap