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