SQLite Forum

vtable module - pass state from xColumn to callback
Login

vtable module - pass state from xColumn to callback

(1) By anonymous on 2021-08-20 13:59:35 [link] [source]

I'm implementing a vtable module.

In xColumn, I call sqlite3_result_* to produce the value, but I also have additional metadata about the value (say, a void*) that I need to make available to the callback passed to sqlite3_exec.

The problem is that the only state I have (4th arg to sqlite3_exec, then 1st arg to callback), is not available when xColumn is called. And whatever state I might have in xColumn is not available at callback time.

Is there a preferred way to make some state (e.g., the one I have at sqlite3_exec time) available at xColumn time?

(2.1) By David Jones (vman59) on 2021-08-20 18:10:58 edited from 2.0 in reply to 1 [link] [source]

For an xlsx virtual table module I wrote, it also defines an attr function to return metadata. e.g.:

CREATE VIRTUAL TABLE orders USING xlsxvtab('orders.xlsx','Sheet 1'); SELECT a,b,c,attr(c,'f') AS 'formula',attr(c,6) AS 'format' FROM orders;

The module generates an error if the first argument to attr() does not reference a cell in the virtual table. This is done by having a global attr() function defined at module load and a local scope attr() that overrides it for the table create.

(3) By David Jones (vman59) on 2021-08-21 00:16:30 in reply to 2.1 [link] [source]

Some more information: The xcolumn() method uses sqlite3_result_subtype() to tag the value with the column index (1-255) of the column being returned. The attr() function then uses sqlite3_value_subtype() to identify the column data for the current row and retrieve the its metadata according to the second argument.

The 8-bit limitation for the subtype code means it only works for virtual tables with less than 256 columns.

(4) By anonymous on 2021-08-21 21:54:10 in reply to 3 [source]

Sounds interesting, is it opensourced somewhere?

(5) By David Jones (vman59) on 2021-08-22 14:11:49 in reply to 4 [link] [source]

https://sourceforge.net/projects/vms-ports/files/SQLITE3/excel_convert_003c.zip/download

It was written in an OpenVMS/DECC environment, so I had to add a Makefile and a couple tweaks to let it build under Linux/gcc (gcc will still complain about a few "#pragma message" directives that mean something different in DECC).

(6) By anonymous on 2021-08-23 06:09:28 in reply to 3 [link] [source]

Thanks, that's clever, although insufficient for my case :(