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 $self->throw_exception('FreeTDS does not support MARS at the time of '
135 if (exists $self->_server_info->{normalized_dbms_version} &&
136 $self->_server_info->{normalized_dbms_version} < 9) {
137 $self->throw_exception('SQL Server 2005 or later required to use MARS.');
140 if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN
141 warn "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'"
143 $dsn = "dbi:ODBC:dsn=$data_source";
146 $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes";
148 $self->ensure_connected;
152 sub connect_call_use_MARS {
153 carp "'connect_call_use_MARS' has been deprecated, use "
154 ."'connect_call_use_mars' instead.";
155 shift->connect_call_use_mars(@_)
158 =head2 connect_call_use_dynamic_cursors
162 on_connect_call => 'use_dynamic_cursors'
164 Which will add C<< odbc_cursortype => 2 >> to your DBI connection
165 attributes, or alternatively specify the necessary flag directly:
167 Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 })
169 See L<DBD::ODBC/odbc_cursortype> for more information.
171 If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>.
173 This will not work with CODE ref connect_info's.
175 B<WARNING:> on FreeTDS (and maybe some other drivers) this will break
176 C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will be used instead, which on SQL
177 Server 2005 and later will return erroneous results on tables which have an on
178 insert trigger that inserts into another table with an C<IDENTITY> column.
180 B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
181 be visible from a following statement (e.g. a select.)
183 B<WARNING:> FreeTDS versions > 0.82 seem to have completely broken the ODBC
184 protocol. DBIC will not allow dynamic cursor support with such versions to
185 protect your data. Please hassle the authors of FreeTDS to act on the bugs that
186 make their driver not overly usable with DBD::ODBC.
190 sub connect_call_use_dynamic_cursors {
193 if (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) {
195 my $dbi_inf = $self->_dbi_connect_info;
197 $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info')
198 if ref($dbi_inf->[0]) eq 'CODE';
200 # reenter connection information with the attribute re-set
201 $dbi_inf->[3] = {} if @$dbi_inf <= 3;
202 $dbi_inf->[3]{odbc_cursortype} = 2;
204 $self->_dbi_connect_info($dbi_inf);
206 $self->disconnect; # resetting dbi attrs, so have to reconnect
207 $self->ensure_connected;
211 sub _run_connection_actions {
214 # keep the dynamic_cursors_support and driver-state in sync
216 my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1;
220 !!$self->_using_dynamic_cursors
222 if ($use_dyncursors) {
224 my $dbh = $self->_dbh;
225 local $dbh->{RaiseError} = 1;
226 local $dbh->{PrintError} = 0;
227 $dbh->do('SELECT @@IDENTITY');
229 $self->throw_exception (
230 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
232 $self->_using_freetds
233 ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
239 $self->_using_dynamic_cursors(1);
240 $self->_identity_method('@@identity');
241 $self->_no_scope_identity_query($self->_using_freetds);
244 $self->_using_dynamic_cursors(0);
245 $self->_identity_method(undef);
246 $self->_no_scope_identity_query(undef);
250 $self->next::method (@_);
252 # freetds is too damn broken, some fixups
253 if ($self->_using_freetds) {
255 # no dynamic cursors starting from 0.83
256 if ($self->_using_dynamic_cursors) {
257 my $fv = $self->_using_freetds_version || 999; # assume large if can't be determined
258 $self->throw_exception(
259 'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 '
260 . "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in "
265 # FreeTDS is too broken wrt execute_for_fetch batching
266 # just disable it outright until things quiet down
267 $self->_get_dbh->{odbc_disable_array_operations} = 1;
271 =head2 connect_call_use_server_cursors
275 on_connect_call => 'use_server_cursors'
277 May allow multiple active select statements. See
278 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
280 Takes an optional parameter for the value to set the attribute to, default is
283 B<WARNING>: this does not work on all versions of SQL Server, and may lock up
286 At the time of writing, this option only works on Microsoft's Windows drivers,
287 later versions of the ODBC driver and the Native Client driver.
291 sub connect_call_use_server_cursors {
293 my $sql_rowset_size = shift || 2;
295 if ($^O !~ /win32|cygwin/i) {
296 $self->throw_exception('Server cursors only work on Windows platforms at '
297 .'the time of writing.');
300 $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
307 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
311 You may distribute this code under the same terms as Perl itself.