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 CODE ref connect_infos';
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);
101 my ($source, $cols, $data) = @_;
103 my $identity_insert = 0;
106 foreach my $col (@{$cols}) {
107 if ($source->column_info($col)->{is_auto_increment}) {
108 $identity_insert = 1;
113 if ($identity_insert) {
114 my $table = $source->from;
115 $self->_get_dbh->do("SET IDENTITY_INSERT $table ON");
118 $self->next::method(@_);
120 if ($identity_insert) {
121 my $table = $source->from;
122 $self->_get_dbh->do("SET IDENTITY_INSERT $table OFF");
126 sub _prep_for_execute {
128 my ($op, $extra_bind, $ident, $args) = @_;
130 my ($sql, $bind) = $self->next::method (@_);
132 if ($op eq 'insert') {
133 $sql .= ';SELECT SCOPE_IDENTITY()';
135 my $col_info = $self->_resolve_column_info($ident, [map $_->[0], @{$bind}]);
136 if (List::Util::first { $_->{is_auto_increment} } (values %$col_info) ) {
138 my $table = $ident->from;
139 my $identity_insert_on = "SET IDENTITY_INSERT $table ON";
140 my $identity_insert_off = "SET IDENTITY_INSERT $table OFF";
141 $sql = "$identity_insert_on; $sql; $identity_insert_off";
145 return ($sql, $bind);
152 my ($rv, $sth, @bind) = $self->dbh_do($self->can('_dbh_execute'), @_);
153 if ($op eq 'insert') {
154 my ($identity) = $sth->fetchrow_array;
157 if ((not defined $identity) && $self->_using_dynamic_cursors) {
158 ($identity) = $self->_dbh->selectrow_array('select @@identity');
161 $self->_scope_identity($identity);
164 return wantarray ? ($rv, $sth, @bind) : $rv;
167 sub last_insert_id { shift->_scope_identity() }
173 See L<DBIx::Class/CONTRIBUTORS>.
177 You may distribute this code under the same terms as Perl itself.