1 package DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server;
5 use base qw/DBIx::Class::Storage::DBI::MSSQL/;
7 use Carp::Clan qw/^DBIx::Class/;
10 __PACKAGE__->mk_group_accessors(simple => qw/
11 _scope_identity _using_dynamic_cursors
16 DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server - Support specific
17 to Microsoft SQL Server over ODBC
21 This class implements support specific to Microsoft SQL Server over ODBC,
22 including auto-increment primary keys and SQL::Abstract::Limit dialect. It
23 is loaded automatically by by DBIx::Class::Storage::DBI::ODBC when it
24 detects a MSSQL back-end.
26 =head1 IMPLEMENTATION NOTES
28 Microsoft SQL Server supports three methods of retrieving the C<IDENTITY>
29 value for inserted row: C<IDENT_CURRENT>, C<@@IDENTITY>, and C<SCOPE_IDENTITY()>.
30 C<SCOPE_IDENTITY()> is used here because it is the safest. However, it must
31 be called is the same execute statement, not just the same connection.
33 So, this implementation appends a C<SELECT SCOPE_IDENTITY()> statement
34 onto each C<INSERT> to accommodate that requirement.
36 If you use dynamic cursors with C<< odbc_cursortype => 2 >> or
37 L</on_connect_call_use_dynamic_cursors> then the less accurate
38 C<SELECT @@IDENTITY> is used instead.
40 =head1 MULTIPLE ACTIVE STATEMENTS
42 The following options are alternative ways to enable concurrent executing
43 statement support. Each has its own advantages and drawbacks.
45 =head2 connect_call_use_dynamic_cursors
49 on_connect_call => 'use_dynamic_cursors'
51 in your L<DBIx::Class::Storage::DBI/connect_info> as one way to enable multiple
52 concurrent statements.
54 Will add C<< odbc_cursortype => 2 >> to your DBI connection attributes. See
55 L<DBD::ODBC/odbc_cursortype> for more information.
57 This will not work with CODE ref connect_info's and will do nothing if you set
58 C<odbc_cursortype> yourself.
60 B<WARNING:> this will break C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will
61 be used instead, which on SQL Server 2005 and later will return erroneous
62 results on tables which have an on insert trigger that inserts into another
63 table with an C<IDENTITY> column.
67 sub connect_call_use_dynamic_cursors {
70 if (ref($self->_dbi_connect_info->[0]) eq 'CODE') {
71 croak 'cannot set DBI attributes on a CODE ref connect_info';
74 my $dbi_attrs = $self->_dbi_connect_info->[-1];
77 if (not exists $dbi_attrs->{odbc_cursortype}) {
78 # turn on support for multiple concurrent statements, unless overridden
79 $self->_dbi_connect_info->[-1] = { %$dbi_attrs, odbc_cursortype => 2 };
80 # will take effect next connection
82 $self->_using_dynamic_cursors(1);
87 no warnings 'uninitialized';
90 if (ref($self->_dbi_connect_info->[0]) ne 'CODE' &&
91 $self->_dbi_connect_info->[-1]{odbc_cursortype} == 2) {
92 $self->_using_dynamic_cursors(1);
96 $self->_using_dynamic_cursors(0);
99 =head2 connect_call_use_server_cursors
103 on_connect_call => 'use_server_cursors'
105 May allow multiple active select statements. See
106 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
108 Takes an optional parameter for the value to set the attribute to, default is
111 B<WARNING>: this does not work on all versions of SQL Server, and may lock up
116 =head2 connect_call_use_mars
120 on_connect_call => 'use_mars'
122 Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result
123 Sets". See L<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?>
124 for more information.
126 B<WARNING>: This has implications for the way transactions are handled.
130 sub connect_call_use_mars {
133 my $dsn = $self->_dbi_connect_info->[0];
135 if (ref($dsn) eq 'CODE') {
136 croak 'cannot change the DBI DSN on a CODE ref connect_info';
139 if ($dsn !~ /MARS_Connection=/) {
140 $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes";
141 # will take effect next connection
148 my ($source, $cols, $data) = @_;
150 my $identity_insert = 0;
153 foreach my $col (@{$cols}) {
154 if ($source->column_info($col)->{is_auto_increment}) {
155 $identity_insert = 1;
160 if ($identity_insert) {
161 my $table = $source->from;
162 $self->_get_dbh->do("SET IDENTITY_INSERT $table ON");
165 $self->next::method(@_);
167 if ($identity_insert) {
168 my $table = $source->from;
169 $self->_get_dbh->do("SET IDENTITY_INSERT $table OFF");
173 sub _prep_for_execute {
175 my ($op, $extra_bind, $ident, $args) = @_;
177 my ($sql, $bind) = $self->next::method (@_);
179 if ($op eq 'insert') {
180 $sql .= ';SELECT SCOPE_IDENTITY()';
182 my $col_info = $self->_resolve_column_info($ident, [map $_->[0], @{$bind}]);
183 if (List::Util::first { $_->{is_auto_increment} } (values %$col_info) ) {
185 my $table = $ident->from;
186 my $identity_insert_on = "SET IDENTITY_INSERT $table ON";
187 my $identity_insert_off = "SET IDENTITY_INSERT $table OFF";
188 $sql = "$identity_insert_on; $sql; $identity_insert_off";
192 return ($sql, $bind);
199 my ($rv, $sth, @bind) = $self->dbh_do($self->can('_dbh_execute'), @_);
200 if ($op eq 'insert') {
201 my ($identity) = $sth->fetchrow_array;
204 if ((not defined $identity) && $self->_using_dynamic_cursors) {
205 ($identity) = $self->_dbh->selectrow_array('select @@identity');
208 $self->_scope_identity($identity);
211 return wantarray ? ($rv, $sth, @bind) : $rv;
214 sub last_insert_id { shift->_scope_identity() }
220 See L<DBIx::Class/CONTRIBUTORS>.
224 You may distribute this code under the same terms as Perl itself.