1 package DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server;
6 DBIx::Class::Storage::DBI::ODBC
7 DBIx::Class::Storage::DBI::MSSQL
10 use Scalar::Util 'reftype';
12 use DBIx::Class::Carp;
15 __PACKAGE__->mk_group_accessors(simple => qw/
16 _using_dynamic_cursors
21 DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server - Support specific
22 to Microsoft SQL Server over ODBC
26 This class implements support specific to Microsoft SQL Server over ODBC. It is
27 loaded automatically by by DBIx::Class::Storage::DBI::ODBC when it detects a
30 Most of the functionality is provided from the superclass
31 L<DBIx::Class::Storage::DBI::MSSQL>.
35 =head2 Basic Linux Setup (Debian)
37 sudo aptitude install tdsodbc libdbd-odbc-perl unixodbc
39 In case it is not already there put the following in C</etc/odbcinst.ini>:
43 Driver = /usr/lib/odbc/libtdsodbc.so
44 Setup = /usr/lib/odbc/libtdsS.so
47 Set your C<$dsn> in L<connect_info|DBIx::Class::Storage::DBI/connect_info> as follows:
49 dbi:ODBC:server=<my.host.name>;port=1433;driver=FreeTDS;tds_version=8.0
51 If you use the EasySoft driver (L<http://www.easysoft.com>):
53 dbi:ODBC:server=<my.host.name>;port=1433;driver=Easysoft ODBC-SQL Server
55 =head2 Basic Windows Setup
57 Use the following C<$dsn> for the Microsoft ODBC driver:
59 dbi:ODBC:driver={SQL Server};server=SERVER\SQL_SERVER_INSTANCE_NAME
61 And for the Native Client:
63 dbi:ODBC:driver={SQL Server Native Client 10.0};server=SERVER\SQL_SERVER_INSTANCE_NAME
65 Go into Control Panel -> System and Security -> Administrative Tools -> Data
66 Sources (ODBC) to check driver names and to set up data sources.
68 Use System DSNs, not User DSNs if you want to use DSNs.
70 If you set up a DSN, use the following C<$dsn> for
71 L<connect_info|DBIx::Class::Storage::DBI/connect_info>:
75 =head1 MULTIPLE ACTIVE STATEMENTS
77 The following options are alternative ways to enable concurrent executing
78 statement support. Each has its own advantages and drawbacks and works on
79 different platforms. Read each section carefully.
81 For more details about using MAS in MSSQL over DBD::ODBC see this excellent
82 document provided by EasySoft:
83 L<http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>.
85 In order of preference, they are:
89 =item * L<mars|/connect_call_use_mars>
91 =item * L<dynamic_cursors|/connect_call_use_dynamic_cursors>
93 =item * L<server_cursors|/connect_call_use_server_cursors>
99 =head2 connect_call_use_mars
103 on_connect_call => 'use_mars'
105 in your connection info, or alternatively specify it directly:
107 Your::Schema->connect (
108 $original_dsn . '; MARS_Connection=Yes',
114 Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result
115 Sets". See L<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?>
116 for more information.
118 This does not work on FreeTDS drivers at the time of this writing, and only
119 works with the Native Client, later versions of the Windows MS ODBC driver, and
124 sub connect_call_use_mars {
127 my $dsn = $self->_dbi_connect_info->[0];
129 if (ref($dsn) eq 'CODE') {
130 $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info');
133 if ($dsn !~ /MARS_Connection=/) {
134 if ($self->_using_freetds) {
135 $self->throw_exception('FreeTDS does not support MARS at the time of '
139 if (exists $self->_server_info->{normalized_dbms_version} &&
140 $self->_server_info->{normalized_dbms_version} < 9) {
141 $self->throw_exception('SQL Server 2005 or later required to use MARS.');
144 if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN
145 warn "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'"
147 $dsn = "dbi:ODBC:dsn=$data_source";
150 $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes";
152 $self->ensure_connected;
156 sub connect_call_use_MARS {
157 carp "'connect_call_use_MARS' has been deprecated, use "
158 ."'connect_call_use_mars' instead.";
159 shift->connect_call_use_mars(@_)
162 =head2 connect_call_use_dynamic_cursors
166 on_connect_call => 'use_dynamic_cursors'
168 Which will add C<< odbc_cursortype => 2 >> to your DBI connection
169 attributes, or alternatively specify the necessary flag directly:
171 Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 })
173 See L<DBD::ODBC/odbc_cursortype> for more information.
175 If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>.
177 This will not work with CODE ref connect_info's.
179 B<WARNING:> on FreeTDS (and maybe some other drivers) this will break
180 C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will be used instead, which on SQL
181 Server 2005 and later will return erroneous results on tables which have an on
182 insert trigger that inserts into another table with an C<IDENTITY> column.
184 B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
185 be visible from a following statement (e.g. a select.)
187 B<WARNING:> FreeTDS versions > 0.82 seem to have completely broken the ODBC
188 protocol. DBIC will not allow dynamic cursor support with such versions to
189 protect your data. Please hassle the authors of FreeTDS to act on the bugs that
190 make their driver not overly usable with DBD::ODBC.
194 sub connect_call_use_dynamic_cursors {
197 if (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) {
199 my $dbi_inf = $self->_dbi_connect_info;
201 $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info')
202 if ref($dbi_inf->[0]) eq 'CODE';
204 # reenter connection information with the attribute re-set
205 $dbi_inf->[3] = {} if @$dbi_inf <= 3;
206 $dbi_inf->[3]{odbc_cursortype} = 2;
208 $self->_dbi_connect_info($dbi_inf);
210 $self->disconnect; # resetting dbi attrs, so have to reconnect
211 $self->ensure_connected;
215 sub _run_connection_actions {
218 $self->next::method (@_);
220 # keep the dynamic_cursors_support and driver-state in sync
222 my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1;
226 !!$self->_using_dynamic_cursors
228 if ($use_dyncursors) {
230 my $dbh = $self->_dbh;
231 local $dbh->{RaiseError} = 1;
232 local $dbh->{PrintError} = 0;
233 $dbh->do('SELECT @@IDENTITY');
235 $self->throw_exception (
236 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
238 $self->_using_freetds
239 ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
245 $self->_using_dynamic_cursors(1);
246 $self->_identity_method('@@identity');
249 $self->_using_dynamic_cursors(0);
250 $self->_identity_method(undef);
254 $self->_no_scope_identity_query($self->_using_dynamic_cursors
255 ? $self->_using_freetds
259 # freetds is too damn broken, some fixups
260 if ($self->_using_freetds) {
262 # no dynamic cursors starting from 0.83
263 if ($self->_using_dynamic_cursors) {
264 my $fv = $self->_using_freetds_version || 999; # assume large if can't be determined
265 $self->throw_exception(
266 'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 '
267 . "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in "
272 # FreeTDS is too broken wrt execute_for_fetch batching
273 # just disable it outright until things quiet down
274 $self->_disable_odbc_array_ops;
278 =head2 connect_call_use_server_cursors
282 on_connect_call => 'use_server_cursors'
284 May allow multiple active select statements. See
285 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
287 Takes an optional parameter for the value to set the attribute to, default is
290 B<WARNING>: this does not work on all versions of SQL Server, and may lock up
293 At the time of writing, this option only works on Microsoft's Windows drivers,
294 later versions of the ODBC driver and the Native Client driver.
298 sub connect_call_use_server_cursors {
300 my $sql_rowset_size = shift || 2;
302 if ($^O !~ /win32|cygwin/i) {
303 $self->throw_exception('Server cursors only work on Windows platforms at '
304 .'the time of writing.');
307 $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
314 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
318 You may distribute this code under the same terms as Perl itself.