RFE: Add sheet as TABLE object and support for SELECT * FROM <TABLE> to enable reading XLSX from H2 as CREATE LINKED TABLE

[t603]

Hello, Volker,

once XLSX is readable from SQuirreL SQL Client 4.8.0 now after Your latest patches, I am able to get data from XLSX to e. g. H2 database now via export to CSV and import from CSV in SQuirreL using:

SQuirreL's native macro command:
@file 'C:\temp\temp\testOut_Sheet1.csv' {call Sheet1};

and

H2 native function CSVREAD:
select * from csvread('C:\temp\temp\testOut_Sheet1.csv', 'id;name', 'charset=UTF-8 fieldSeparator=;');

But It would be great to add feature to ExcelJConnect, which enables to read data from XLSX directly. I guess, that it means, that ExcelJConnect have to:

1. propagate XSLX sheets as tables, not only procedures
2. return sheet data (ResultSet) as the result of command "SELECT * FROM "

Because I guess, that once H2 will be able to read data as the result of "select * from " into its own table (in-memory one, for example), than H2 will provide its full and rich database functionality to client. AFAIK commercial drivers make this the same way, it means that they utilise (use inside them) H2, HSQLDB or any other pure Java database engine and these JDBC drivers just provide their "reading or writing XLSX capabilities" to such database engines.

I already tried to read data into H2 database (inside SQuirreL SQL Client) in in-memory mode, but with no luck, just because missing "select * from " capability of ExcelJConnect.

Here is setting of Driver and Alias of SQuirreL, which combines H2 with ExcelJDriver:

Here is couple of testing SQL commands, the same as I use for data exchange with other database JDBC driver using native H2 CREATE LINKED TABLE command:

/* 
  2024-11-12a 
  working with XLSX in SQuirreL SQL Client 4.8.0 via H2 + ExcelJConnect 
  H2 2.3.232 (2024-08-11) jar 
    from "./h2/bin/h2-2.3.232.jar" 
    available on "https://github.com/h2database/h2database/releases/download/version-2.3.232/h2-2024-08-11.zip" 
  ExcelJConnect-main-baf7659af0-1.jar 
*/ 

drop table if exists "Sheet1"; 
create linked table "Sheet1" ('com.inet.excel.ExcelDriver', 'jdbc:inetexcel:C:\temp\temp\testIn.xlsx?hasHeaderRow=false', null, null, 'select * from Sheet1'); 
--Error: Unsupported SQL Syntax. Only {call sheetname()} or {call sheetname} are supported: SELECT * FROM select * from Sheet1 T WHERE 1=0 

drop table if exists "Sheet1"; 
create linked table "Sheet1" ('com.inet.excel.ExcelDriver', 'jdbc:inetexcel:C:\temp\temp\testIn.xlsx?hasHeaderRow=false', null, null, 'select * from {call Sheet1}'); 
--Error: Unsupported SQL Syntax. Only {call sheetname()} or {call sheetname} are supported: SELECT * FROM select * from {call Sheet1} T WHERE 1=0 

drop table if exists "Sheet1"; 
create linked table "Sheet1" ('com.inet.excel.ExcelDriver', 'jdbc:inetexcel:C:\temp\temp\testIn.xlsx?hasHeaderRow=false', null, null, '{call Sheet1}'); 
--Error: Unsupported SQL Syntax. Only {call sheetname()} or {call sheetname} are supported: SELECT * FROM {call Sheet1} T WHERE 1=0  

drop table if exists "Sheet1"; 
create linked table "Sheet1" ('com.inet.excel.ExcelDriver', 'jdbc:inetexcel:C:\temp\temp\testIn.xlsx?hasHeaderRow=false', null, null, 'Sheet1'); 
--Error: Unsupported SQL Syntax. Only {call sheetname()} or {call sheetname} are supported: SELECT * FROM Sheet1 T WHERE 1=0

So, if You think, that such RFE would be feasible, I guess, that it will help me and other XLSX readers a lot. Thank You in advance, Stepan