1 package DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server;
7 DBIx::Class::Storage::DBI::ADO
8 DBIx::Class::Storage::DBI::MSSQL
11 use DBIx::Class::Carp;
12 use DBIx::Class::Storage::DBI::ADO::CursorUtils qw/_normalize_guids _strip_trailing_binary_nulls/;
15 __PACKAGE__->cursor_class(
16 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor'
19 __PACKAGE__->datetime_parser_type (
20 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::DateTime::Format'
23 __PACKAGE__->new_guid(sub {
25 my $guid = $self->_get_dbh->selectrow_array('SELECT NEWID()');
26 $guid =~ s/\A \{ (.+) \} \z/$1/xs;
32 DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server - Support for Microsoft
33 SQL Server via DBD::ADO
37 This subclass supports MSSQL server connections via L<DBD::ADO>.
41 The MSSQL specific functionality is provided by
42 L<DBIx::Class::Storage::DBI::MSSQL>.
46 dbi:ADO:provider=sqlncli10;server=EEEBOX\SQLEXPRESS
52 C<_identity_method> is set to C<@@identity>, as C<SCOPE_IDENTITY()> doesn't work
53 with L<DBD::ADO>. See L<DBIx::Class::Storage::DBI::MSSQL/IMPLEMENTATION NOTES>
54 for caveats regarding this.
58 There is a bug with MSSQL ADO providers where data gets truncated based on the
59 size of the bind sizes in the first prepare call:
61 L<https://rt.cpan.org/Ticket/Display.html?id=52048>
63 The C<ado_size> workaround is used (see L<DBD::ADO/ADO providers>) with the
64 approximate maximum size of the data_type of the bound column, or 8000 (maximum
65 VARCHAR size) if the data_type is not available.
67 Please report problems with this driver and send patches.
71 C<LongReadLen> is set to C<LongReadLen * 2 + 1> on connection as it is necessary
72 for some LOB types. Be aware of this if you localize this value on the C<$dbh>
77 Due perhaps to the ado_size workaround we use, and/or other reasons, binary data
78 such as C<varbinary> column data comes back padded with trailing C<NULL> chars.
79 The Cursor class for this driver
80 (L<DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor>) removes them,
81 of course if your binary data is actually C<NULL> padded that may be an issue to
82 keep in mind when using this driver.
84 =head2 uniqueidentifier columns
86 uniqueidentifier columns come back from ADO wrapped in braces and must be
87 submitted to the MSSQL ADO driver wrapped in braces. We take care of this
88 transparently in this driver and the associated Cursor class
89 (L<DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor>) so that you
90 don't have to use braces in most cases (except in literal SQL, in those cases
91 you will have to add the braces yourself.)
93 =head2 fractional seconds
95 Fractional seconds with L<DBIx::Class::InflateColumn::DateTime> are not
96 currently supported, datetimes are truncated at the second.
103 # SCOPE_IDENTITY() doesn't work
104 $self->_identity_method('@@identity');
105 $self->_no_scope_identity_query(1);
107 return $self->next::method(@_);
110 sub _run_connection_actions {
113 # make transactions work
114 require DBD::ADO::Const;
115 $self->_dbh->{ado_conn}{CursorLocation} =
116 DBD::ADO::Const->Enums->{CursorLocationEnum}{adUseClient};
118 # set LongReadLen = LongReadLen * 2 + 1
119 # this may need to be in ADO.pm, being conservative for now...
120 my $long_read_len = $self->_dbh->{LongReadLen};
122 # This is the DBD::ADO default.
123 if ($long_read_len != 2147483647) {
124 $self->_dbh->{LongReadLen} = $long_read_len * 2 + 1;
127 return $self->next::method(@_);
131 # Fix up binary data and GUIDs for ->find, for cursors see the cursor_class
135 my ($ident, $select) = @_;
137 my @row = $self->next::method(@_);
139 return @row unless $self->cursor_class->isa(
140 'DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::Cursor'
143 my $col_infos = $self->_resolve_column_info($ident);
145 _normalize_guids($select, $col_infos, \@row, $self);
147 _strip_trailing_binary_nulls($select, $col_infos, \@row, $self);
152 # We need to catch VARCHAR(max) before bind_attribute_by_data_type because it
153 # could be specified by size, also if bind_attribute_by_data_type fails we want
154 # to specify the default ado_size of 8000.
155 # Also make sure GUID binds have braces on them or else ADO throws an "Invalid
156 # character value for cast specification"
158 sub _dbi_attrs_for_bind {
160 my ($ident, $bind) = @_;
162 my $lob_max = $self->_get_dbh->{LongReadLen} || 32768;
164 foreach my $bind (@$bind) {
165 my $attrs = $bind->[0];
166 my $data_type = $attrs->{sqlt_datatype};
167 my $size = $attrs->{sqlt_size};
169 if ($size && lc($size) eq 'max') {
170 if ($data_type =~ /^(?:varchar|character varying|nvarchar|national char varying|national character varying|varbinary)\z/i) {
171 $attrs->{dbd_attrs} = { ado_size => $lob_max };
174 carp_unique "bizarre data_type '$data_type' with size => 'max'";
178 if ($self->_is_guid_type($data_type) && substr($bind->[1], 0, 1) ne '{') {
179 $bind->[1] = '{' . $bind->[1] . '}';
183 my $attrs = $self->next::method(@_);
185 foreach my $attr (@$attrs) {
186 $attr->{ado_size} ||= 8000 if $attr;
192 # Can't edit all the binds in _dbi_attrs_for_bind for _insert_bulk, so we take
193 # care of those GUIDs here.
196 my ($source, $cols, $data) = @_;
198 my $columns_info = $source->columns_info($cols);
201 foreach my $col (@$cols) {
202 if ($self->_is_guid_type($columns_info->{$col}{data_type})) {
203 foreach my $data_row (@$data) {
204 if (substr($data_row->[$col_idx], 0, 1) ne '{') {
205 $data_row->[$col_idx] = '{' . $data_row->[$col_idx] . '}';
212 return $self->next::method(@_);
215 sub bind_attribute_by_data_type {
216 my ($self, $data_type) = @_;
218 $data_type = lc $data_type;
221 $self->_mssql_max_data_type_representation_size_in_bytes->{$data_type};
226 $res->{ado_size} = $max_size;
229 carp_unique "could not map data_type '$data_type' to a max size for ado_size: defaulting to 8000";
235 # FIXME This list is an abomination. We need a way to do this outside
236 # of the scope of DBIC, as it is right now nobody will ever think to
237 # even look here to diagnose some sort of misbehavior.
238 sub _mssql_max_data_type_representation_size_in_bytes {
241 my $lob_max = $self->_get_dbh->{LongReadLen} || 32768;
248 'varchar(max)' => $lob_max,
249 'character varying' => 8000,
252 'varbinary(max)' => $lob_max,
254 'national character' => 16000,
255 'national char' => 16000,
257 'nvarchar(max)' => ($lob_max*2),
258 'national character varying' => 16000,
259 'national char varying' => 16000,
270 'int identity' => 100,
271 'integer identity' => 100,
275 'double precision' => 100,
277 uniqueidentifier => 100,
284 datetimeoffset => 100,
285 smalldatetime => 100,
298 'tinyint unsigned' => 100,
299 'smallint unsigned' => 100,
300 'mediumint unsigned' => 100,
301 'int unsigned' => 100,
302 'integer unsigned' => 100,
303 'bigint unsigned' => 100,
304 'float unsigned' => 100,
305 'double unsigned' => 100,
306 'double precision unsigned' => 100,
307 'decimal unsigned' => 100,
310 tinyblob => $lob_max,
311 tinytext => $lob_max,
314 mediumblob => $lob_max,
315 mediumtext => $lob_max,
316 longblob => $lob_max,
317 longtext => $lob_max,
334 'bit varying' => 8000,
339 'time without time zone' => 100,
340 'time with time zone' => 100,
341 'timestamp without time zone' => 100,
342 'timestamp with time zone' => 100,
348 'long vargraphic' => $lob_max,
351 'char for bit data' => 8000,
352 'varchar for bit data' => 8000,
353 'long varchar for bit data' => $lob_max,
358 binary_double => 100,
362 'long raw' => $lob_max,
363 'timestamp with local time zone' => 100,
371 'long varbit' => $lob_max,
372 'long bit varying' => $lob_max,
373 uniqueidentifierstr => 100,
374 'long binary' => $lob_max,
375 'long varchar' => $lob_max,
376 'long nvarchar' => $lob_max,
379 'char(x) character set unicode_fss' => 16000,
380 'varchar(x) character set unicode_fss' => 16000,
381 'blob sub_type text' => $lob_max,
382 'blob sub_type text character set unicode_fss' => $lob_max,
388 'datetime year to fraction(5)' => 100,
389 # FIXME add other datetime types
392 autoincrement => 100,
407 longchar => $lob_max,
409 longbinary => $lob_max,
413 package # hide from PAUSE
414 DBIx::Class::Storage::DBI::ADO::Microsoft_SQL_Server::DateTime::Format;
416 my $datetime_format = '%m/%d/%Y %I:%M:%S %p';
421 require DateTime::Format::Strptime;
422 $datetime_parser ||= DateTime::Format::Strptime->new(
423 pattern => $datetime_format,
426 return $datetime_parser->parse_datetime(shift);
429 sub format_datetime {
431 require DateTime::Format::Strptime;
432 $datetime_parser ||= DateTime::Format::Strptime->new(
433 pattern => $datetime_format,
436 return $datetime_parser->format_datetime(shift);
439 =head1 FURTHER QUESTIONS?
441 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
443 =head1 COPYRIGHT AND LICENSE
445 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
446 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
447 redistribute it and/or modify it under the same terms as the
448 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.