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
Getting SQL Exception when uploading in valid date
S.P.L Srikanth
2013-02-04 18:03:18.0
Hi,
We recently have purchased Excel_Core_JDBC20.jar, HXTT_Common_JDBC20.jar In order to upload 2010 Excel file in JDK 1.2.
I am using below code and when I am trying to upload a file, I am getting SQLException.

Code:
private String convertExcelToBean(MappingBean mappingBean, boolean is2010Excel) throws Exception {
String errorMessage = null;
PreparedStatement pstmt = null;
Statement stmt = null;
ResultSet rs = null;
Connection con = null;
String databaseName = "Database"+getRandomNumber();
try {
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
String url=null;
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("firstRowHasNames","false");//no header rows.
if(is2010Excel) {
url="jdbc:excel:///_memory_/?versionNumber=XLSX";
} else {
url="jdbc:excel:///_memory_/";
}
con = DriverManager.getConnection(url,properties);
stmt = con.createStatement();
DatabaseMetaData databaseMetadata = con.getMetaData();
rs = databaseMetadata.getCatalogs();
List list = new ArrayList();
while (rs.next()) {
String listofDatabases=rs.getString("TABLE_CAT");
list.add(listofDatabases);
}
boolean databaseExists = true;
while(databaseExists) {
if(list.contains(databaseName)) {
databaseName = "Database"+getRandomNumber();
} else {
databaseExists = false;
}
}
String sql = "create database if not exists "+databaseName+" ?";
pstmt = con.prepareStatement(sql);
pstmt.setObject(1,mappingBean.getFileContentStream()); // Load a file from jsp and get the input stream.
pstmt.executeUpdate();
pstmt.close();
mappingBean.getFileContentStream().close();
//sql = "Select * from "+databaseName+".Sheet1";
//sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES ";
//stmt=con.createStatement();
//rs = stmt.executeQuery(sql);
// rs.beforeFirst();
if(is2010Excel) {
rs = con.getMetaData().getTables(databaseName+".xlsx", null, "%", null);
} else {
rs = con.getMetaData().getTables(databaseName+".xls", null, "%", null);
}
System.out.println("1");
String tableName = "";
while(rs.next()) {
System.out.println("2");
tableName = rs.getString("TABLE_NAME");
System.out.println("3");
System.out.println("Table Name "+tableName);
}
if(StringUtils.isEmpty(tableName)) {
closeMemoryDB(con, databaseName);
return FileUploadConstants.PC_INTERNAL_ERROR;
}
sql = "Select * from "+databaseName+"."+tableName;
stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sql);
if(rs !=null) {
rs.last(); ----- Here I am getting Excception
int totalSize = rs.getRow();
}


} catch( SQLException sqle ) {
log.error("SQL Exception due to "+sqle.getMessage());
System.out.println("SQL Exception due to "+sqle.getMessage());
errorMessage = FileUploadConstants.PC_INTERNAL_ERROR;
} catch (Exception e) {
log.error("Exception due to "+e.getMessage());
System.out.println("Exception due to "+e.getMessage());
errorMessage = FileUploadConstants.PC_FILE_INVALID_FORMAT;
} finally {
if(null != rs) {
rs.close();
}
if(null != pstmt) {
pstmt.close();
}
if(null != stmt) {
stmt.close();
}
}
closeMemoryDB(con, databaseName);
return errorMessage;
}
private void closeMemoryDB(Connection con, String databaseName) throws SQLException {
Statement stmt = null;
try {
System.out.println("4");
String sql = "drop database "+databaseName;
stmt = con.createStatement();
int i = stmt.executeUpdate(sql);
System.out.println("Database dropped "+i);
} catch(SQLException ex) {
System.out.println("Cannot drop database due to "+ex.getMessage());
} finally {
if(null != stmt) {
stmt.close();
}
if(null != con) {
con.close();
}
}
}

Error:
java.sql.SQLException: Failed to get timestamp according to pattern yyyy-MM-dd hh:mm:ss from java.lang.String: Footer Dates
at java.lang.Throwable.(Throwable.java:52)
at java.lang.Throwable.(Throwable.java:66)
at java.sql.SQLException.(SQLException.java:51)
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.global.SQLState.transferValueException(Unknown Source)

I will send the file through mail. Please take a look and solve this issue

Note: The latest jar files has database created purely in memory. If you fix this issue, first revert back to previous version(Creating database in memory using temp files) and provide your fix and release the jar files (We are facing issues when database is created purely in memory).
Re:Getting SQL Exception when uploading in valid date
HXTT Support
2013-02-05 05:14:39.0
> java.sql.SQLException: Failed to get timestamp according to pattern
> yyyy-MM-dd hh:mm:ss from java.lang.String: Footer Dates
You can add maxScanRows=0 or maxScanRows=-1 connection property.

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