Linked table fetch size

[bocher]

Hi,

What  about adding to the create linked table  syntax optional arguments (as for CSVRead function) to tune the JDBC request ? I will be interested for example to set the fetch size of the resulset.

Best regards

Erwan

[bocher]

Dear all,

I reactivate this message. Just to find out if my question was not relevant

Best regards

[Noel Grandin]

Sounds possible and reasonable to me.

[bocher]

I have a doubt on how to define the fetch_size from the create linked table signature. What do you think about adding an option like this

CREATE LINKED TABLE LINK('org.h2.Driver', 'jdbc:h2:./test2',
    'sa', 'sa', 'TEST', parameters);

where parameters is  a string that contains the connection parameters as

CREATE LINKED TABLE LINK('org.h2.Driver', 'jdbc:h2:./test2',
    'sa', 'sa', 'TEST', 'FETCH_SIZE=1000; AUTO_COMMIT=FALSE');

Best regards

Erwan

[Evgenij Ryazanov]

On Thursday, 31 December 2020 at 21:52:10 UTC+8 bocher wrote:

I have a doubt on how to define the fetch_size from the create linked table signature. What do you think about adding an option like this

CREATE LINKED TABLE LINK('org.h2.Driver', 'jdbc:h2:./test2',
    'sa', 'sa', 'TEST', parameters);

I think it isn't possible without unwanted incompatible changes in syntax of CREATE LINKED TABLE command.

https://h2database.com/html/commands.html#create_linked_table

This command already accepts 5 or 6 parameters.

It would be better to specify the new additional parameters of Connection and Statement outside of these parentheses.

[bocher]

It would be better to specify the new additional parameters of Connection and Statement outside of these parentheses.

Do you have an idea in mind ?  I'd like to set the parameters from the SQL engine.

[Evgenij Ryazanov]

On Thursday, 31 December 2020 at 22:55:44 UTC+8 bocher wrote:

It would be better to specify the new additional parameters of Connection and Statement outside of these parentheses.

Do you have an idea in mind ?  I'd like to set the parameters from the SQL engine.

I think you can add some optional clauses at the end of this command, something like FETCH SIZE something.

I also don't understand why do you want to play with auto-commit in them. Linked tables in H2 didn't belong to H2's own sessions / transactions. They reside on the database level. H2 handles possible rollback in them by itself in its own sessions. Without auto-commit in the driver of the table you'll just break updatable linked tables, H2 needs to commit changes to them immediately. If you want to change it, most likely you will need to reimplement linked tables in H2, it's a large complicated work.

[bocher]

Hi,

auto-commit  was just for demonstration purpose. 

So you think about adding a term in the parser to write

CREATE LINKED TABLE LINK('org.h2.Driver', 'jdbc:h2:./test2',  'sa', 'sa', 'TEST') FETCH_SIZE 1000;

CREATE LINKED TABLE LINK('org.h2.Driver', 'jdbc:h2:./test2',  'sa', 'sa', 'TEST') EMIT UPDATES  FETCH_SIZE 1000;

CREATE LINKED TABLE LINK('org.h2.Driver', 'jdbc:h2:./test2',  'sa', 'sa', 'TEST') READONLY  FETCH_SIZE 1000;

Best regards

Erwan

[Evgenij Ryazanov]

Yes, if you don't have better ideas.

[bocher]

Sounds good for me. I will send a PR asap

[bocher]

Done https://github.com/h2database/h2database/pull/2995

Thanks a lot to Evgenij