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 In order of preference, they are:
85 =item * L<mars|/connect_call_use_mars>
87 =item * L<dynamic_cursors|/connect_call_use_dynamic_cursors>
89 =item * L<server_cursors|/connect_call_use_server_cursors>
95 =head2 connect_call_use_mars
99 on_connect_call => 'use_mars'
101 in your connection info, or alternatively specify it directly:
103 Your::Schema->connect (
104 $original_dsn . '; MARS_Connection=Yes',
110 Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result
111 Sets". See L<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?>
112 for more information.
114 This does not work on FreeTDS drivers at the time of this writing, and only
115 works with the Native Client, later versions of the Windows MS ODBC driver, and
120 sub connect_call_use_mars {
123 my $dsn = $self->_dbi_connect_info->[0];
125 if (ref($dsn) eq 'CODE') {
126 $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info');
129 if ($dsn !~ /MARS_Connection=/) {
130 if ($self->_using_freetds) {
131 my $v = $self->_using_freetds_version;
132 $self->throw_exception("FreeTDS 0.91 or later required for MARS support, you have $v")
136 if (exists $self->_server_info->{normalized_dbms_version} &&
137 $self->_server_info->{normalized_dbms_version} < 9) {
138 $self->throw_exception('SQL Server 2005 or later required to use MARS.');
141 if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN
142 warn "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'"
144 $dsn = "dbi:ODBC:dsn=$data_source";
147 $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes";
149 $self->ensure_connected;
153 sub connect_call_use_MARS {
154 carp "'connect_call_use_MARS' has been deprecated, use "
155 ."'connect_call_use_mars' instead.";
156 shift->connect_call_use_mars(@_)
159 =head2 connect_call_use_dynamic_cursors
163 on_connect_call => 'use_dynamic_cursors'
165 Which will add C<< odbc_cursortype => 2 >> to your DBI connection
166 attributes, or alternatively specify the necessary flag directly:
168 Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 })
170 See L<DBD::ODBC/odbc_cursortype> for more information.
172 If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>.
174 This will not work with CODE ref connect_info's.
176 B<WARNING:> on FreeTDS (and maybe some other drivers) this will break
177 C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will be used instead, which on SQL
178 Server 2005 and later will return erroneous results on tables which have an on
179 insert trigger that inserts into another table with an C<IDENTITY> column.
181 B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
182 be visible from a following statement (e.g. a select.)
184 B<WARNING:> FreeTDS versions > 0.82 seem to have completely broken the ODBC
185 protocol. DBIC will not allow dynamic cursor support with such versions to
186 protect your data. Please hassle the authors of FreeTDS to act on the bugs that
187 make their driver not overly usable with DBD::ODBC.
191 sub connect_call_use_dynamic_cursors {
194 if (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) {
196 my $dbi_inf = $self->_dbi_connect_info;
198 $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info')
199 if ref($dbi_inf->[0]) eq 'CODE';
201 # reenter connection information with the attribute re-set
202 $dbi_inf->[3] = {} if @$dbi_inf <= 3;
203 $dbi_inf->[3]{odbc_cursortype} = 2;
205 $self->_dbi_connect_info($dbi_inf);
207 $self->disconnect; # resetting dbi attrs, so have to reconnect
208 $self->ensure_connected;
212 sub _run_connection_actions {
215 # keep the dynamic_cursors_support and driver-state in sync
217 my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1;
221 !!$self->_using_dynamic_cursors
223 if ($use_dyncursors) {
225 my $dbh = $self->_dbh;
226 local $dbh->{RaiseError} = 1;
227 local $dbh->{PrintError} = 0;
228 $dbh->do('SELECT @@IDENTITY');
230 $self->throw_exception (
231 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
233 $self->_using_freetds
234 ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
240 $self->_using_dynamic_cursors(1);
241 $self->_identity_method('@@identity');
242 $self->_no_scope_identity_query($self->_using_freetds);
245 $self->_using_dynamic_cursors(0);
246 $self->_identity_method(undef);
247 $self->_no_scope_identity_query(undef);
251 $self->next::method (@_);
253 # freetds is too damn broken, some fixups
254 if ($self->_using_freetds) {
256 # no dynamic cursors starting from 0.83
257 if ($self->_using_dynamic_cursors) {
258 my $fv = $self->_using_freetds_version || 999; # assume large if can't be determined
259 $self->throw_exception(
260 'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 '
261 . "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in "
266 # FreeTDS is too broken wrt execute_for_fetch batching
267 # just disable it outright until things quiet down
268 $self->_get_dbh->{odbc_disable_array_operations} = 1;
272 =head2 connect_call_use_server_cursors
276 on_connect_call => 'use_server_cursors'
278 May allow multiple active select statements. See
279 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
281 Takes an optional parameter for the value to set the attribute to, default is
284 B<WARNING>: this does not work on all versions of SQL Server, and may lock up
287 At the time of writing, this option only works on Microsoft's Windows drivers,
288 later versions of the ODBC driver and the Native Client driver.
292 sub connect_call_use_server_cursors {
294 my $sql_rowset_size = shift || 2;
296 if ($^O !~ /win32|cygwin/i) {
297 $self->throw_exception('Server cursors only work on Windows platforms at '
298 .'the time of writing.');
301 $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
308 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
312 You may distribute this code under the same terms as Perl itself.