1 package DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server;
7 DBIx::Class::Storage::DBI::ADO
8 DBIx::Class::Storage::DBI::MSSQL
12 use DBIx::Class::Carp;
13 use DBIx::Class::Storage::DBI::ADO::CursorUtils qw/_normalize_guids _strip_trailing_binary_nulls/;
14 use DBIx::Class::ResultSource::FromSpec::Util 'fromspec_columns_info';
17 __PACKAGE__->cursor_class(
18 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor'
21 __PACKAGE__->datetime_parser_type (
22 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::DateTime::Format'
25 __PACKAGE__->new_guid(sub {
27 my $guid = $self->_get_dbh->selectrow_array('SELECT NEWID()');
28 $guid =~ s/\A \{ (.+) \} \z/$1/xs;
34 DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server - Support for Microsoft
35 SQL Server via DBD::ADO
39 This subclass supports MSSQL server connections via L<DBD::ADO>.
43 The MSSQL specific functionality is provided by
44 L<DBIx::Class::Storage::DBI::MSSQL>.
48 dbi:ADO:provider=sqlncli10;server=EEEBOX\SQLEXPRESS
54 C<_identity_method> is set to C<@@identity>, as C<SCOPE_IDENTITY()> doesn't work
55 with L<DBD::ADO>. See L<DBIx::Class::Storage::DBI::MSSQL/IMPLEMENTATION NOTES>
56 for caveats regarding this.
60 There is a bug with MSSQL ADO providers where data gets truncated based on the
61 size of the bind sizes in the first prepare call:
63 L<https://rt.cpan.org/Ticket/Display.html?id=52048>
65 The C<ado_size> workaround is used (see L<DBD::ADO/ADO providers>) with the
66 approximate maximum size of the data_type of the bound column, or 8000 (maximum
67 VARCHAR size) if the data_type is not available.
69 Please report problems with this driver and send patches.
73 C<LongReadLen> is set to C<LongReadLen * 2 + 1> on connection as it is necessary
74 for some LOB types. Be aware of this if you localize this value on the C<$dbh>
79 Due perhaps to the ado_size workaround we use, and/or other reasons, binary data
80 such as C<varbinary> column data comes back padded with trailing C<NULL> chars.
81 The Cursor class for this driver
82 (L<DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor>) removes them,
83 of course if your binary data is actually C<NULL> padded that may be an issue to
84 keep in mind when using this driver.
86 =head2 uniqueidentifier columns
88 uniqueidentifier columns come back from ADO wrapped in braces and must be
89 submitted to the MSSQL ADO driver wrapped in braces. We take care of this
90 transparently in this driver and the associated Cursor class
91 (L<DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor>) so that you
92 don't have to use braces in most cases (except in literal SQL, in those cases
93 you will have to add the braces yourself.)
95 =head2 fractional seconds
97 Fractional seconds with L<DBIx::Class::InflateColumn::DateTime> are not
98 currently supported, datetimes are truncated at the second.
105 # SCOPE_IDENTITY() doesn't work
106 $self->_identity_method('@@identity');
107 $self->_no_scope_identity_query(1);
109 return $self->next::method(@_);
112 sub _run_connection_actions {
115 # make transactions work
116 require DBD::ADO::Const;
117 $self->_dbh->{ado_conn}{CursorLocation} =
118 DBD::ADO::Const->Enums->{CursorLocationEnum}{adUseClient};
120 # set LongReadLen = LongReadLen * 2 + 1
121 # this may need to be in ADO.pm, being conservative for now...
122 my $long_read_len = $self->_dbh->{LongReadLen};
124 # This is the DBD::ADO default.
125 if ($long_read_len != 2147483647) {
126 $self->_dbh->{LongReadLen} = $long_read_len * 2 + 1;
129 return $self->next::method(@_);
133 # Fix up binary data and GUIDs for ->find, for cursors see the cursor_class
137 my ($ident, $select) = @_;
139 my @row = $self->next::method(@_);
141 return @row unless $self->cursor_class->isa(
142 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor'
145 my $col_infos = fromspec_columns_info($ident);
147 _normalize_guids($select, $col_infos, \@row, $self);
149 _strip_trailing_binary_nulls($select, $col_infos, \@row, $self);
154 # We need to catch VARCHAR(max) before bind_attribute_by_data_type because it
155 # could be specified by size, also if bind_attribute_by_data_type fails we want
156 # to specify the default ado_size of 8000.
157 # Also make sure GUID binds have braces on them or else ADO throws an "Invalid
158 # character value for cast specification"
160 sub _dbi_attrs_for_bind {
162 my ($ident, $bind) = @_;
164 my $lob_max = $self->_get_dbh->{LongReadLen} || 32768;
166 foreach my $bind (@$bind) {
167 my $attrs = $bind->[0];
168 my $data_type = $attrs->{sqlt_datatype};
169 my $size = $attrs->{sqlt_size};
171 if ($size && lc($size) eq 'max') {
172 if ($data_type =~ /^(?:varchar|character varying|nvarchar|national char varying|national character varying|varbinary)\z/i) {
173 $attrs->{dbd_attrs} = { ado_size => $lob_max };
176 carp_unique "bizarre data_type '$data_type' with size => 'max'";
180 if ($self->_is_guid_type($data_type) && substr($bind->[1], 0, 1) ne '{') {
181 $bind->[1] = '{' . $bind->[1] . '}';
185 my $attrs = $self->next::method(@_);
187 # The next::method above caches the returned hashrefs in a _dbh related
188 # structure. It is safe for us to modify it in this manner, as the default
189 # does not really change (albeit the entire logic is insane and is pending
190 # a datatype-objects rewrite)
191 $_ and $_->{ado_size} ||= 8000 for @$attrs;
196 # Can't edit all the binds in _dbi_attrs_for_bind for _insert_bulk, so we take
197 # care of those GUIDs here.
200 my ($source, $cols, $data) = @_;
202 my $columns_info = $source->columns_info($cols);
205 foreach my $col (@$cols) {
206 if ($self->_is_guid_type($columns_info->{$col}{data_type})) {
207 foreach my $data_row (@$data) {
208 if (substr($data_row->[$col_idx], 0, 1) ne '{') {
209 $data_row->[$col_idx] = '{' . $data_row->[$col_idx] . '}';
216 return $self->next::method(@_);
219 sub bind_attribute_by_data_type {
220 my ($self, $data_type) = @_;
222 $data_type = lc $data_type;
225 $self->_mssql_max_data_type_representation_size_in_bytes->{$data_type};
230 $res->{ado_size} = $max_size;
233 carp_unique "could not map data_type '$data_type' to a max size for ado_size: defaulting to 8000";
239 # FIXME This list is an abomination. We need a way to do this outside
240 # of the scope of DBIC, as it is right now nobody will ever think to
241 # even look here to diagnose some sort of misbehavior.
242 sub _mssql_max_data_type_representation_size_in_bytes {
245 my $lob_max = $self->_get_dbh->{LongReadLen} || 32768;
252 'varchar(max)' => $lob_max,
253 'character varying' => 8000,
256 'varbinary(max)' => $lob_max,
258 'national character' => 16000,
259 'national char' => 16000,
261 'nvarchar(max)' => ($lob_max*2),
262 'national character varying' => 16000,
263 'national char varying' => 16000,
274 'int identity' => 100,
275 'integer identity' => 100,
279 'double precision' => 100,
281 uniqueidentifier => 100,
288 datetimeoffset => 100,
289 smalldatetime => 100,
302 'tinyint unsigned' => 100,
303 'smallint unsigned' => 100,
304 'mediumint unsigned' => 100,
305 'int unsigned' => 100,
306 'integer unsigned' => 100,
307 'bigint unsigned' => 100,
308 'float unsigned' => 100,
309 'double unsigned' => 100,
310 'double precision unsigned' => 100,
311 'decimal unsigned' => 100,
314 tinyblob => $lob_max,
315 tinytext => $lob_max,
318 mediumblob => $lob_max,
319 mediumtext => $lob_max,
320 longblob => $lob_max,
321 longtext => $lob_max,
338 'bit varying' => 8000,
343 'time without time zone' => 100,
344 'time with time zone' => 100,
345 'timestamp without time zone' => 100,
346 'timestamp with time zone' => 100,
352 'long vargraphic' => $lob_max,
355 'char for bit data' => 8000,
356 'varchar for bit data' => 8000,
357 'long varchar for bit data' => $lob_max,
362 binary_double => 100,
366 'long raw' => $lob_max,
367 'timestamp with local time zone' => 100,
375 'long varbit' => $lob_max,
376 'long bit varying' => $lob_max,
377 uniqueidentifierstr => 100,
378 'long binary' => $lob_max,
379 'long varchar' => $lob_max,
380 'long nvarchar' => $lob_max,
383 'char(x) character set unicode_fss' => 16000,
384 'varchar(x) character set unicode_fss' => 16000,
385 'blob sub_type text' => $lob_max,
386 'blob sub_type text character set unicode_fss' => $lob_max,
392 'datetime year to fraction(5)' => 100,
393 # FIXME add other datetime types
396 autoincrement => 100,
411 longchar => $lob_max,
413 longbinary => $lob_max,
417 package # hide from PAUSE
418 DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::DateTime::Format;
420 my $datetime_format = '%m/%d/%Y %I:%M:%S %p';
425 require DateTime::Format::Strptime;
426 $datetime_parser ||= DateTime::Format::Strptime->new(
427 pattern => $datetime_format,
430 return $datetime_parser->parse_datetime(shift);
433 sub format_datetime {
435 require DateTime::Format::Strptime;
436 $datetime_parser ||= DateTime::Format::Strptime->new(
437 pattern => $datetime_format,
440 return $datetime_parser->format_datetime(shift);
443 =head1 FURTHER QUESTIONS?
445 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
447 =head1 COPYRIGHT AND LICENSE
449 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
450 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
451 redistribute it and/or modify it under the same terms as the
452 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.