NAME

DBIO::MSSQL::Storage::ODBC - Support for Microsoft SQL Server via DBD::ODBC

VERSION

version 0.900007

DESCRIPTION

Storage driver for Microsoft SQL Server accessed via DBD::ODBC (including FreeTDS-based ODBC connections). Inherits from both DBIO::Storage::DBI::ODBC and DBIO::MSSQL::Storage.

A schema that declares the driver -- the DBIO::MSSQL component or the -ms shortcut -- has its storage pinned to DBIO::MSSQL::Storage, which moves into this class at its first use when the DSN is a dbi:ODBC: one ("_adapt_to_access_path" in DBIO::MSSQL::Storage). That is decided from the DSN: no connection is opened for it and the server is not asked what it is.

A schema without a declared driver is refused at connect (core ADR 0067), and DBIO->driver_for throws for a dbi:ODBC: DSN: DBD::ODBC names no driver.

It carries the transport helpers from DBIO::Storage::DBI::ODBC (such as _using_freetds) on top of the transport-neutral MSSQL semantics of DBIO::MSSQL::Storage, without pulling in any DBD::Sybase-specific code.

The declared way is pinned offline in t/62-adapt-access-path.t (which class the storage becomes, and that nothing is reported) and live in t/72-adapt-access-path-odbc-live.t (DBD::ODBC 1.61 over the FreeTDS ODBC driver 1.3.17 against SQL Server 2022): the storage is DBIO::MSSQL::Storage before its first use and this class after it, over one connection, and no driver mismatch is reported.

BINARY VALUES

A value for a binary column -- binary, varbinary, image, and the names DBIO::MSSQL::DDL makes one of them (blob, bytea, ...) -- is bound as SQL_LONGVARBINARY. That is the one type that carries a value whole in every place a bind value stands in: INSERT, UPDATE and a condition, against VARBINARY(MAX), VARBINARY(n), BINARY(n) and IMAGE, from an empty value to 100000 bytes (measured with DBD::ODBC 1.61 over the FreeTDS ODBC driver 1.3.17 against SQL Server 2022). Bound without a type, as it was before, a binary value did not arrive at all: the driver takes it for text. Bound as SQL_VARBINARY or SQL_BINARY it is cut to 8000 bytes without an error.

  • The value is the bytes of the string, one per character, whatever form Perl holds the string in. DBD::ODBC would send a string with the UTF-8 flag on as the bytes of its UTF-8 form; the storage hands it the bytes instead.

  • A character above 255 is no byte. Such a value throws before anything is sent, naming the column and the position, not the value.

  • populate in void context stays the one prepared statement it is; the type is bound once and holds for every row.

  • Which bind value belongs to a binary column is what its data type says: a column of the result source, or the type given with a bind value in literal SQL (\[ '?', [ { sqlt_datatype => 'varbinary' } => $value ] ]). A value bound without a type is text to the driver, as any other.

Reading is not changed by any of this: "LONG VALUES".

STRINGS BEYOND THE CODE PAGE

Measured with DBD::ODBC 1.61 built without Unicode support (odbc_has_unicode is 0: a string goes to the driver as bytes, and a string with the UTF-8 flag on as its UTF-8 form) over the FreeTDS ODBC driver 1.3.17 against SQL Server 2022 (code page 1252), pinned by t/75-odbc-unicode-binds.t and, live, t/76-odbc-unicode-binds-live.t.

Bound without a type, as it was, a string is a CHAR to the driver and goes through the code page of the server whatever the column is: CJK and an emoji were refused by the driver ("Error converting characters into server's character set") even for an NVARCHAR column, which could have held them. A value for a column that is an NCHAR, NVARCHAR or NTEXT on the server (NVARCHAR(MAX) included) is therefore bound as SQL_WLONGVARCHAR. What the column is on the server is what DBIO::MSSQL::DDL deploys the declared type as (DBIO::MSSQL::Adapter is the one table): the declared nchar, nvarchar and ntext, but also text (an NVARCHAR(MAX)), varchar and string (NVARCHAR(n)) and char (NCHAR(n)). The server's own spelling stands: varchar(50), varchar(max) and char(10) are a real VARCHAR and CHAR. The value goes to the server as Unicode and arrives exact, from one character to 100000, in INSERT, UPDATE and conditions. SQL_WVARCHAR is cut at 4000 characters without an error from NVARCHAR(MAX) on, and SQL_WCHAR pads.

  • A real VARCHAR column (declared varchar(n), varchar(max), char(n)) keeps the driver's refusal. A Unicode bind into it would turn what the code page lacks into ? without a word. Text of the code page (an a-umlaut, the euro sign) is stored as its code page byte; CJK is refused, no row is written.

  • The client character set must be UTF-8. The driver sends the UTF-8 bytes of a string and the client library reads them in its ClientCharset: with ISO-8859-1 (or a C locale, where the client character set comes from the locale) an a-umlaut is stored as two other characters, silently, with or without a bind type. The storage therefore asks the connection once what it holds for an a-umlaut, when the connection is made (it also decides how text is read, below), and refuses a string beyond ASCII when it is not one, naming ClientCharset: write ClientCharset=UTF-8 into the DSN, or set a UTF-8 locale. ASCII is never refused. A DBD::ODBC built with Unicode support sends wide strings and has no such question; the probe is not made.

  • A string is its characters, whatever form Perl holds it in: a string with a character from U+0080 on is handed to the driver with the UTF-8 flag on (an a-umlaut made with "\x{E4}" has none, and the driver would take its one byte for a broken UTF-8 sequence and refuse it). Text that is already encoded as UTF-8 bytes must be decoded first, or it is encoded a second time. A binary column's value is bytes and is not touched ("BINARY VALUES").

  • Reading returns characters. A DBD::ODBC without Unicode support hands a character column back as the UTF-8 bytes of the value, without the UTF-8 flag (an a-umlaut two characters long, CJK three each): a silently wrong value for every caller that compares or prints it. Once the connection has said its client character set is UTF-8 (the question above), the storage sets odbc_utf8_on on the handle, on every connection, a reconnect too, and the driver decodes: the value is the string that was written, with the flag on. An odbc_utf8_on in the connect info is the caller's choice and stays (0 gives the bytes again). Not set when the character set is not UTF-8 (writing is refused there) and not for a DBD::ODBC with Unicode support, which decodes by itself. Binary columns are bytes and untouched. Before this the caller had to decode.

LONG VALUES

Measured 2026-10-17 with DBD::ODBC 1.61 over the FreeTDS ODBC driver 1.3.17 against SQL Server 2022 (t/77-long-values-live.t).

DBD::ODBC reads a LOB value -- TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), IMAGE, XML -- only up to LongReadLen, which is DBI's 80 unless the connect info says otherwise, and throws beyond it ("long truncated"). That was loud, but a schema with a LOB column was unreadable past 80 bytes. The measurement that decided the default:

  • A large LongReadLen cost nothing in the measurement. No memory or time showed for an empty or a short value at 50000000 and at 2147483647, 5 MB values were read whole, 20000 rows of TEXT no slower. So the storage sets LongReadLen to 1073741824 on every connection (a reconnect too) unless the connect info names one, which stays the regulator. Mind the price below: that measurement holds on a machine that overcommits memory.

  • The price of 1 GiB. DBD::ODBC 1.61 allocates, for every LOB column of every statement handle, a buffer of LongReadLen bytes when it binds the columns (dbdimp.c, bind_columns); in the Unicode build an NVARCHAR(MAX) or XML column twice that. The buffer is allocated, not touched, so it costs nothing as long as the operating system lets a process allocate more than there is: only thanks to overcommit. With vm.overcommit_memory=2, a ulimit -v, a container limit on address space or simply many LOB columns in one statement, the allocation fails with Out of memory!, which DBD::ODBC raises as a Perl panic and which cannot be caught with eval. Cached statement handles (prepare_cached, the storage's own statement cache) multiply it: every live handle holds its buffers. The maintainer's decision is that 1 GiB stays the default; where it does not fit, set LongReadLen in the connect attributes to more than the longest value (never to exactly that, see the next item).

  • The boundary of a small one is not loud. With LongReadLen N, a VARCHAR(MAX), TEXT or NTEXT value of N + 1 characters, and an NVARCHAR(MAX) value of N, comes back with its last character replaced by a NUL, without an error (N + 2 and N + 1 throw; binary types are exact at N and throw at N + 1). A default of 1073741824 puts that out of reach. A LongReadLen set by hand brings it back; it is held as a TODO test and an upstream draft (maint/upstream/dbd-odbc-longreadlen-boundary.md). Set it to more than the longest value, never to exactly that.

  • Beyond the limit it still throws, for every LOB type alike.

The FreeTDS class over DBD::Sybase has the opposite default and a price for it ("VALUES LONGER THAN TEXTSIZE" in DBIO::MSSQL::Storage::Sybase); the two paths agree on what matters: no value is cut without being said.

SQL_VARIANT is read (a binary base type as the hex digits of the value, as text; over DBD::Sybase it loses its row). The CLR types HIERARCHYID, GEOGRAPHY and GEOMETRY are refused by the driver.

SUPPORT MATRIX

DBD::ODBC is the supported access path; DBD::Sybase over FreeTDS is restricted ("SUPPORT MATRIX" in DBIO::MSSQL::Storage::Sybase, the same table).

Case                       DBD::ODBC (supported)         DBD::Sybase / FreeTDS (restricted)
-------------------------  ----------------------------  -------------------------------------
Bind of > 4000 bytes       bound, whole                  literal in the statement, whole
Bind beyond ASCII, into    exact: SQL_WLONGVARCHAR; the  REFUSED, naming DBD::ODBC
a column that is NCHAR,    ODBC client character set
NVARCHAR or NTEXT ON THE   must be UTF-8, else the
SERVER: declared nchar,    string is REFUSED. A
nvarchar, ntext -- and     string already encoded as
text, varchar, string,     UTF-8 bytes is encoded a
char, which DBIO deploys   second time, silently (it
as national                cannot be told from text)
Bind beyond ASCII, into    what the code page has is     REFUSED
a real VARCHAR/CHAR:       exact; what it lacks is
declared varchar(n),       refused by the driver
char(n), varchar(max)      (loud)
Empty string ''            exact                         literal ''; EXEC refused
Binary values, writing     SQL_LONGVARBINARY             hex pieces, 2099 placeholders per
                                                         statement; EXEC refused
Binary values, reading     bytes, LongReadLen 1 GiB      bytes through CAST AS IMAGE up to
                           unless set                    TEXTSIZE; a longer value REFUSED
TEXT, NTEXT, VARCHAR(MAX), whole by default; an          whole up to TEXTSIZE =
NVARCHAR(MAX) read         explicit small LongReadLen    LongReadLen (32768); a longer value
                           throws beyond it, but a       REFUSED, exactly. On the
                           value AT the boundary comes   handle / in literal SQL: cut
                           back with a NUL for its last  without an error (limit)
                           character (TODO, upstream)
Empty LOB value, memory    none                          TEXT/NTEXT/MAX types: none; binary
                                                         columns: LongReadLen bytes each
SQL_VARIANT column         read (a binary value as hex   REFUSED: rows with a binary
                           digits, as text)              base type are lost
IMAGE as hex digits above  n/a                           row lost, no error (limit)
TEXTSIZE/2; VARBINARY(MAX)
on dbh_do
HIERARCHYID, GEOGRAPHY,    refused by the driver         refused (ct_describe failed), loud
GEOMETRY
Transaction with an open   cursor before: commit and     BEGIN TRAN and COMMIT throw,
cursor                     rollback right, core warns    nothing is sent; ROLLBACK ends
                           that it abandoned the handle; the cursor and rolls back on
                           cursor inside: the next       the same connection (dbio-sybase t/61)
                           statement is refused (t/78)
Binds on dbh_do /          DBD binds as it is            not covered; the caller has the
on_connect_do                                            handle
Private subclass pinned    core warns                    refused
by storage_type
$rs->explain               refused: no plan over ODBC    plan (18 columns) for a query without
                           (the query would run)         placeholders; with them it dies
Key of a new row, with     SCOPE_IDENTITY()              SCOPE_IDENTITY(); @@identity
a trigger on the table                                   was the trigger's key (1000 for 1)
populate (void) with a     throws, nothing stays         same: stops at the first failing
row the server refuses                                   tuple
NoBindVars class,          n/a (binds)                   row by row in one transaction
populate, void context
NoBindVars class, pinned   n/a                           decision taken, layers kept
to ::Sybase with layers
NoBindVars class, ''       exact (0)                     core's literal is unquoted: Msg 102,
for an INT column                                        loud, no row (core's)

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.