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 $self->next::method (@_);
216 # keep the dynamic_cursors_support and driver-state in sync
218 my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1;
222 !!$self->_using_dynamic_cursors
224 if ($use_dyncursors) {
226 my $dbh = $self->_dbh;
227 local $dbh->{RaiseError} = 1;
228 local $dbh->{PrintError} = 0;
229 $dbh->do('SELECT @@IDENTITY');
231 $self->throw_exception (
232 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
234 $self->_using_freetds
235 ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
241 $self->_using_dynamic_cursors(1);
242 $self->_identity_method('@@identity');
245 $self->_using_dynamic_cursors(0);
246 $self->_identity_method(undef);
250 $self->_no_scope_identity_query($self->_using_dynamic_cursors
251 ? $self->_using_freetds
255 # freetds is too damn broken, some fixups
256 if ($self->_using_freetds) {
258 # no dynamic cursors starting from 0.83
259 if ($self->_using_dynamic_cursors) {
260 my $fv = $self->_using_freetds_version || 999; # assume large if can't be determined
261 $self->throw_exception(
262 'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 '
263 . "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in "
268 # FreeTDS is too broken wrt execute_for_fetch batching
269 # just disable it outright until things quiet down
270 $self->_disable_odbc_array_ops;
274 =head2 connect_call_use_server_cursors
278 on_connect_call => 'use_server_cursors'
280 May allow multiple active select statements. See
281 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
283 Takes an optional parameter for the value to set the attribute to, default is
286 B<WARNING>: this does not work on all versions of SQL Server, and may lock up
289 At the time of writing, this option only works on Microsoft's Windows drivers,
290 later versions of the ODBC driver and the Native Client driver.
294 sub connect_call_use_server_cursors {
296 my $sql_rowset_size = shift || 2;
298 if ($^O !~ /win32|cygwin/i) {
299 $self->throw_exception('Server cursors only work on Windows platforms at '
300 .'the time of writing.');
303 $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
310 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
314 You may distribute this code under the same terms as Perl itself.