Commit | Line | Data |
c1cac633 |
1 | package DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server; |
2 | use strict; |
3 | use warnings; |
4 | |
eb0323df |
5 | use base qw/DBIx::Class::Storage::DBI::MSSQL/; |
2ad62d97 |
6 | use mro 'c3'; |
6298a324 |
7 | use Scalar::Util 'reftype'; |
ed7ab0f4 |
8 | use Try::Tiny; |
384b8bce |
9 | use Carp::Clan qw/^DBIx::Class/; |
fd323bf1 |
10 | use namespace::clean; |
c1cac633 |
11 | |
7b1b2582 |
12 | __PACKAGE__->mk_group_accessors(simple => qw/ |
13 | _using_dynamic_cursors |
14 | /); |
c1cac633 |
15 | |
16 | =head1 NAME |
17 | |
a89c6fc0 |
18 | DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server - Support specific |
19 | to Microsoft SQL Server over ODBC |
c1cac633 |
20 | |
21 | =head1 DESCRIPTION |
22 | |
5a77aa8b |
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 |
25 | MSSQL back-end. |
c1cac633 |
26 | |
5a77aa8b |
27 | Most of the functionality is provided from the superclass |
28 | L<DBIx::Class::Storage::DBI::MSSQL>. |
c1cac633 |
29 | |
dcc7ddff |
30 | =head1 USAGE NOTES |
31 | |
32 | =head2 Basic Linux Setup (Debian) |
33 | |
34 | sudo aptitude install tdsodbc libdbd-odbc-perl unixodbc |
35 | |
36 | In case it is not already there put the following in C</etc/odbcinst.ini>: |
37 | |
38 | [FreeTDS] |
39 | Description = FreeTDS |
40 | Driver = /usr/lib/odbc/libtdsodbc.so |
41 | Setup = /usr/lib/odbc/libtdsS.so |
42 | UsageCount = 1 |
43 | |
44 | Set your C<$dsn> in L<connect_info|DBIx::Class::Storage::DBI/connect_info> as follows: |
45 | |
46 | dbi:ODBC:server=<my.host.name>;port=1433;driver=FreeTDS;tds_version=8.0 |
47 | |
48 | If you use the EasySoft driver (L<http://www.easysoft.com>): |
49 | |
50 | dbi:ODBC:server=<my.host.name>;port=1433;driver=Easysoft ODBC-SQL Server |
51 | |
52 | =head2 Basic Windows Setup |
53 | |
54 | Use the following C<$dsn> for the Microsoft ODBC driver: |
55 | |
56 | dbi:ODBC:driver={SQL Server};server=SERVER\SQL_SERVER_INSTANCE_NAME |
57 | |
58 | And for the Native Client: |
59 | |
60 | dbi:ODBC:driver={SQL Server Native Client 10.0};server=SERVER\SQL_SERVER_INSTANCE_NAME |
61 | |
62 | Go into Control Panel -> System and Security -> Administrative Tools -> Data |
63 | Sources (ODBC) to check driver names and to set up data sources. |
64 | |
65 | Use System DSNs, not User DSNs if you want to use DSNs. |
66 | |
67 | If you set up a DSN, use the following C<$dsn> for |
68 | L<connect_info|DBIx::Class::Storage::DBI/connect_info>: |
69 | |
70 | dbi:ODBC:dsn=MY_DSN |
71 | |
7b1b2582 |
72 | =head1 MULTIPLE ACTIVE STATEMENTS |
73 | |
74 | The following options are alternative ways to enable concurrent executing |
384b8bce |
75 | statement support. Each has its own advantages and drawbacks and works on |
76 | different platforms. Read each section carefully. |
77 | |
78 | In order of preference, they are: |
79 | |
80 | =over 8 |
81 | |
1db83fb9 |
82 | =item * L<mars|/connect_call_use_mars> |
384b8bce |
83 | |
1db83fb9 |
84 | =item * L<dynamic_cursors|/connect_call_use_dynamic_cursors> |
384b8bce |
85 | |
1db83fb9 |
86 | =item * L<server_cursors|/connect_call_use_server_cursors> |
384b8bce |
87 | |
88 | =back |
89 | |
90 | =head1 METHODS |
91 | |
92 | =head2 connect_call_use_mars |
93 | |
94 | Use as: |
95 | |
96 | on_connect_call => 'use_mars' |
97 | |
1db83fb9 |
98 | in your connection info, or alternatively specify it directly: |
99 | |
100 | Your::Schema->connect ( |
101 | $original_dsn . '; MARS_Connection=Yes', |
102 | $user, |
103 | $pass, |
104 | \%attrs, |
105 | ) |
106 | |
384b8bce |
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. |
110 | |
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 |
113 | the Easysoft driver. |
114 | |
115 | =cut |
116 | |
117 | sub connect_call_use_mars { |
118 | my $self = shift; |
119 | |
120 | my $dsn = $self->_dbi_connect_info->[0]; |
121 | |
122 | if (ref($dsn) eq 'CODE') { |
123 | $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info'); |
124 | } |
125 | |
126 | if ($dsn !~ /MARS_Connection=/) { |
127 | if ($self->using_freetds) { |
128 | $self->throw_exception('FreeTDS does not support MARS at the time of ' |
129 | .'writing.'); |
130 | } |
131 | |
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.'); |
135 | } |
136 | |
137 | if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN |
a469c045 |
138 | warn "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'" |
139 | ." for MARS\n"; |
140 | $dsn = "dbi:ODBC:dsn=$data_source"; |
384b8bce |
141 | } |
142 | |
143 | $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes"; |
144 | $self->disconnect; |
145 | $self->ensure_connected; |
146 | } |
147 | } |
148 | |
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(@_) |
153 | } |
7b1b2582 |
154 | |
155 | =head2 connect_call_use_dynamic_cursors |
156 | |
157 | Use as: |
158 | |
159 | on_connect_call => 'use_dynamic_cursors' |
160 | |
1db83fb9 |
161 | Which will add C<< odbc_cursortype => 2 >> to your DBI connection |
162 | attributes, or alternatively specify the necessary flag directly: |
7b1b2582 |
163 | |
1db83fb9 |
164 | Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 }) |
7b1b2582 |
165 | |
1db83fb9 |
166 | See L<DBD::ODBC/odbc_cursortype> for more information. |
7b1b2582 |
167 | |
41dd5d30 |
168 | If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>. |
169 | |
170 | This will not work with CODE ref connect_info's. |
7b1b2582 |
171 | |
1db83fb9 |
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. |
7b1b2582 |
176 | |
177 | =cut |
178 | |
179 | sub connect_call_use_dynamic_cursors { |
180 | my $self = shift; |
181 | |
1db83fb9 |
182 | my $conn_info = $self->_dbi_connect_info; |
7b1b2582 |
183 | |
1db83fb9 |
184 | if (ref($conn_info->[0]) eq 'CODE') { |
185 | $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info'); |
7b1b2582 |
186 | } |
187 | |
1db83fb9 |
188 | if ( |
189 | ref($conn_info->[-1]) ne 'HASH' |
190 | or |
191 | ($conn_info->[-1]{odbc_cursortype}||0) < 2 |
192 | ) { |
193 | # reenter connection information with the attribute re-set |
194 | $self->connect_info( |
195 | @{$conn_info}[0,1,2], |
196 | { %{$self->_dbix_connect_attributes}, odbc_cursortype => 2 }, |
197 | ); |
75517ea9 |
198 | $self->disconnect; # resetting dbi attrs, so have to reconnect |
199 | $self->ensure_connected; |
7b1b2582 |
200 | } |
201 | } |
202 | |
1db83fb9 |
203 | sub _run_connection_actions { |
7b1b2582 |
204 | my $self = shift; |
205 | |
1db83fb9 |
206 | # keep the dynamic_cursors_support and driver-state in sync |
207 | # on every reconnect |
208 | my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1; |
1a58752c |
209 | if ( |
1db83fb9 |
210 | $use_dyncursors |
211 | xor |
212 | !!$self->_using_dynamic_cursors |
1a58752c |
213 | ) { |
1db83fb9 |
214 | if ($use_dyncursors) { |
215 | try { |
216 | my $dbh = $self->_dbh; |
217 | local $dbh->{RaiseError} = 1; |
218 | local $dbh->{PrintError} = 0; |
219 | $dbh->do('SELECT @@IDENTITY'); |
220 | } catch { |
221 | $self->throw_exception ( |
222 | 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).' |
223 | . ( |
224 | $self->using_freetds |
225 | ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.' |
226 | : '' |
227 | ) |
228 | ); |
229 | }; |
230 | |
231 | $self->_using_dynamic_cursors(1); |
232 | $self->_identity_method('@@identity'); |
233 | } |
234 | else { |
235 | $self->_using_dynamic_cursors(0); |
236 | $self->_identity_method(undef); |
237 | } |
384b8bce |
238 | } |
1db83fb9 |
239 | |
240 | $self->next::method (@_); |
7b1b2582 |
241 | } |
242 | |
243 | =head2 connect_call_use_server_cursors |
244 | |
245 | Use as: |
246 | |
247 | on_connect_call => 'use_server_cursors' |
248 | |
249 | May allow multiple active select statements. See |
250 | L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information. |
251 | |
252 | Takes an optional parameter for the value to set the attribute to, default is |
253 | C<2>. |
254 | |
255 | B<WARNING>: this does not work on all versions of SQL Server, and may lock up |
256 | your database! |
257 | |
384b8bce |
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. |
260 | |
7b1b2582 |
261 | =cut |
262 | |
263 | sub connect_call_use_server_cursors { |
264 | my $self = shift; |
265 | my $sql_rowset_size = shift || 2; |
266 | |
384b8bce |
267 | if ($^O !~ /win32|cygwin/i) { |
268 | $self->throw_exception('Server cursors only work on Windows platforms at ' |
269 | .'the time of writing.'); |
270 | } |
271 | |
9ae966b9 |
272 | $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size; |
7b1b2582 |
273 | } |
274 | |
384b8bce |
275 | =head2 using_freetds |
7b1b2582 |
276 | |
384b8bce |
277 | Tries to determine, to the best of our ability, whether or not you are using the |
278 | FreeTDS driver with L<DBD::ODBC>. |
7b1b2582 |
279 | |
280 | =cut |
281 | |
384b8bce |
282 | sub using_freetds { |
7b1b2582 |
283 | my $self = shift; |
284 | |
285 | my $dsn = $self->_dbi_connect_info->[0]; |
286 | |
384b8bce |
287 | $dsn = '' if ref $dsn eq 'CODE'; |
7b1b2582 |
288 | |
384b8bce |
289 | my $dbh = $self->_get_dbh; |
290 | |
291 | return 1 if $dsn =~ /driver=FreeTDS/i |
292 | || (try { $dbh->get_info(6) }||'') =~ /tdsodbc/i; |
293 | |
294 | return 0; |
7b1b2582 |
295 | } |
296 | |
297 | 1; |
298 | |
5a77aa8b |
299 | =head1 AUTHOR |
c1cac633 |
300 | |
5a77aa8b |
301 | See L<DBIx::Class/CONTRIBUTORS>. |
c1cac633 |
302 | |
303 | =head1 LICENSE |
304 | |
305 | You may distribute this code under the same terms as Perl itself. |
306 | |
307 | =cut |
259c0e40 |
308 | # vim: sw=2 sts=2 |