Allow running multiple statements in single cell

[emredjan]

While most basic SQL operations can be distilled to a single statement (or can be divided into multiple cells), there are times when more than one statement needs to be run in a single batch. Most obvious example is when using variables:

DECLARE @report_date DATE = '2021-02-28'

SELECT @report_date

This statement returns no output when using xeus-sql, presumably the execution ends after running the first declare statement. Splitting it into two cells don't work either as the SELECT statement fails unless the variable is declared in the same batch. The database is SQL Server and connected via Microsoft ODBC driver, but the same query runs and returns a correct result using ipython-sql or pyodbc / sqlalchemy, so the issue seems to be either with the SOCI backend, or xeus-sql implementation.

Similarly, the following query doesn't execute the INSERT part, only runs the CREATE TABLE part when run in a single cell with xeus-sql (Though executing these in two separate cells works, as they don't need to part of the same batch)

CREATE TABLE table1
(
    column1 NVARCHAR(100) NOT NULL,
    column2 FLOAT
)

INSERT INTO table1
VALUES
    ('DUMMY', 0)

[marimeireles]

Hey @emredjan, thanks for opening the issue!
Addressing your second issue:
xeus-sql depends on the chain of the sql command being valid for it to run. The only distinction we have on the xeus-sql side for sql code is if it has a rich display output (tables and graphs) or not. So, I'm not very knowledgeable in sql sorry, but I'd say this is not a valid command to put up in a chain and that's why it's not working. I don't think there's a super easy way to fix this since it's coming from upstream SOCI. Unless there's only a few use cases where something like this happens, then we could maybe create an if and treat it differently.
Regarding your first issue... I think it falls into the same category of it's not a "chainable" command and that's why it doesn't run. But since this use case doesn't work at all, it'd be good if we could find a solution, but none of the things I'm thinking now are simple to do.
Maybe we could have a kind of cache tool... to store variable values. But I think the retrieving system for these would be very complicated.
Maybe the best solution would be transforming everything that stores values in to magic commands? The CRTP syntax parser logic for the xvega stuff could be easily reused for this. I can expand on this if folks are interested in contributing, in the moment I can't really spend so much time on this project.
Other than that, I really don't know how this could be fixed.
Everyone is welcome to chime in! With ideas or code! I will gladly review PRs and help as much as I can :)