Commit | Line | Data |
c1cac633 |
1 | package DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server; |
2 | use strict; |
3 | use warnings; |
4 | |
aca3b4c3 |
5 | use base qw/ |
6 | DBIx::Class::Storage::DBI::ODBC |
7 | DBIx::Class::Storage::DBI::MSSQL |
8 | /; |
2ad62d97 |
9 | use mro 'c3'; |
6298a324 |
10 | use Scalar::Util 'reftype'; |
ed7ab0f4 |
11 | use Try::Tiny; |
70c28808 |
12 | use DBIx::Class::Carp; |
fd323bf1 |
13 | use namespace::clean; |
c1cac633 |
14 | |
7b1b2582 |
15 | __PACKAGE__->mk_group_accessors(simple => qw/ |
16 | _using_dynamic_cursors |
17 | /); |
c1cac633 |
18 | |
19 | =head1 NAME |
20 | |
a89c6fc0 |
21 | DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server - Support specific |
22 | to Microsoft SQL Server over ODBC |
c1cac633 |
23 | |
24 | =head1 DESCRIPTION |
25 | |
5a77aa8b |
26 | This class implements support specific to Microsoft SQL Server over ODBC. It is |
863b0539 |
27 | loaded automatically by DBIx::Class::Storage::DBI::ODBC when it detects a |
5a77aa8b |
28 | MSSQL back-end. |
c1cac633 |
29 | |
5a77aa8b |
30 | Most of the functionality is provided from the superclass |
31 | L<DBIx::Class::Storage::DBI::MSSQL>. |
c1cac633 |
32 | |
dcc7ddff |
33 | =head1 USAGE NOTES |
34 | |
35 | =head2 Basic Linux Setup (Debian) |
36 | |
37 | sudo aptitude install tdsodbc libdbd-odbc-perl unixodbc |
38 | |
a5280453 |
39 | In case it is not already there put the following (adjust for non-64bit arch) in |
40 | C</etc/odbcinst.ini>: |
dcc7ddff |
41 | |
42 | [FreeTDS] |
43 | Description = FreeTDS |
a5280453 |
44 | Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so |
45 | Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so |
dcc7ddff |
46 | UsageCount = 1 |
47 | |
48 | Set your C<$dsn> in L<connect_info|DBIx::Class::Storage::DBI/connect_info> as follows: |
49 | |
50 | dbi:ODBC:server=<my.host.name>;port=1433;driver=FreeTDS;tds_version=8.0 |
51 | |
52 | If you use the EasySoft driver (L<http://www.easysoft.com>): |
53 | |
54 | dbi:ODBC:server=<my.host.name>;port=1433;driver=Easysoft ODBC-SQL Server |
55 | |
56 | =head2 Basic Windows Setup |
57 | |
58 | Use the following C<$dsn> for the Microsoft ODBC driver: |
59 | |
60 | dbi:ODBC:driver={SQL Server};server=SERVER\SQL_SERVER_INSTANCE_NAME |
61 | |
62 | And for the Native Client: |
63 | |
64 | dbi:ODBC:driver={SQL Server Native Client 10.0};server=SERVER\SQL_SERVER_INSTANCE_NAME |
65 | |
66 | Go into Control Panel -> System and Security -> Administrative Tools -> Data |
67 | Sources (ODBC) to check driver names and to set up data sources. |
68 | |
69 | Use System DSNs, not User DSNs if you want to use DSNs. |
70 | |
71 | If you set up a DSN, use the following C<$dsn> for |
72 | L<connect_info|DBIx::Class::Storage::DBI/connect_info>: |
73 | |
74 | dbi:ODBC:dsn=MY_DSN |
75 | |
7b1b2582 |
76 | =head1 MULTIPLE ACTIVE STATEMENTS |
77 | |
78 | The following options are alternative ways to enable concurrent executing |
384b8bce |
79 | statement support. Each has its own advantages and drawbacks and works on |
80 | different platforms. Read each section carefully. |
81 | |
2b6d7e87 |
82 | For more details about using MAS in MSSQL over DBD::ODBC see this excellent |
83 | document provided by EasySoft: |
84 | L<http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>. |
85 | |
384b8bce |
86 | In order of preference, they are: |
87 | |
88 | =over 8 |
89 | |
1db83fb9 |
90 | =item * L<mars|/connect_call_use_mars> |
384b8bce |
91 | |
1db83fb9 |
92 | =item * L<dynamic_cursors|/connect_call_use_dynamic_cursors> |
384b8bce |
93 | |
1db83fb9 |
94 | =item * L<server_cursors|/connect_call_use_server_cursors> |
384b8bce |
95 | |
96 | =back |
97 | |
98 | =head1 METHODS |
99 | |
100 | =head2 connect_call_use_mars |
101 | |
102 | Use as: |
103 | |
104 | on_connect_call => 'use_mars' |
105 | |
1db83fb9 |
106 | in your connection info, or alternatively specify it directly: |
107 | |
108 | Your::Schema->connect ( |
109 | $original_dsn . '; MARS_Connection=Yes', |
110 | $user, |
111 | $pass, |
112 | \%attrs, |
113 | ) |
114 | |
384b8bce |
115 | Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result |
116 | Sets". See L<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?> |
117 | for more information. |
118 | |
119 | This does not work on FreeTDS drivers at the time of this writing, and only |
120 | works with the Native Client, later versions of the Windows MS ODBC driver, and |
121 | the Easysoft driver. |
122 | |
123 | =cut |
124 | |
125 | sub connect_call_use_mars { |
126 | my $self = shift; |
127 | |
128 | my $dsn = $self->_dbi_connect_info->[0]; |
129 | |
130 | if (ref($dsn) eq 'CODE') { |
131 | $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info'); |
132 | } |
133 | |
134 | if ($dsn !~ /MARS_Connection=/) { |
aca3b4c3 |
135 | if ($self->_using_freetds) { |
94f9fbef |
136 | $self->throw_exception('FreeTDS does not support MARS at the time of ' |
137 | .'writing.'); |
384b8bce |
138 | } |
139 | |
140 | if (exists $self->_server_info->{normalized_dbms_version} && |
141 | $self->_server_info->{normalized_dbms_version} < 9) { |
142 | $self->throw_exception('SQL Server 2005 or later required to use MARS.'); |
143 | } |
144 | |
145 | if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN |
2ad0cbfb |
146 | carp_unique "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'" |
a469c045 |
147 | ." for MARS\n"; |
148 | $dsn = "dbi:ODBC:dsn=$data_source"; |
384b8bce |
149 | } |
150 | |
151 | $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes"; |
152 | $self->disconnect; |
153 | $self->ensure_connected; |
154 | } |
155 | } |
156 | |
157 | sub connect_call_use_MARS { |
158 | carp "'connect_call_use_MARS' has been deprecated, use " |
159 | ."'connect_call_use_mars' instead."; |
160 | shift->connect_call_use_mars(@_) |
161 | } |
7b1b2582 |
162 | |
163 | =head2 connect_call_use_dynamic_cursors |
164 | |
165 | Use as: |
166 | |
167 | on_connect_call => 'use_dynamic_cursors' |
168 | |
1db83fb9 |
169 | Which will add C<< odbc_cursortype => 2 >> to your DBI connection |
170 | attributes, or alternatively specify the necessary flag directly: |
7b1b2582 |
171 | |
1db83fb9 |
172 | Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 }) |
7b1b2582 |
173 | |
1db83fb9 |
174 | See L<DBD::ODBC/odbc_cursortype> for more information. |
7b1b2582 |
175 | |
41dd5d30 |
176 | If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>. |
177 | |
178 | This will not work with CODE ref connect_info's. |
7b1b2582 |
179 | |
1db83fb9 |
180 | B<WARNING:> on FreeTDS (and maybe some other drivers) this will break |
181 | C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will be used instead, which on SQL |
182 | Server 2005 and later will return erroneous results on tables which have an on |
183 | insert trigger that inserts into another table with an C<IDENTITY> column. |
7b1b2582 |
184 | |
9ffaf8a3 |
185 | B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not |
186 | be visible from a following statement (e.g. a select.) |
187 | |
25d3127d |
188 | B<WARNING:> FreeTDS versions > 0.82 seem to have completely broken the ODBC |
189 | protocol. DBIC will not allow dynamic cursor support with such versions to |
190 | protect your data. Please hassle the authors of FreeTDS to act on the bugs that |
191 | make their driver not overly usable with DBD::ODBC. |
192 | |
7b1b2582 |
193 | =cut |
194 | |
195 | sub connect_call_use_dynamic_cursors { |
196 | my $self = shift; |
197 | |
9ffaf8a3 |
198 | if (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) { |
7b1b2582 |
199 | |
9ffaf8a3 |
200 | my $dbi_inf = $self->_dbi_connect_info; |
201 | |
202 | $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info') |
203 | if ref($dbi_inf->[0]) eq 'CODE'; |
7b1b2582 |
204 | |
1db83fb9 |
205 | # reenter connection information with the attribute re-set |
9ffaf8a3 |
206 | $dbi_inf->[3] = {} if @$dbi_inf <= 3; |
207 | $dbi_inf->[3]{odbc_cursortype} = 2; |
208 | |
209 | $self->_dbi_connect_info($dbi_inf); |
210 | |
75517ea9 |
211 | $self->disconnect; # resetting dbi attrs, so have to reconnect |
212 | $self->ensure_connected; |
7b1b2582 |
213 | } |
214 | } |
215 | |
1db83fb9 |
216 | sub _run_connection_actions { |
7b1b2582 |
217 | my $self = shift; |
218 | |
23eae175 |
219 | $self->next::method (@_); |
220 | |
1db83fb9 |
221 | # keep the dynamic_cursors_support and driver-state in sync |
222 | # on every reconnect |
223 | my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1; |
1a58752c |
224 | if ( |
1db83fb9 |
225 | $use_dyncursors |
226 | xor |
227 | !!$self->_using_dynamic_cursors |
1a58752c |
228 | ) { |
1db83fb9 |
229 | if ($use_dyncursors) { |
230 | try { |
231 | my $dbh = $self->_dbh; |
232 | local $dbh->{RaiseError} = 1; |
233 | local $dbh->{PrintError} = 0; |
234 | $dbh->do('SELECT @@IDENTITY'); |
235 | } catch { |
236 | $self->throw_exception ( |
237 | 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).' |
238 | . ( |
aca3b4c3 |
239 | $self->_using_freetds |
1db83fb9 |
240 | ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.' |
241 | : '' |
242 | ) |
243 | ); |
244 | }; |
245 | |
246 | $self->_using_dynamic_cursors(1); |
247 | $self->_identity_method('@@identity'); |
248 | } |
249 | else { |
250 | $self->_using_dynamic_cursors(0); |
251 | $self->_identity_method(undef); |
252 | } |
384b8bce |
253 | } |
1db83fb9 |
254 | |
23eae175 |
255 | $self->_no_scope_identity_query($self->_using_dynamic_cursors |
256 | ? $self->_using_freetds |
257 | : undef |
258 | ); |
25d3127d |
259 | |
260 | # freetds is too damn broken, some fixups |
aca3b4c3 |
261 | if ($self->_using_freetds) { |
25d3127d |
262 | |
263 | # no dynamic cursors starting from 0.83 |
264 | if ($self->_using_dynamic_cursors) { |
aca3b4c3 |
265 | my $fv = $self->_using_freetds_version || 999; # assume large if can't be determined |
25d3127d |
266 | $self->throw_exception( |
267 | 'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 ' |
268 | . "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in " |
269 | . 'their driver.' |
270 | ) if $fv > 0.82 |
271 | } |
272 | |
273 | # FreeTDS is too broken wrt execute_for_fetch batching |
274 | # just disable it outright until things quiet down |
11f7049f |
275 | $self->_disable_odbc_array_ops; |
25d3127d |
276 | } |
7b1b2582 |
277 | } |
278 | |
279 | =head2 connect_call_use_server_cursors |
280 | |
281 | Use as: |
282 | |
283 | on_connect_call => 'use_server_cursors' |
284 | |
285 | May allow multiple active select statements. See |
286 | L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information. |
287 | |
288 | Takes an optional parameter for the value to set the attribute to, default is |
289 | C<2>. |
290 | |
291 | B<WARNING>: this does not work on all versions of SQL Server, and may lock up |
292 | your database! |
293 | |
384b8bce |
294 | At the time of writing, this option only works on Microsoft's Windows drivers, |
295 | later versions of the ODBC driver and the Native Client driver. |
296 | |
7b1b2582 |
297 | =cut |
298 | |
299 | sub connect_call_use_server_cursors { |
300 | my $self = shift; |
301 | my $sql_rowset_size = shift || 2; |
302 | |
384b8bce |
303 | if ($^O !~ /win32|cygwin/i) { |
304 | $self->throw_exception('Server cursors only work on Windows platforms at ' |
305 | .'the time of writing.'); |
306 | } |
307 | |
9ae966b9 |
308 | $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size; |
7b1b2582 |
309 | } |
310 | |
a2bd3796 |
311 | =head1 FURTHER QUESTIONS? |
c1cac633 |
312 | |
a2bd3796 |
313 | Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>. |
c1cac633 |
314 | |
a2bd3796 |
315 | =head1 COPYRIGHT AND LICENSE |
c1cac633 |
316 | |
a2bd3796 |
317 | This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE> |
318 | by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can |
319 | redistribute it and/or modify it under the same terms as the |
320 | L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>. |
c1cac633 |
321 | |
322 | =cut |
a2bd3796 |
323 | |
324 | 1; |
325 | |
9ffaf8a3 |
326 | # vim:sw=2 sts=2 et |