Execute SQL script - bulk operations

[cyrixsimon]

Hi,

there should be a function that enables the execution of an entire SQL script. This would make it possible to perform mass operations. At the moment, each statement has to be executed individually, which results in very slow processing for a large SQL file. No bulk insertion or other bulk operations can be performed with acceptable performance.

The WebSocket specification provides a command that can be used for exactly this purpose.

=> https://github.com/exasol/websocket-api/blob/master/docs/commands/executeBatchV1.md

The command executeBatch will allow to send multiple SQL statements at once and execute them on the EXASOL database.

[bobozaur]

Hey,

This is unfortunately a bit more involved. The main problem is that executeBatch expects an array of SQL statements. This means the input must be split into individual statements, which comments and strings make a bit more complicated. Also, unlike the built-in sqlx drivers, this would not work for prepared statements. Those would have to be issued individually.

It's definitely doable by creating a minimal parser. This is in fact being used for migrations, though it's not parsing the input but rather doing an incremental attempt at splitting queries by ;.

To properly support this, the steps would be:

• Create a query splitter/parser that accounts for comments/strings.
• Use that for migrations
• Option 1: Repalce usage of execute with executeBatch in an attempt to better support multi-statement queries. This would have to error out if the query is accepting arguments and is thus a prepared statement.
• Option 2: Expose a driver specific execute_batch method and only use the query splitter here. This means that apart from this method, the driver still works with single statement queries.

At the moment I don't have the availability to work on this, but a contribution would be more than welcome! I could guide you through the process and review the code if you want to give it a go.