1 package DBIx::Class::Storage::DBI::MSSQL;
7 DBIx::Class::Storage::DBI::UniqueIdentifier
8 DBIx::Class::Storage::DBI::IdentityInsert
12 use DBIx::Class::_Util qw( dbic_internal_try dbic_internal_catch sigwarn_silencer );
15 __PACKAGE__->mk_group_accessors(simple => qw/
16 _identity _identity_method _no_scope_identity_query
19 __PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::MSSQL');
21 __PACKAGE__->sql_quote_char([qw/[ ]/]);
23 __PACKAGE__->datetime_parser_type (
24 'DBIx::Class::Storage::DBI::MSSQL::DateTime::Format'
27 __PACKAGE__->new_guid('NEWID()');
29 sub _prep_for_execute {
31 my ($op, $ident, $args) = @_;
33 # cast MONEY values properly
34 if ($op eq 'insert' || $op eq 'update') {
35 my $fields = $args->[0];
37 my $colinfo = $ident->columns_info([keys %$fields]);
39 for my $col (keys %$fields) {
40 # $ident is a result source object with INSERT/UPDATE ops
42 $colinfo->{$col}{data_type}
44 $colinfo->{$col}{data_type} =~ /^money\z/i
46 my $val = $fields->{$col};
47 $fields->{$col} = \['CAST(? AS MONEY)', [ $col => $val ]];
52 my ($sql, $bind) = $self->next::method (@_);
54 # SELECT SCOPE_IDENTITY only works within a statement scope. We
55 # must try to always use this particular idiom first, as it is the
56 # only one that guarantees retrieving the correct id under high
57 # concurrency. When this fails we will fall back to whatever secondary
58 # retrieval method is specified in _identity_method, but at this
59 # point we don't have many guarantees we will get what we expected.
60 # http://msdn.microsoft.com/en-us/library/ms190315.aspx
61 # http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx
62 if ($self->_perform_autoinc_retrieval and not $self->_no_scope_identity_query) {
63 $sql .= "\nSELECT SCOPE_IDENTITY()";
72 # always list ctx - we need the $sth
73 my ($rv, $sth, @bind) = $self->next::method(@_);
75 if ($self->_perform_autoinc_retrieval) {
77 # attempt to bring back the result of SELECT SCOPE_IDENTITY() we tacked
78 # on in _prep_for_execute above
81 # we didn't even try on ftds
82 unless ($self->_no_scope_identity_query) {
83 ($identity) = dbic_internal_try { $sth->fetchrow_array };
87 # SCOPE_IDENTITY failed, but we can do something else
88 if ( (! $identity) && $self->_identity_method) {
89 ($identity) = $self->_dbh->selectrow_array(
90 'select ' . $self->_identity_method
94 $self->_identity($identity);
97 return wantarray ? ($rv, $sth, @bind) : $rv;
100 sub last_insert_id { shift->_identity }
103 # MSSQL is retarded wrt ordered subselects. One needs to add a TOP
104 # to *all* subqueries, but one also *can't* use TOP 100 PERCENT
105 # http://sqladvice.com/forums/permalink/18496/22931/ShowThread.aspx#22931
107 sub _select_args_to_query {
108 #my ($self, $ident, $select, $cond, $attrs) = @_;
112 my $sql_bind = $self->next::method (@_);
114 # see if this is an ordered subquery
116 $$sql_bind->[0] !~ /^ \s* \( \s* SELECT \s+ TOP \s+ \d+ \s+ /xi
118 scalar $self->_extract_order_criteria ($attrs->{order_by})
120 $self->throw_exception(
121 'An ordered subselect encountered - this is not safe! Please see "Ordered Subselects" in DBIx::Class::Storage::DBI::MSSQL'
122 ) unless $attrs->{unsafe_subselect_ok};
124 $$sql_bind->[0] =~ s/^ \s* \( \s* SELECT (?=\s) / '(SELECT TOP ' . $self->sql_maker->__max_int /exi;
131 # savepoint syntax is the same as in Sybase ASE
133 sub _exec_svp_begin {
134 my ($self, $name) = @_;
136 $self->_dbh->do("SAVE TRANSACTION $name");
139 # A new SAVE TRANSACTION with the same name releases the previous one.
140 sub _exec_svp_release { 1 }
142 sub _exec_svp_rollback {
143 my ($self, $name) = @_;
145 $self->_dbh->do("ROLLBACK TRANSACTION $name");
148 sub sqlt_type { 'SQLServer' }
150 sub sql_limit_dialect {
153 my $supports_rno = 0;
155 if (exists $self->_server_info->{normalized_dbms_version}) {
156 $supports_rno = 1 if $self->_server_info->{normalized_dbms_version} >= 9;
159 # User is connecting via DBD::Sybase and has no permission to run
160 # stored procedures like xp_msver, or version detection failed for some
162 # So, we use a query to check if RNO is implemented.
164 $self->_get_dbh->selectrow_array('SELECT row_number() OVER (ORDER BY rand())');
169 return $supports_rno ? 'RowNumberOver' : 'Top';
175 my $dbh = $self->_dbh or return 0;
178 local $dbh->{RaiseError} = 1;
179 local $dbh->{PrintError} = 0;
181 $dbh->do('select 1');
184 dbic_internal_catch {
185 # MSSQL is *really* annoying wrt multiple active resultsets,
186 # and this may very well be the reason why the _ping failed
188 # Proactively disconnect, while hiding annoying warnings if the case
191 # < check basic retryability prerequisites (e.g. no txn) >
193 # ->storage->connected()
195 # So if we got here with the in_handler bit set - we won't break
196 # anything by a disconnect
197 if( $self->{_in_do_block_retry_handler} ) {
198 local $SIG{__WARN__} = sigwarn_silencer qr/disconnect invalidates .+? active statement/;
207 package # hide from PAUSE
208 DBIx::Class::Storage::DBI::MSSQL::DateTime::Format;
210 my $datetime_format = '%Y-%m-%d %H:%M:%S.%3N'; # %F %T
211 my $smalldatetime_format = '%Y-%m-%d %H:%M:%S';
213 my ($datetime_parser, $smalldatetime_parser);
217 require DateTime::Format::Strptime;
218 $datetime_parser ||= DateTime::Format::Strptime->new(
219 pattern => $datetime_format,
222 return $datetime_parser->parse_datetime(shift);
225 sub format_datetime {
227 require DateTime::Format::Strptime;
228 $datetime_parser ||= DateTime::Format::Strptime->new(
229 pattern => $datetime_format,
232 return $datetime_parser->format_datetime(shift);
235 sub parse_smalldatetime {
237 require DateTime::Format::Strptime;
238 $smalldatetime_parser ||= DateTime::Format::Strptime->new(
239 pattern => $smalldatetime_format,
242 return $smalldatetime_parser->parse_datetime(shift);
245 sub format_smalldatetime {
247 require DateTime::Format::Strptime;
248 $smalldatetime_parser ||= DateTime::Format::Strptime->new(
249 pattern => $smalldatetime_format,
252 return $smalldatetime_parser->format_datetime(shift);
259 DBIx::Class::Storage::DBI::MSSQL - Base Class for Microsoft SQL Server support
264 This is the base class for Microsoft SQL Server support, used by
265 L<DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server> and
266 L<DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server>.
268 =head1 IMPLEMENTATION NOTES
270 =head2 IDENTITY information
272 Microsoft SQL Server supports three methods of retrieving the IDENTITY
273 value for inserted row: IDENT_CURRENT, @@IDENTITY, and SCOPE_IDENTITY().
274 SCOPE_IDENTITY is used here because it is the safest. However, it must
275 be called is the same execute statement, not just the same connection.
277 So, this implementation appends a SELECT SCOPE_IDENTITY() statement
278 onto each INSERT to accommodate that requirement.
280 C<SELECT @@IDENTITY> can also be used by issuing:
282 $self->_identity_method('@@identity');
284 it will only be used if SCOPE_IDENTITY() fails.
286 This is more dangerous, as inserting into a table with an on insert trigger that
287 inserts into another table with an identity will give erroneous results on
288 recent versions of SQL Server.
290 =head2 identity insert
292 Be aware that we have tried to make things as simple as possible for our users.
293 For MSSQL that means that when a user tries to create a row, while supplying an
294 explicit value for an autoincrementing column, we will try to issue the
295 appropriate database call to make this possible, namely C<SET IDENTITY_INSERT
296 $table_name ON>. Unfortunately this operation in MSSQL requires the
297 C<db_ddladmin> privilege, which is normally not included in the standard
300 =head2 Ordered Subselects
302 If you attempted the following query (among many others) in Microsoft SQL
306 prefetch => 'relation',
311 You may be surprised to receive an exception. The reason for this is a quirk
312 in the MSSQL engine itself, and sadly doesn't have a sensible workaround due
313 to the way DBIC is built. DBIC can do truly wonderful things with the aid of
314 subselects, and does so automatically when necessary. The list of situations
315 when a subselect is necessary is long and still changes often, so it can not
316 be exhaustively enumerated here. The general rule of thumb is a joined
317 L<has_many|DBIx::Class::Relationship/has_many> relationship with limit/group
318 applied to the left part of the join.
320 In its "pursuit of standards" Microsft SQL Server goes to great lengths to
321 forbid the use of ordered subselects. This breaks a very useful group of
322 searches like "Give me things number 4 to 6 (ordered by name), and prefetch
323 all their relations, no matter how many". While there is a hack which fools
324 the syntax checker, the optimizer may B<still elect to break the subselect>.
325 Testing has determined that while such breakage does occur (the test suite
326 contains an explicit test which demonstrates the problem), it is relative
327 rare. The benefits of ordered subselects are on the other hand too great to be
328 outright disabled for MSSQL.
330 Thus compromise between usability and perfection is the MSSQL-specific
331 L<resultset attribute|DBIx::Class::ResultSet/ATTRIBUTES> C<unsafe_subselect_ok>.
332 It is deliberately not possible to set this on the Storage level, as the user
333 should inspect (and preferably regression-test) the return of every such
334 ResultSet individually. The example above would work if written like:
337 unsafe_subselect_ok => 1,
338 prefetch => 'relation',
343 If it is possible to rewrite the search() in a way that will avoid the need
344 for this flag - you are urged to do so. If DBIC internals insist that an
345 ordered subselect is necessary for an operation, and you believe there is a
346 different/better way to get the same result - please file a bugreport.
348 =head1 FURTHER QUESTIONS?
350 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
352 =head1 COPYRIGHT AND LICENSE
354 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
355 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
356 redistribute it and/or modify it under the same terms as the
357 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.