Everything is fine as long as the user knows the name of the Excel Sheet he/she wants to access. Given the filename how do we get a list of sheets (tables) within the Excel file?
for example...
We can provide a list of Excel files to the usr and use that to form the database portion of the Select statement e.g. "database"."?" but we want to query the Excel file first and return a list of Sheets for the user to choose from since the user may not know how many Sheets are in the Excel file.
All we want to know is how do we form a select statement to return the Sheets within the Excel file. Using your examples you only give "database"."table" we don't know the table (Sheet) name until we look inside.
thanks frank.
|
>we don't know the table (Sheet) name until we look inside.
Easy. Please read DatabaseMetaData api in JDK document.
DatabaseMetaData dbmd = con.getMetaData();
dbmd.getTables(null,null,"%",null);
|
Yes we understand and did that before we contacted you. Your example shows how to do it with JDBC but you are calling getTables() on what Excel file (database)? No where in your example do you describe how to connect to a data base without specifying the table ("database"."table").
jdbc:excel:/" or "jdbc:excel:///". Then you can use some full UNC path names in SQL to visit anywhere where your Java VM has right to access.
For instance:
select * from \\amd2500\e$\excelfiles\test;
elect * from "\\amd2500\d$\exceliles".test;
select * from ".".test;
IF we connect to DB and call getTables() on the connection it fails, naturally as expected since the driver does not know which database (Excel File to use).
The HXTT driver throws exceptions.. here is the code, here is exception..
09:25:05,966 ERROR [STDERR] java.lang.NullPointerException
09:25:05,967 ERROR [STDERR] at com.hxtt.sql.excel.v.a(Unknown Source)
09:25:05,967 ERROR [STDERR] at com.hxtt.sql.excel.v.a(Unknown Source)
09:25:05,967 ERROR [STDERR] at com.hxtt.sql.excel.v.a(Unknown Source)
09:25:05,967 ERROR [STDERR] at com.hxtt.sql.excel.v.if(Unknown Source)
09:25:05,967 ERROR [STDERR] at com.hxtt.sql.a.getTables(Unknown Source)
09:25:05,967 ERROR [STDERR] at com.logicbit.houdini.services.remote.req.db.HDXDBUtil.getTables(HDXDBUtil.java:1705)
HERE iS CODE... Connection is established. We can query Excel file but only as long as we provide table name. IF we try and get the tbales using getTables() it fails. How do we specify the database to use before we execute the following code?
ResultSet set = null;
try {
//String[] types = new String[2];
//types[0] = "TABLE";
//types[1] = "SYSTEM TABLE";
//Statement stmnt = this.conn.createStatement();
set = this.conn.getMetaData().getTables(null,null,"%",null);
if ( set==null) logger.log( "NFO", "HDX.HDXReqIFC.HDXDBUtil", "getTables() META is NULL", this.conn.getWarnings().toString() );
this.rslt = new ArrayList();
while( set.next() ) {
LinkedHashMap rec = new LinkedHashMap();
logger.log( "NFO", "HDX.HDXReqIFC.HDXRest", "TABLETABLE:::", set.getString(3) );
rec.put( "table", set.getString(3) );
this.rslt.add(rec);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if ( set!=null ) set.close();
set = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
|
1st solution
url = "jdbc:excel:/\\\\amd2500\\f$/excelfiles";
rs=dbmd.getTables(null,null,"%",null);
2nd solution
url = "jdbc:excel:///";
rs=dbmd.getTables("\\\\amd2500\\f$/excelfiles",null,"%",null);
>09:25:05,966 ERROR [STDERR] java.lang.NullPointerException
Thanks. It will disappear in the next package.
|
Main
webmaster@hxtt.com