NAME

DBIO::MSSQL::Storage - Base Class for Microsoft SQL Server support in DBIO

VERSION

version 0.900007

DESCRIPTION

Storage driver for Microsoft SQL Server databases. Handles identity column (autoincrement) retrieval via SCOPE_IDENTITY(), proper MONEY column casting on insert/update, ordered subselect safety checks, savepoint support, deferred foreign key checks via sp_msforeachtable, and datetime parsing.

Limited queries always use the ROW_NUMBER() OVER() dialect via DBIO::MSSQL::SQLMaker; there is no version-dependent TOP path.

This is the class a schema declares -- the DBIO::MSSQL component and the -ms shortcut pin it -- and it is transport-neutral: everything here holds whichever DBD carries the connection. A connected storage is one of its two subclasses, DBIO::MSSQL::Storage::ODBC or DBIO::MSSQL::Storage::Sybase; a declared storage moves there itself at its first use ("_adapt_to_access_path").

METHODS

_dbh_execute_for_fetch

The executing half of core's void-context populate. The rows are executed with execute_for_fetch, which DBI runs to the end of the data whatever became of the tuple before. SQL Server rolls the whole transaction back on some statement errors (Msg 8114, a bound value that cannot be converted), so the tuples after the failing one ran in autocommit and stayed, while the ones before it were gone: populate threw and left part of its data. Core is given a handle that stops at the first failing tuple (DBIO::MSSQL::Storage::StopAtFirstError); nothing of the data set stays.

last_insert_id

Returns the last identity value inserted, as retrieved by SCOPE_IDENTITY() or the fallback _identity_method.

sqlt_type

Returns SQLServer, identifying this storage to SQL::Translator.

_connect_params_dsn

The DSN seam of "_connect_params_dsn" in DBIO::Storage::DBI (ADR 0057), overridden because core's default form puts the name the storage class is registered for into the DSN, and this class is registered for MSSQL -- a registry name, there is no DBD::MSSQL. The DSN built here names DBD::Sybase, whatever name the seam is handed: of the two DBDs that reach SQL Server it is the one whose DSN keys dbname, host and port can fill. DBD::ODBC needs a data source name or the name of an ODBC driver, which connection parameters do not carry.

dbi:Sybase:host=<host>;port=<port>;database=<dbname>

host and port go in as a pair, dbname as database= (dbname= is no DBD::Sybase key), and a part not given is left out -- nothing is put into the DSN that the parameters do not say:

  • without dbname there is no database=, and the server puts the session into the login's default database;

  • without host and port the DSN names no server, and DBD::Sybase connects to its default one: $DSQUERY, else the server called SYBASE in freetds.conf or the interfaces file.

Two combinations refuse, each error naming the ways out:

  • host without port. DBD::Sybase documents the two only as a pair and uses them only when both are set; a lone host is ignored and the connection goes to the default server, silently. No port is assumed: 1433 is where a default instance usually listens, a named instance listens elsewhere, and neither DBD::Sybase nor its documentation supplies one.

  • port without host. Ignored the same way; the port given would be dropped.

Anything these three parts cannot say goes in as the whole DSN: as dsn, or as a dbname that starts with dbi: (DBIO::AccessBroker::Static passes it on), which core takes as the DSN before this seam is asked. That is the only way in for DBD::ODBC -- DBIO::MSSQL::Storage::ODBC inherits DBIO::Storage::DBI::ODBC ahead of this class and so keeps core's refusal, deliberately: an ODBC storage must not reconnect through another DBD -- and for a named server over DBD::Sybase (dbi:Sybase:server=<name>;...).

Three things this DSN does not do:

  • It names no server. Over FreeTDS a second statement active at the same time makes DBD::Sybase open a second connection. With host=...;port=... that connection is made, but the client library leaves its message 44 ("Server name not found in configuration files") on the handle, and the statement fails with it; a named [server] block in freetds.conf has a name to resolve. Use the server= form for that.

  • It decides the DBD, and it is asked before the storage has adapted to one. The seam is asked when the schema connects, on this class; the storage becomes the class for its access path only at the first use ("_adapt_to_access_path"), and by then the DSN built here names DBD::Sybase. So connection parameters always lead to DBIO::MSSQL::Storage::Sybase, never to the ODBC class -- which would refuse them. Every later connection asks the seam of the class the storage became: the same form over DBD::Sybase, a refusal over DBD::ODBC (t/62-adapt-access-path.t).

  • It is not verified beyond one client library. The three forms were run through this seam against SQL Server 2022 with DBD::Sybase 1.27 over FreeTDS 1.3.17 (t/60-connect-params-live.t): host=...;port=...;database=... connects into the database named, host=...;port=... into the login's default database, and database=... alone connects once $DSQUERY names a server FreeTDS can resolve. Sybase's own client library was not tried.

_bind_host_into_dsn

The HostBound seam of "_bind_host_into_dsn" in DBIO::Storage::DBI: how a DBIO::AccessBroker::HostBound view -- a host of its own per replicant, one set of credentials -- puts its host (and port) into a DSN. It stands on this base class, so it is reached on the declared class (asked before the storage has adapted to an access path), on DBIO::MSSQL::Storage::ODBC and on DBIO::MSSQL::Storage::Sybase alike, and answers by the DBD the DSN names.

DBD::ODBC (dbi:ODBC:) names the server in a Server key:

  • Server=host,port (the Microsoft form): the host and the port are replaced where they stand.

  • Server=host;Port=n (the FreeTDS form): Server and Port are replaced where they stand.

A view without a port keeps the port the answer had. Every other part of the DSN, the case of the keys, braces such as Driver={ODBC Driver 18 for SQL Server} and the dbi:ODBC(attr): prefix stay as they are.

It is declined -- the view refuses the connection, naming the form and never a value of the DSN -- for a DSN that holds no host to replace or names the server so that replacing it would change the meaning: a data source (DSN=, FILEDSN=, a bare name), Servername= (an entry of freetds.conf), Address=/Addr=, a Server with an instance (host\instance), a protocol or IPv6 colon (tcp:host), or a braced value, both a comma port and a Port key, a key given twice, no Server at all, and a view port where the DSN shows no port form to put it in (Server=host alone): which of the two forms the driver reads is the DSN's to say, and the seam does not guess.

DBD::Sybase (dbi:Sybase:): core's default -- host= and port= are bound -- and the refusal of server=NAME, a named server of freetds.conf/interfaces that holds no host. Any other DBD gets core's answer too.

_adapt_to_access_path

The hook of "_adapt_to_access_path" in DBIO::Storage::DBI (ADR 0059). A schema declares this driver by loading the DBIO::MSSQL component or by the -ms shortcut, and both pin this class -- the transport-neutral base (ADR 0035), which fits neither DBD that reaches SQL Server as it stands. Core calls the hook at the first use that needs the driver, with the name of the DBD the connect info leads through, and the storage moves into this driver's class for it:

  • ODBC -- DBIO::MSSQL::Storage::ODBC. Decided from the DSN, without a connection.

  • Sybase -- DBIO::MSSQL::Storage::Sybase, which then decides whether the connection takes placeholders and, if it does not, moves on to DBIO::MSSQL::Storage::Sybase::NoBindVars. It asks the client library through the handle: on this access path the first use connects. DBIO::Sybase::Storage then mixes its FreeTDS class in.

    DBIO::Sybase is an optional dependency of this distribution. Without it a dbi:Sybase: DSN throws here, naming it, and the storage stays undetermined; no other class of this driver works on a DBD::Sybase handle.

Any other DBD leaves the storage as it is. If that DBD belongs to another driver, core reports it ("_driver_mismatch" in DBIO::Storage::DBI); a DBD nothing is registered for is not judged.

A connection coderef names no DBD until it has run, so core hands the hook none. The hook then runs it -- one connection, the one the storage goes on using -- and reads the DBD off the handle. Connect info without a DSN, as on a schema that only generates SQL, is left alone.

Three things the hook does not do:

  • It does not ask the server what it is. A schema that declared this driver has answered that question. A declared storage never leaves this driver. Nothing this driver reads anyway tells it the product, so a server that is not SQL Server is not reported by it.

  • It does not move a class that is not this one. A subclass of this class pinned by hand through "storage_type" in DBIO::Schema stays what it is -- core requires the storage to remain an instance of the class that was declared, and neither access-path class inherits a private one. Over DBD::ODBC core then reports the DBD as not belonging to that class, which is true: derive from DBIO::MSSQL::Storage::ODBC instead. A storage pinned to one of the two access-path classes is already on its access path.

  • It refuses a private subclass over DBD::Sybase. On a DBD::Sybase handle such a subclass throws at the first use, naming DBIO::MSSQL::Storage::Sybase as the class to derive from and DBD::ODBC as the other way (ADR 0008) -- whatever driver_strict and driver_warnings say. A storage pinned to DBIO::MSSQL::Storage::ODBC (or a class derived from it) over DBD::Sybase is refused with a message of its own: the class and the DSN contradict each other. The base is transport-neutral and has none of what the Sybase class does for this DBD: the transaction statements (COMMIT and ROLLBACK are refused by the client library with results pending, and txn_do runs its body a second time on a new connection; an empty transaction can be neither committed nor rolled back), the 4000 byte limit of bind values (the server cuts a longer one without an error, "VALUES OF MORE THAN 4000 BYTES" in DBIO::MSSQL::Storage::Sybase), and binary values in both directions. This departs from ADR 0059 of core, where a driver mismatch warns and only driver_strict makes it an error: here it is an error for this one pairing. A layer class core composes over the declared base is not a private subclass.

  • It does not choose the DBD. Connection parameters without a DSN have been turned into one by "_connect_params_dsn" when the schema connected -- on this class, before the hook runs -- and that DSN names DBD::Sybase. The hook follows it.

What the DBD::Sybase class brings includes the FreeTDS adaptations. One of them is to be known: over FreeTDS SET TEXTSIZE is set to LongReadLen at connect ("set_textsize" in DBIO::Sybase::Storage::FreeTDS, 32768 unless the connect info says otherwise), so a longer value of a LOB column is not delivered cut: a column of a result source that is a (N)VARCHAR(MAX), an NTEXT or a binary type is read behind a check the server runs, and a value over TEXTSIZE bytes fails the read naming the column (the handle itself, literal SQL and expressions are not covered and still cut without an error). Raise LongReadLen in the connect info for longer ones; an NVARCHAR(MAX), which is what a text column is, holds two bytes per character. See "VALUES LONGER THAN TEXTSIZE" in DBIO::MSSQL::Storage::Sybase.

_mask_error_values

my $text = $storage->_mask_error_values($text, \@values, $statement);

The seam of core (ADR 0075) through which the text of an engine passes before it is thrown. SQL Server writes the offending value into its own sentences (Conversion failed when converting the varchar value 'x' to data type int., The duplicate key value is (x)., Truncated value: 'x'., Incorrect syntax near 'x'.), also when the value was a bind, and a short one (7, ab) is not found by looking for the bound values alone. So the sentences are masked first, by "mask_error_details" in DBIO::MSSQL::Util with the bound values, then core's implementation (next::method) masks the values the handle quotes. The base class carries it, so DBIO::MSSQL::Storage::ODBC and DBIO::MSSQL::Storage::Sybase (and the NoBindVars classes, whose statement holds the values) have it too. Only a defined text that is no reference is looked at; $dbh->errstr stays as the DBD has it.

SQL Server reports in the language of the session (SET LANGUAGE, the login's or the server's default), and the words around a value change with it. The masking therefore goes by what does not: the number of the message (Server message number=N in the text of DBD::Sybase; over DBD::ODBC the numbers the handle reported, see DBIO::MSSQL::Storage::ODBC) and the marks the server quotes with. See "mask_error_details" in DBIO::MSSQL::Util.

with_deferred_fk_checks

$storage->with_deferred_fk_checks(sub { ... });

Runs the supplied coderef with all foreign key constraints disabled via sp_msforeachtable. Constraints are re-enabled and explicitly verified afterwards, and the transaction is committed.

ORDERED SUBSELECTS

SQL Server rejects ORDER BY in a subquery unless it carries a TOP, and TOP 100 PERCENT does not help. An ordered subselect therefore throws an exception unless the unsafe_subselect_ok attribute is set; with it, the subquery is rewritten to (SELECT TOP <max_int> ..., where max_int is 0x7FFFFFFF.

EXISTS QUERIES

This storage declares the supports_exists_query capability (ADR 0037), so "results_exist" in DBIO::ResultSet, "results_exist_async" in DBIO::ResultSet and "results_exist_as_query" in DBIO::ResultSet are available on SQL Server. It does not override "_exists_value_sql" in DBIO::Storage::DBI: core's base rendering, SELECT CASE WHEN EXISTS <inner> THEN 1 ELSE 0 END, is grammatical Transact-SQL as written -- a FROM-less SELECT is legal here, and CASE WHEN EXISTS (...) is Microsoft's own documented idiom. What T-SQL does not have is EXISTS as a bare value expression, which is what core emitted before ADR 0037.

One SQL Server restriction reaches this call: an existence check on a resultset carrying an order_by is refused by this driver's ordered-subselect guard before any EXISTS SQL is built, because Transact-SQL forbids ORDER BY inside a subquery without TOP / OFFSET / FOR XML. An ordering has no meaning for an existence check anyway -- drop it, or ask on the unordered resultset.

SET OPERATIONS

Of DBIO::ResultSet's six set operations, SQL Server has four: union, union_all, intersect and except, all spelled the ANSI way and inherited from core unchanged.

intersect_all and except_all throw (ADR 0039). Transact-SQL has no INTERSECT ALL and no EXCEPT ALL: EXCEPT and INTERSECT always return distinct rows, and unlike UNION they take no ALL. The refusal is unconditional and version-independent, because the gap is -- the grammar has read { EXCEPT | INTERSECT } in every version Microsoft documents.

The keyword is resolved before any operand SQL is assembled, so a refused operation throws while the query is being built and never yields a fragment that could be embedded in a larger query. $rs->except and $rs->intersect are the nearest available operations, but they are not substitutes: they collapse duplicate rows, which is a different question.

QUERY PLANS

"explain" in DBIO::ResultSet asks the storage for the whole operation of obtaining a query plan, through "_explain_query" in DBIO::Storage::DBI (ADR 0040, which replaced the string-valued _explain_sql). SQL Server is one of the two engines that argument was made for, and this class overrides the hook because core's inherited operation cannot work here at all: it is EXPLAIN <query>, and Transact-SQL has no EXPLAIN statement. There is nothing for the server to parse.

What SQL Server has instead is a session setting, which is why no rewriting of a single statement string could have expressed this. Obtaining a plan is three round-trips with session state left behind between them:

SET SHOWPLAN_ALL ON     -- must be the only statement in its batch
<the query>             -- NOT executed: it answers with the plan
SET SHOWPLAN_ALL OFF

The middle statement is the explained query itself, unchanged and carrying its own bind values -- it is still parsed, so its placeholders are still its own. While the setting is on the server "returns execution information for each statement without executing it", so what comes back is the plan rather than the query's result.

All three statements go through "_execute_rendered" in DBIO::Storage::DBI, which the contract requires without engine-named exceptions. That is what keeps tracing and therefore "redact_bind_value" in DBIO::Storage::DBI (ADR 0025) over the explained query's own bind values, dbh_do retry over a dropped connection, DBIO::Storage::DBI::NoBindVars interpolation, and -- on a DBIO::Replicated::Storage -- the read handler rather than the master's. Because core delegates the whole operation rather than the statements inside it, all three land on the same connection, which a session setting requires.

Turning it back off is guarded, not merely last

SET SHOWPLAN_ALL OFF runs from a Scope::Guard, so it also runs when the explained query throws. This is the failure that matters here: an unguarded OFF is skipped on an exception and the session stays poisoned. SHOWPLAN_ALL remains on for the life of that connection, and every subsequent query on it returns a plan instead of its rows -- well-formed rowsets, no error anywhere, outliving the call for as long as the handle is pooled. That is a worse defect than the missing EXPLAIN this override exists to fix, and it is silent where the original is loud. The guard does not touch the exception in flight: the error from the explained query is what propagates.

The guard is created after the ON succeeded, deliberately. A SET that failed was never applied, and turning off a setting the session never got would replace the real error with a second one.

The plan is fetched before the guard fires, which is also deliberate: the OFF is a statement, and issuing one with an unfetched rowset still pending is exactly what desynchronises a DBD::Sybase connection.

Why SHOWPLAN_ALL and not SHOWPLAN_XML

Both settings suppress execution and return a plan, and either would be legal in this hook, so the choice is argued rather than inherited from core naming SHOWPLAN_ALL in its POD.

SET SHOWPLAN_ALL ON answers with a rowset: a hierarchical tree of plan rows over eighteen named columns (StmtText, PhysicalOp, LogicalOp, Argument, EstimateRows, TotalSubtreeCost, ...), documented as "intended to be used by applications written to handle its output" -- which is what a driver is. That shape maps onto _explain_query's arrayref of row arrayrefs as it stands, with nothing invented and nothing flattened.

SET SHOWPLAN_XML ON answers with one XML document per batch, so through this hook it would arrive as a single row of a single column holding one opaque string. ADR 0040 section 6 permits that shape -- it is what Firebird's plan does -- but here it would be a wrapping chosen over a fit that already exists. Its advantages are real and do not apply to this call: it is the form the tooling ecosystem consumes, and it reaches platforms SHOWPLAN_ALL is not documented for (Azure Synapse dedicated SQL pools, Fabric warehouses). A caller who wants the XML wants to feed it to a plan viewer, which is native escape hatch territory (core ADR 0017) rather than $rs->explain.

SET SHOWPLAN_TEXT is not a candidate: Microsoft describes it as the readable form for command-prompt tools such as osql, and it carries one column where SHOWPLAN_ALL carries eighteen.

What of this is verified

The seam is verified against core's source (DBIO 0.900004 as installed): the hook name and signature, that _explain_sql is gone rather than deprecated, and that "explain" in DBIO::ResultSet strips as_query's parentheses before the hook sees the statement.

The Transact-SQL facts come from Microsoft's own SET SHOWPLAN_ALL and SET SHOWPLAN_XML reference pages: that the statements are not executed while the setting is on, that the setting must be the only statement in its batch and cannot appear inside a stored procedure, and the column list above.

Over DBD::ODBC explain is refused. Run live on 2026-10-19 (SQL Server 2022, DBD::ODBC 1.61 over the FreeTDS ODBC driver), the three round-trips below do not give a plan there: the prepared SET SHOWPLAN_ALL ON does not outlive its prepare, so the explained query runs, and its own rows would come back looking like a plan -- well formed, no error, wrong. DBIO::MSSQL::Storage::ODBC overrides _explain_query and throws ("explain is refused ... no plan over ODBC") before sending anything; the way to a plan is DBD::Sybase (DBIO::MSSQL::Storage::Sybase), where a query without placeholders returns the 18-column plan and one with placeholders dies in execute. t/56-explain-query.t pins the refusal and, offline, what the Sybase path puts on the wire, in what order, that every statement goes through the storage's statement path, and that the OFF is issued even when the explained query throws. Its live half (the ODBC refusal, the Sybase plan of 18 columns) is written and has not been run yet.

Two things in the Sybase path are not measured beyond that:

  • the batch rule versus how the DBD prepares. Each call to _execute_rendered is its own prepare/execute, so each statement is its own batch -- unless the driver wraps it, as DBD::ODBC evidently does for the prepared SET above.

  • how many result sets come back. Because the query does not execute, the plan should be the first and only rowset, which is why this method fetches once and does not loop over $sth->more_results. That follows from the documented semantics rather than from an observation, and it is transport-sensitive: DBD::Sybase in particular treats a left-over result set as a desynchronised connection.

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.