I’ve written a virtual table to view spreadsheet data inside Excel workbook (.xlsx) files as SQlite tables. I want to implement an SQL function, attr(), which the virtual table overrides to give access to the raw attributes of the cell being returned. I’m looking for a robust way to find the actual column refered to by an overriden function, not just the value returned by the xColumn call.
sqlite> .load xlsxvtab.so # creates xlsxvtab module and attr() function
sqlite> create virtual table summary using xlsxvtab(‘expenses.xlsx’,’sheet1’,’F20’, ‘H32’);
sqlite> select F,G,H,attr(H,3) from summary; # show formula used to calculate column H.
The issue is that attr gets called with 2 values and I need to divine that the first value came from column H of the current row of the cursor opened by this select statement. The hack I’m using now is to give the values returned by the xColumn method a subtype equal to the column number. In the attr() function I retrieve the subtype and re-fetch that column from the current row of the last cursor open on that table.
Various pitfalls with this technique:
- Subtype numbers are limited to the range 0-255.
- Assumes all functions called before xNext() method called again.
- Complex queries with multiple cursors?
Jens Alfke asks:
>Maybe pass the column name as a string, i.e. `attr('H',3)`
2 problems with that idea. First, the first argument has to refer to a value in the virtual table in order to invoke the overridden version (overrides are per table, so I use the ppArg to bind function invocation to associated virtual table). Second, if I created a view that gives column H a name of Total, I’d want to the function call to be attr(Total,3). I don’t know how to make the function figure out that attr(‘Total’,3) really means column H in some table.
sqlite-users mailing list
[hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users