Unable to execute transactions in turso shell

[CodingDoug]

Issuing the SQL statement BEGIN on its own line causes an error:

error: Failed to execute SQL statement begin

The actual error is being masked due to #62:

{"error":"interactive transaction not allowed in HTTP queries"}

We need a way to easily work with transactions in the shell without having to issue each statement separately over HTTP. Perhaps there is a way to start a multi-line mode that collects statements to run together in a batch so that BEGIN/COMMIT can be useful. Separating statements with semicolons currently does not work due to https://github.com/libsql/sqld/issues/65.

[haaawk]

Separating statements with semicolons works now:

╰──> ./turso db create t                                                                                                                                                                                        
Created database t to Warsaw, Poland (waw) in 13 seconds.

You can start an interactive SQL shell with:

   turso db shell t

To obtain connection URL, run:

   turso db show --url t

╰──> ./turso db shell t
Connected to t at https://haaawk:9B5I8z3L1NV64x2g@t-haaawk.turso.io

Welcome to Turso SQL shell!

Type ".quit" to exit the shell, ".tables" to list all tables, and ".schema" to show table schemas.

→  create table test(a int);

→  begin;insert into test values(1);insert into test values(2);commit

→  select * from test;
A  
1  
2 

Is it good enough to close this issue @CodingDoug or would you like something more?

[haaawk]

Closing... @CodingDoug please reopen if you think this issue needs more work

[CodingDoug]

While transactions in the shell now work with semicolons, here's the experience now without knowing that ahead of time with a reasonable attempt:

→  begin
Error: Failed to execute SQL statement: begin
interactive transaction not allowed in HTTP queries

The error message is a bit confusing because the user may not understand that 1) it's using HTTP behind the scenes, 2) why we disallow this over HTTP, 3) no advice on how to move forward.

Ideally, a reasonable attempt like this should be followed up with actionable advice. If we can detect this situation and provide an actionable error message, e.g.

Transactions are only supported in the shell using semicolons to separate each statement. For example: "BEGIN; [your SQL statements]; END"

That would give them the supported way to move forward (until we have our improved shell).

The only problem here is that we don't have an identifying error code, so the shell would have to parse the error string.