1 package DBIx::Class::Storage::DBI::MSSQL;
7 DBIx::Class::Storage::DBI::UniqueIdentifier
8 DBIx::Class::Storage::DBI::IdentityInsert
13 use List::Util 'first';
16 __PACKAGE__->mk_group_accessors(simple => qw/
17 _identity _identity_method
20 __PACKAGE__->sql_maker_class('DBIx::Class::SQLMaker::MSSQL');
22 __PACKAGE__->sql_quote_char([qw/[ ]/]);
24 __PACKAGE__->datetime_parser_type (
25 'DBIx::Class::Storage::DBI::MSSQL::DateTime::Format'
28 __PACKAGE__->new_guid('NEWID()');
30 sub _prep_for_execute {
32 my ($op, $ident, $args) = @_;
34 # cast MONEY values properly
35 if ($op eq 'insert' || $op eq 'update') {
36 my $fields = $args->[0];
38 my $colinfo = $ident->columns_info([keys %$fields]);
40 for my $col (keys %$fields) {
41 # $ident is a result source object with INSERT/UPDATE ops
43 $colinfo->{$col}{data_type}
45 $colinfo->{$col}{data_type} =~ /^money\z/i
47 my $val = $fields->{$col};
48 $fields->{$col} = \['CAST(? AS MONEY)', [ $col => $val ]];
53 my ($sql, $bind) = $self->next::method (@_);
55 # SELECT SCOPE_IDENTITY only works within a statement scope. We
56 # must try to always use this particular idiom frist, as it is the
57 # only one that guarantees retrieving the correct id under high
58 # concurrency. When this fails we will fall back to whatever secondary
59 # retrieval method is specified in _identity_method, but at this
60 # point we don't have many guarantees we will get what we expected.
61 # http://msdn.microsoft.com/en-us/library/ms190315.aspx
62 # http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx
63 if ($self->_perform_autoinc_retrieval) {
64 $sql .= "\nSELECT SCOPE_IDENTITY()";
74 # always list ctx - we need the $sth
75 my ($rv, $sth, @bind) = $self->next::method(@_);
77 if ($self->_perform_autoinc_retrieval) {
79 # this should bring back the result of SELECT SCOPE_IDENTITY() we tacked
80 # on in _prep_for_execute above
81 my ($identity) = try { $sth->fetchrow_array };
83 # SCOPE_IDENTITY failed, but we can do something else
84 if ( (! $identity) && $self->_identity_method) {
85 ($identity) = $self->_dbh->selectrow_array(
86 'select ' . $self->_identity_method
90 $self->_identity($identity);
94 return wantarray ? ($rv, $sth, @bind) : $rv;
97 sub last_insert_id { shift->_identity }
100 # MSSQL is retarded wrt ordered subselects. One needs to add a TOP
101 # to *all* subqueries, but one also *can't* use TOP 100 PERCENT
102 # http://sqladvice.com/forums/permalink/18496/22931/ShowThread.aspx#22931
104 sub _select_args_to_query {
107 my ($sql, $prep_bind, @rest) = $self->next::method (@_);
109 # see if this is an ordered subquery
112 $sql !~ /^ \s* SELECT \s+ TOP \s+ \d+ \s+ /xi
114 scalar $self->_extract_order_criteria ($attrs->{order_by})
116 $self->throw_exception(
117 'An ordered subselect encountered - this is not safe! Please see "Ordered Subselects" in DBIx::Class::Storage::DBI::MSSQL
118 ') unless $attrs->{unsafe_subselect_ok};
119 my $max = $self->sql_maker->__max_int;
120 $sql =~ s/^ \s* SELECT \s/SELECT TOP $max /xi;
124 ? ($sql, $prep_bind, @rest)
125 : \[ "($sql)", @$prep_bind ]
130 # savepoint syntax is the same as in Sybase ASE
132 sub _exec_svp_begin {
133 my ($self, $name) = @_;
135 $self->_dbh->do("SAVE TRANSACTION $name");
138 # A new SAVE TRANSACTION with the same name releases the previous one.
139 sub _exec_svp_release { 1 }
141 sub _exec_svp_rollback {
142 my ($self, $name) = @_;
144 $self->_dbh->do("ROLLBACK TRANSACTION $name");
147 sub sqlt_type { 'SQLServer' }
149 sub sql_limit_dialect {
152 my $supports_rno = 0;
154 if (exists $self->_server_info->{normalized_dbms_version}) {
155 $supports_rno = 1 if $self->_server_info->{normalized_dbms_version} >= 9;
158 # User is connecting via DBD::Sybase and has no permission to run
159 # stored procedures like xp_msver, or version detection failed for some
161 # So, we use a query to check if RNO is implemented.
163 $self->_get_dbh->selectrow_array('SELECT row_number() OVER (ORDER BY rand())');
168 return $supports_rno ? 'RowNumberOver' : 'Top';
174 my $dbh = $self->_dbh or return 0;
176 local $dbh->{RaiseError} = 1;
177 local $dbh->{PrintError} = 0;
180 $dbh->do('select 1');
187 package # hide from PAUSE
188 DBIx::Class::Storage::DBI::MSSQL::DateTime::Format;
190 my $datetime_format = '%Y-%m-%d %H:%M:%S.%3N'; # %F %T
191 my $smalldatetime_format = '%Y-%m-%d %H:%M:%S';
193 my ($datetime_parser, $smalldatetime_parser);
197 require DateTime::Format::Strptime;
198 $datetime_parser ||= DateTime::Format::Strptime->new(
199 pattern => $datetime_format,
202 return $datetime_parser->parse_datetime(shift);
205 sub format_datetime {
207 require DateTime::Format::Strptime;
208 $datetime_parser ||= DateTime::Format::Strptime->new(
209 pattern => $datetime_format,
212 return $datetime_parser->format_datetime(shift);
215 sub parse_smalldatetime {
217 require DateTime::Format::Strptime;
218 $smalldatetime_parser ||= DateTime::Format::Strptime->new(
219 pattern => $smalldatetime_format,
222 return $smalldatetime_parser->parse_datetime(shift);
225 sub format_smalldatetime {
227 require DateTime::Format::Strptime;
228 $smalldatetime_parser ||= DateTime::Format::Strptime->new(
229 pattern => $smalldatetime_format,
232 return $smalldatetime_parser->format_datetime(shift);
239 DBIx::Class::Storage::DBI::MSSQL - Base Class for Microsoft SQL Server support
244 This is the base class for Microsoft SQL Server support, used by
245 L<DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server> and
246 L<DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server>.
248 =head1 IMPLEMENTATION NOTES
250 =head2 IDENTITY information
252 Microsoft SQL Server supports three methods of retrieving the IDENTITY
253 value for inserted row: IDENT_CURRENT, @@IDENTITY, and SCOPE_IDENTITY().
254 SCOPE_IDENTITY is used here because it is the safest. However, it must
255 be called is the same execute statement, not just the same connection.
257 So, this implementation appends a SELECT SCOPE_IDENTITY() statement
258 onto each INSERT to accommodate that requirement.
260 C<SELECT @@IDENTITY> can also be used by issuing:
262 $self->_identity_method('@@identity');
264 it will only be used if SCOPE_IDENTITY() fails.
266 This is more dangerous, as inserting into a table with an on insert trigger that
267 inserts into another table with an identity will give erroneous results on
268 recent versions of SQL Server.
270 =head2 identity insert
272 Be aware that we have tried to make things as simple as possible for our users.
273 For MSSQL that means that when a user tries to create a row, while supplying an
274 explicit value for an autoincrementing column, we will try to issue the
275 appropriate database call to make this possible, namely C<SET IDENTITY_INSERT
276 $table_name ON>. Unfortunately this operation in MSSQL requires the
277 C<db_ddladmin> privilege, which is normally not included in the standard
280 =head2 Ordered Subselects
282 If you attempted the following query (among many others) in Microsoft SQL
286 prefetch => 'relation',
291 You may be surprised to receive an exception. The reason for this is a quirk
292 in the MSSQL engine itself, and sadly doesn't have a sensible workaround due
293 to the way DBIC is built. DBIC can do truly wonderful things with the aid of
294 subselects, and does so automatically when necessary. The list of situations
295 when a subselect is necessary is long and still changes often, so it can not
296 be exhaustively enumerated here. The general rule of thumb is a joined
297 L<has_many|DBIx::Class::Relationship/has_many> relationship with limit/group
298 applied to the left part of the join.
300 In its "pursuit of standards" Microsft SQL Server goes to great lengths to
301 forbid the use of ordered subselects. This breaks a very useful group of
302 searches like "Give me things number 4 to 6 (ordered by name), and prefetch
303 all their relations, no matter how many". While there is a hack which fools
304 the syntax checker, the optimizer may B<still elect to break the subselect>.
305 Testing has determined that while such breakage does occur (the test suite
306 contains an explicit test which demonstrates the problem), it is relative
307 rare. The benefits of ordered subselects are on the other hand too great to be
308 outright disabled for MSSQL.
310 Thus compromise between usability and perfection is the MSSQL-specific
311 L<resultset attribute|DBIx::Class::ResultSet/ATTRIBUTES> C<unsafe_subselect_ok>.
312 It is deliberately not possible to set this on the Storage level, as the user
313 should inspect (and preferably regression-test) the return of every such
314 ResultSet individually. The example above would work if written like:
317 unsafe_subselect_ok => 1,
318 prefetch => 'relation',
323 If it is possible to rewrite the search() in a way that will avoid the need
324 for this flag - you are urged to do so. If DBIC internals insist that an
325 ordered subselect is necessary for an operation, and you believe there is a
326 different/better way to get the same result - please file a bugreport.
330 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
334 You may distribute this code under the same terms as Perl itself.