1 package DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server;
5 use base qw/DBIx::Class::Storage::DBI::MSSQL/;
7 use Scalar::Util 'reftype';
12 __PACKAGE__->mk_group_accessors(simple => qw/
13 _using_dynamic_cursors
18 DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server - Support specific
19 to Microsoft SQL Server over ODBC
23 This class implements support specific to Microsoft SQL Server over ODBC. It is
24 loaded automatically by by DBIx::Class::Storage::DBI::ODBC when it detects a
27 Most of the functionality is provided from the superclass
28 L<DBIx::Class::Storage::DBI::MSSQL>.
32 =head2 Basic Linux Setup (Debian)
34 sudo aptitude install tdsodbc libdbd-odbc-perl unixodbc
36 In case it is not already there put the following in C</etc/odbcinst.ini>:
40 Driver = /usr/lib/odbc/libtdsodbc.so
41 Setup = /usr/lib/odbc/libtdsS.so
44 Set your C<$dsn> in L<connect_info|DBIx::Class::Storage::DBI/connect_info> as follows:
46 dbi:ODBC:server=<my.host.name>;port=1433;driver=FreeTDS;tds_version=8.0
48 If you use the EasySoft driver (L<http://www.easysoft.com>):
50 dbi:ODBC:server=<my.host.name>;port=1433;driver=Easysoft ODBC-SQL Server
52 =head2 Basic Windows Setup
54 Use the following C<$dsn> for the Microsoft ODBC driver:
56 dbi:ODBC:driver={SQL Server};server=SERVER\SQL_SERVER_INSTANCE_NAME
58 And for the Native Client:
60 dbi:ODBC:driver={SQL Server Native Client 10.0};server=SERVER\SQL_SERVER_INSTANCE_NAME
62 Go into Control Panel -> System and Security -> Administrative Tools -> Data
63 Sources (ODBC) to check driver names and to set up data sources.
65 Use System DSNs, not User DSNs if you want to use DSNs.
67 If you set up a DSN, use the following C<$dsn> for
68 L<connect_info|DBIx::Class::Storage::DBI/connect_info>:
72 =head1 MULTIPLE ACTIVE STATEMENTS
74 The following options are alternative ways to enable concurrent executing
75 statement support. Each has its own advantages and drawbacks and works on
76 different platforms. Read each section carefully.
78 In order of preference, they are:
82 =item * L<mars|/connect_call_use_mars>
84 =item * L<dynamic_cursors|/connect_call_use_dynamic_cursors>
86 =item * L<server_cursors|/connect_call_use_server_cursors>
92 =head2 connect_call_use_mars
96 on_connect_call => 'use_mars'
98 in your connection info, or alternatively specify it directly:
100 Your::Schema->connect (
101 $original_dsn . '; MARS_Connection=Yes',
107 Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result
108 Sets". See L<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?>
109 for more information.
111 This does not work on FreeTDS drivers at the time of this writing, and only
112 works with the Native Client, later versions of the Windows MS ODBC driver, and
117 sub connect_call_use_mars {
120 my $dsn = $self->_dbi_connect_info->[0];
122 if (ref($dsn) eq 'CODE') {
123 $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info');
126 if ($dsn !~ /MARS_Connection=/) {
127 if ($self->using_freetds) {
128 $self->throw_exception('FreeTDS does not support MARS at the time of '
132 if (exists $self->_server_info->{normalized_dbms_version} &&
133 $self->_server_info->{normalized_dbms_version} < 9) {
134 $self->throw_exception('SQL Server 2005 or later required to use MARS.');
137 if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN
138 warn "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'"
140 $dsn = "dbi:ODBC:dsn=$data_source";
143 $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes";
145 $self->ensure_connected;
149 sub connect_call_use_MARS {
150 carp "'connect_call_use_MARS' has been deprecated, use "
151 ."'connect_call_use_mars' instead.";
152 shift->connect_call_use_mars(@_)
155 =head2 connect_call_use_dynamic_cursors
159 on_connect_call => 'use_dynamic_cursors'
161 Which will add C<< odbc_cursortype => 2 >> to your DBI connection
162 attributes, or alternatively specify the necessary flag directly:
164 Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 })
166 See L<DBD::ODBC/odbc_cursortype> for more information.
168 If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>.
170 This will not work with CODE ref connect_info's.
172 B<WARNING:> on FreeTDS (and maybe some other drivers) this will break
173 C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will be used instead, which on SQL
174 Server 2005 and later will return erroneous results on tables which have an on
175 insert trigger that inserts into another table with an C<IDENTITY> column.
177 B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
178 be visible from a following statement (e.g. a select.)
182 sub connect_call_use_dynamic_cursors {
185 if (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) {
187 my $dbi_inf = $self->_dbi_connect_info;
189 $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info')
190 if ref($dbi_inf->[0]) eq 'CODE';
192 # reenter connection information with the attribute re-set
193 $dbi_inf->[3] = {} if @$dbi_inf <= 3;
194 $dbi_inf->[3]{odbc_cursortype} = 2;
196 $self->_dbi_connect_info($dbi_inf);
198 $self->disconnect; # resetting dbi attrs, so have to reconnect
199 $self->ensure_connected;
203 sub _run_connection_actions {
206 # keep the dynamic_cursors_support and driver-state in sync
208 my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1;
212 !!$self->_using_dynamic_cursors
214 if ($use_dyncursors) {
216 my $dbh = $self->_dbh;
217 local $dbh->{RaiseError} = 1;
218 local $dbh->{PrintError} = 0;
219 $dbh->do('SELECT @@IDENTITY');
221 $self->throw_exception (
222 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
225 ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
231 $self->_using_dynamic_cursors(1);
232 $self->_identity_method('@@identity');
235 $self->_using_dynamic_cursors(0);
236 $self->_identity_method(undef);
240 $self->next::method (@_);
243 =head2 connect_call_use_server_cursors
247 on_connect_call => 'use_server_cursors'
249 May allow multiple active select statements. See
250 L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
252 Takes an optional parameter for the value to set the attribute to, default is
255 B<WARNING>: this does not work on all versions of SQL Server, and may lock up
258 At the time of writing, this option only works on Microsoft's Windows drivers,
259 later versions of the ODBC driver and the Native Client driver.
263 sub connect_call_use_server_cursors {
265 my $sql_rowset_size = shift || 2;
267 if ($^O !~ /win32|cygwin/i) {
268 $self->throw_exception('Server cursors only work on Windows platforms at '
269 .'the time of writing.');
272 $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
277 Tries to determine, to the best of our ability, whether or not you are using the
278 FreeTDS driver with L<DBD::ODBC>.
285 my $dsn = $self->_dbi_connect_info->[0];
287 $dsn = '' if ref $dsn eq 'CODE';
289 return 1 if $dsn =~ /driver=FreeTDS/i
290 || ($self->_dbh_get_info(6)||'') =~ /tdsodbc/i;
299 See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
303 You may distribute this code under the same terms as Perl itself.