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).
|
> 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.
|
Main
webmaster@hxtt.com