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