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
VARCHARcolumn (declaredvarchar(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: withISO-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, namingClientCharset: writeClientCharset=UTF-8into 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+0080on 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_onon the handle, on every connection, a reconnect too, and the driver decodes: the value is the string that was written, with the flag on. Anodbc_utf8_onin the connect info is the caller's choice and stays (0gives 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
LongReadLencost 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 ofTEXTno slower. So the storage setsLongReadLento 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
LongReadLenbytes when it binds the columns (dbdimp.c,bind_columns); in the Unicode build anNVARCHAR(MAX)orXMLcolumn 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. Withvm.overcommit_memory=2, aulimit -v, a container limit on address space or simply many LOB columns in one statement, the allocation fails withOut of memory!, which DBD::ODBC raises as a Perl panic and which cannot be caught witheval. 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, setLongReadLenin 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
LongReadLenN, aVARCHAR(MAX),TEXTorNTEXTvalue of N + 1 characters, and anNVARCHAR(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. ALongReadLenset 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
DBIO::MSSQL - MSSQL schema component
DBIO::MSSQL::Storage - MSSQL storage base class
DBIO::MSSQL::Storage::Sybase - MSSQL via DBD::Sybase
DBIO::Storage::DBI::ODBC - Base class for ODBC drivers
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.