v2.0.0

A batch is compiled before it runs, transactions and TRY/CATCH work, and
text an EXEC is given runs in a scope of its own.

pip install --upgrade pysqlbridge

BEGIN TRY
    BEGIN TRAN
    DECLARE @n int = (SELECT COUNT(*) FROM people)
    EXEC sp_executesql N'SELECT @n * 2 AS twice', N'@n int', @n = @n
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK
    THROW
END CATCH

A batch that will not compile is refused before any of it runs. A real
server compiles the whole batch first, so a syntax error in the fourth
statement means the first three never ran. This used to run each statement
as it reached it and report the failure as msg 50000, which meant a client
was shown work that a real server would have thrown away, under a number
that says nothing. The pre-pass carries a real server's numbers: 102 for
syntax, 105 for an unclosed quote, 113 for an unclosed comment, 156 for a
keyword where a name belongs, 137 for a variable nothing declared, 1087 for
a table variable, 141 for a SELECT that assigns and reads at once, 173 for a
column declared with no type. What is refused while compiling and what is
refused while running are two different lists, and both were measured.

Transactions, and the errors a batch survives. BEGIN TRAN, COMMIT,
ROLLBACK and SAVE TRAN work from T-SQL and from a client API's own
transaction request, which used to close the connection. TRY/CATCH runs, and
@@ERROR, ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY() and
ERROR_STATE() answer inside it; ERROR_LINE() and ERROR_PROCEDURE() are
NULL, because nothing here counts lines or runs in a procedure and a number
would be invented. THROW re-raises what a CATCH caught and raises what it is
given; RAISERROR
takes its format arguments, severity and state; SET XACT_ABORT ON ends the
batch where a real server ends it. Which errors end a batch and which it
carries on past is a measured list rather than a guess, and an error's
severity and state now reach the client instead of a constant 1.

Text an EXEC runs has its own scope. EXEC('...') and sp_executesql
used to run their text with the batch's own variables, which answered the
wrong thing quietly: an argument was evaluated with no variables at all, so
@x = @y passed NULL, and a value assigned inside leaked back out. The text
now gets a fresh scope holding only its declared parameters, each converted
to the type it was declared with, arguments may be positional or named,
OUTPUT copies back through the outer variable's type, and a table the text
made is dropped when the text ends. Eight message numbers came with it: 8178
for a declared parameter that was not supplied, 8144 for too many arguments,
214 for a statement that is not nvarchar, 119 for a positional argument
after a named one, 8162 for a parameter passed OUTPUT that was not declared
so, 179 for OUTPUT on a constant, 201 for sp_executesql called with nothing
at all, and 266 for text that leaves the transaction count where it did not
find it.

A variable holds the type it was declared with. DECLARE @x int = 5.7
holds 5, varchar(3) cuts what it is given to three characters, and a value
that will not convert is refused with the same number and words a cast gives
it. The declared type is applied on every assignment, not just the first.

Casts and conversions measured one at a time. A cast to nvarchar(max)
keeps all of what it is given rather than the 30 characters an unsized cast
takes. A cast to decimal or money rounds to its places, half away from
nought, and refuses a whole part that will not fit. A date keeps its day and
drops the rest. A bit is written as 1 or 0, and reads true and false back as
bits. A float written as text is six significant digits, scientific where
they will not reach, with a three-figure exponent: CAST(1.0e0 / 3 AS varchar(30)) is 0.333333, where this used to hand over all seventeen
digits Python holds. A number too long for the text it is cast to gets the
message a real server gives, which is a different one for each of four
cases.

Columns are declared the way a real server declares them. Nullability,
precision, radix and scale in INFORMATION_SCHEMA.COLUMNS and sys.columns,
and a written NULL is an int with no type of its own, so the other branch of
a UNION still decides.

A login may carry a username and a password. Windows Authentication
through SSPI still works; a client that sends a username and a password is
now admitted rather than refused.

Three comparisons instead of one. scripts/differential.ps1 answers one
query at a time, scripts/batches.ps1 runs whole batches statement by
statement, and scripts/truncations.ps1 sends every battery query cut short
at each word. Today: 1,168 queries identical with the same column types and
the same message numbers, 138 batches identical, and of 7,846 truncated
prefixes none is answered here that a real server refuses and none is
refused here that it answers. 135 differ by error number alone, 86 of them
one case, and the list of what is measured and not yet built is in the
README.

What changes for a query that used to work. Text a real server refuses
is now refused here too, before any of the batch runs, so a batch with a bad
statement at the end no longer returns the earlier ones. Error numbers,
severities and states are a real server's rather than msg 50000. A float
written as text is six digits rather than seventeen, and a bit written as
text is 1 rather than True. Nothing about reading a table changed.

A decimal is still held as a float, so 0.1 + 0.2 is 0.30000000000000004
here and 0.3 there. Why closing that means carrying declared types through
the whole expression tree is written up in the README.