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::_Util 'dbic_internal_try';
13 use DBIx::Class::Carp;
16 __PACKAGE__->mk_group_accessors(simple => qw/
17 _using_dynamic_cursors
22 DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server - Support specific
23 to Microsoft SQL Server over ODBC
27 This class implements support specific to Microsoft SQL Server over ODBC. It is
28 loaded automatically by DBIx::Class::Storage::DBI::ODBC when it detects a
31 Most of the functionality is provided from the superclass
32 L<DBIx::Class::Storage::DBI::MSSQL>.
36 =head2 Basic Linux Setup (Debian)
38 sudo aptitude install tdsodbc libdbd-odbc-perl unixodbc
40 In case it is not already there put the following (adjust for non-64bit arch) in
45 Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
46 Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
49 Set your C<$dsn> in L<connect_info|DBIx::Class::Storage::DBI/connect_info> as follows:
51 dbi:ODBC:server=<my.host.name>;port=1433;driver=FreeTDS;tds_version=8.0
53 If you use the EasySoft driver (L<http://www.easysoft.com>):
55 dbi:ODBC:server=<my.host.name>;port=1433;driver=Easysoft ODBC-SQL Server
57 =head2 Basic Windows Setup
59 Use the following C<$dsn> for the Microsoft ODBC driver:
61 dbi:ODBC:driver={SQL Server};server=SERVER\SQL_SERVER_INSTANCE_NAME
63 And for the Native Client:
65 dbi:ODBC:driver={SQL Server Native Client 10.0};server=SERVER\SQL_SERVER_INSTANCE_NAME
67 Go into Control Panel -> System and Security -> Administrative Tools -> Data
68 Sources (ODBC) to check driver names and to set up data sources.
70 Use System DSNs, not User DSNs if you want to use DSNs.
72 If you set up a DSN, use the following C<$dsn> for
73 L<connect_info|DBIx::Class::Storage::DBI/connect_info>:
77 =head1 MULTIPLE ACTIVE STATEMENTS
79 The following options are alternative ways to enable concurrent executing
80 statement support. Each has its own advantages and drawbacks and works on
81 different platforms. Read each section carefully.
83 For more details about using MAS in MSSQL over DBD::ODBC see this excellent
84 document provided by EasySoft:
85 L<http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>.
87 In order of preference, they are:
91 =item * L<mars|/connect_call_use_mars>
93 =item * L<dynamic_cursors|/connect_call_use_dynamic_cursors>
95 =item * L<server_cursors|/connect_call_use_server_cursors>
101 =head2 connect_call_use_mars
105 on_connect_call => 'use_mars'
107 in your connection info, or alternatively specify it directly:
109 Your::Schema->connect (
110 $original_dsn . '; MARS_Connection=Yes',
116 Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result
117 Sets". See L<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?>
118 for more information.
120 This does not work on FreeTDS drivers at the time of this writing, and only
121 works with the Native Client, later versions of the Windows MS ODBC driver, and
126 sub connect_call_use_mars {
129 my $dsn = $self->_dbi_connect_info->[0];
131 if (ref($dsn) eq 'CODE') {
132 $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info');
135 if ($dsn !~ /MARS_Connection=/) {
136 if ($self->_using_freetds) {
137 $self->throw_exception('FreeTDS does not support MARS at the time of '
141 if (exists $self->_server_info->{normalized_dbms_version} &&
142 $self->_server_info->{normalized_dbms_version} < 9) {
143 $self->throw_exception('SQL Server 2005 or later required to use MARS.');
146 if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN
147 carp_unique "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'"
149 $dsn = "dbi:ODBC:dsn=$data_source";
152 $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes";
154 $self->ensure_connected;
158 sub connect_call_use_MARS {
159 carp "'connect_call_use_MARS' has been deprecated, use "
160 ."'connect_call_use_mars' instead.";
161 shift->connect_call_use_mars(@_)
164 =head2 connect_call_use_dynamic_cursors
168 on_connect_call => 'use_dynamic_cursors'
170 Which will add C<< odbc_cursortype => 2 >> to your DBI connection
171 attributes, or alternatively specify the necessary flag directly:
173 Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 })
175 See L<DBD::ODBC/odbc_cursortype> for more information.
177 If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>.
179 This will not work with CODE ref connect_info's.
181 B<WARNING:> on FreeTDS (and maybe some other drivers) this will break
182 C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will be used instead, which on SQL
183 Server 2005 and later will return erroneous results on tables which have an on
184 insert trigger that inserts into another table with an C<IDENTITY> column.
186 B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
187 be visible from a following statement (e.g. a select.)
189 B<WARNING:> FreeTDS versions > 0.82 seem to have completely broken the ODBC
190 protocol. DBIC will not allow dynamic cursor support with such versions to
191 protect your data. Please hassle the authors of FreeTDS to act on the bugs that
192 make their driver not overly usable with DBD::ODBC.
196 sub connect_call_use_dynamic_cursors {
199 if (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) {
201 my $dbi_inf = $self->_dbi_connect_info;
203 $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info')
204 if ref($dbi_inf->[0]) eq 'CODE';
206 # reenter connection information with the attribute re-set
207 $dbi_inf->[3] = {} if @$dbi_inf <= 3;
208 $dbi_inf->[3]{odbc_cursortype} = 2;
210 $self->_dbi_connect_info($dbi_inf);
212 $self->disconnect; # resetting dbi attrs, so have to reconnect
213 $self->ensure_connected;
217 sub _run_connection_actions {
220 $self->next::method (@_);
222 # keep the dynamic_cursors_support and driver-state in sync
224 my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1;
228 !!$self->_using_dynamic_cursors
230 if ($use_dyncursors) {
232 my $dbh = $self->_dbh;
233 local $dbh->{RaiseError} = 1;
234 local $dbh->{PrintError} = 0;
235 $dbh->do('SELECT @@IDENTITY');
237 $self->throw_exception (
238 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
240 $self->_using_freetds
241 ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
247 $self->_using_dynamic_cursors(1);
248 $self->_identity_method('@@identity');
251 $self->_using_dynamic_cursors(0);
252 $self->_identity_method(undef);
256 $self->_no_scope_identity_query($self->_using_dynamic_cursors
257 ? $self->_using_freetds
261 # freetds is too damn broken, some fixups
262 if ($self->_using_freetds) {
264 # no dynamic cursors starting from 0.83
265 if ($self->_using_dynamic_cursors) {
266 my $fv = $self->_using_freetds_version || 999; # assume large if can't be determined
267 $self->throw_exception(
268 'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 '
269 . "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in "
274 # FreeTDS is too broken wrt execute_for_fetch batching
275 # just disable it outright until things quiet down
276 $self->_disable_odbc_array_ops;
280 =head2 connect_call_use_server_cursors
284 on_connect_call => 'use_server_cursors'
286 May allow multiple active select statements. See
287 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
289 Takes an optional parameter for the value to set the attribute to, default is
292 B<WARNING>: this does not work on all versions of SQL Server, and may lock up
295 At the time of writing, this option only works on Microsoft's Windows drivers,
296 later versions of the ODBC driver and the Native Client driver.
300 sub connect_call_use_server_cursors {
302 my $sql_rowset_size = shift || 2;
304 if ($^O !~ /win32|cygwin/i) {
305 $self->throw_exception('Server cursors only work on Windows platforms at '
306 .'the time of writing.');
309 $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
312 =head1 FURTHER QUESTIONS?
314 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
316 =head1 COPYRIGHT AND LICENSE
318 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
319 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
320 redistribute it and/or modify it under the same terms as the
321 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.