X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FStorage%2FDBI%2FODBC%2FMicrosoft_SQL_Server.pm;h=1b5fba2c31d43367ef8a413fc4616bec0509f201;hb=796acb6a;hp=b2db13f5facb085c6dfd56b1162718ca91de2d70;hpb=6298a324307439b76419d0f5db453b0d10f10517;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Storage/DBI/ODBC/Microsoft_SQL_Server.pm b/lib/DBIx/Class/Storage/DBI/ODBC/Microsoft_SQL_Server.pm index b2db13f..1b5fba2 100644 --- a/lib/DBIx/Class/Storage/DBI/ODBC/Microsoft_SQL_Server.pm +++ b/lib/DBIx/Class/Storage/DBI/ODBC/Microsoft_SQL_Server.pm @@ -2,10 +2,14 @@ package DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server; use strict; use warnings; -use base qw/DBIx::Class::Storage::DBI::MSSQL/; +use base qw/ + DBIx::Class::Storage::DBI::ODBC + DBIx::Class::Storage::DBI::MSSQL +/; use mro 'c3'; use Scalar::Util 'reftype'; use Try::Tiny; +use DBIx::Class::Carp; use namespace::clean; __PACKAGE__->mk_group_accessors(simple => qw/ @@ -26,10 +30,131 @@ MSSQL back-end. Most of the functionality is provided from the superclass L. +=head1 USAGE NOTES + +=head2 Basic Linux Setup (Debian) + + sudo aptitude install tdsodbc libdbd-odbc-perl unixodbc + +In case it is not already there put the following in C: + + [FreeTDS] + Description = FreeTDS + Driver = /usr/lib/odbc/libtdsodbc.so + Setup = /usr/lib/odbc/libtdsS.so + UsageCount = 1 + +Set your C<$dsn> in L as follows: + + dbi:ODBC:server=;port=1433;driver=FreeTDS;tds_version=8.0 + +If you use the EasySoft driver (L): + + dbi:ODBC:server=;port=1433;driver=Easysoft ODBC-SQL Server + +=head2 Basic Windows Setup + +Use the following C<$dsn> for the Microsoft ODBC driver: + + dbi:ODBC:driver={SQL Server};server=SERVER\SQL_SERVER_INSTANCE_NAME + +And for the Native Client: + + dbi:ODBC:driver={SQL Server Native Client 10.0};server=SERVER\SQL_SERVER_INSTANCE_NAME + +Go into Control Panel -> System and Security -> Administrative Tools -> Data +Sources (ODBC) to check driver names and to set up data sources. + +Use System DSNs, not User DSNs if you want to use DSNs. + +If you set up a DSN, use the following C<$dsn> for +L: + + dbi:ODBC:dsn=MY_DSN + =head1 MULTIPLE ACTIVE STATEMENTS The following options are alternative ways to enable concurrent executing -statement support. Each has its own advantages and drawbacks. +statement support. Each has its own advantages and drawbacks and works on +different platforms. Read each section carefully. + +In order of preference, they are: + +=over 8 + +=item * L + +=item * L + +=item * L + +=back + +=head1 METHODS + +=head2 connect_call_use_mars + +Use as: + + on_connect_call => 'use_mars' + +in your connection info, or alternatively specify it directly: + + Your::Schema->connect ( + $original_dsn . '; MARS_Connection=Yes', + $user, + $pass, + \%attrs, + ) + +Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result +Sets". See L +for more information. + +This does not work on FreeTDS drivers at the time of this writing, and only +works with the Native Client, later versions of the Windows MS ODBC driver, and +the Easysoft driver. + +=cut + +sub connect_call_use_mars { + my $self = shift; + + my $dsn = $self->_dbi_connect_info->[0]; + + if (ref($dsn) eq 'CODE') { + $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info'); + } + + if ($dsn !~ /MARS_Connection=/) { + if ($self->_using_freetds) { + my $v = $self->_using_freetds_version; + $self->throw_exception("FreeTDS 0.91 or later required for MARS support, you have $v") + if $v < 0.91; + } + + if (exists $self->_server_info->{normalized_dbms_version} && + $self->_server_info->{normalized_dbms_version} < 9) { + $self->throw_exception('SQL Server 2005 or later required to use MARS.'); + } + + if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN + warn "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'" + ." for MARS\n"; + $dsn = "dbi:ODBC:dsn=$data_source"; + } + + $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes"; + $self->disconnect; + $self->ensure_connected; + } +} + +sub connect_call_use_MARS { + carp "'connect_call_use_MARS' has been deprecated, use " + ."'connect_call_use_mars' instead."; + shift->connect_call_use_mars(@_) +} =head2 connect_call_use_dynamic_cursors @@ -37,86 +162,111 @@ Use as: on_connect_call => 'use_dynamic_cursors' -in your L as one way to enable multiple -concurrent statements. +Which will add C<< odbc_cursortype => 2 >> to your DBI connection +attributes, or alternatively specify the necessary flag directly: -Will add C<< odbc_cursortype => 2 >> to your DBI connection attributes. See -L for more information. + Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 }) -Alternatively, you can add it yourself and dynamic cursor support will be -automatically enabled. +See L for more information. If you're using FreeTDS, C must be set to at least C<8.0>. This will not work with CODE ref connect_info's. -B this will break C, and C will be used instead, which on SQL +Server 2005 and later will return erroneous results on tables which have an on +insert trigger that inserts into another table with an C column. + +B on FreeTDS, changes made in one statement (e.g. an insert) may not +be visible from a following statement (e.g. a select.) + +B FreeTDS versions > 0.82 seem to have completely broken the ODBC +protocol. DBIC will not allow dynamic cursor support with such versions to +protect your data. Please hassle the authors of FreeTDS to act on the bugs that +make their driver not overly usable with DBD::ODBC. =cut sub connect_call_use_dynamic_cursors { my $self = shift; - if (ref($self->_dbi_connect_info->[0]) eq 'CODE') { - $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info'); - } + if (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) { - my $dbi_attrs = $self->_dbi_connect_info->[-1]; + my $dbi_inf = $self->_dbi_connect_info; - unless (ref($dbi_attrs) && reftype $dbi_attrs eq 'HASH') { - $dbi_attrs = {}; - push @{ $self->_dbi_connect_info }, $dbi_attrs; - } + $self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info') + if ref($dbi_inf->[0]) eq 'CODE'; + + # reenter connection information with the attribute re-set + $dbi_inf->[3] = {} if @$dbi_inf <= 3; + $dbi_inf->[3]{odbc_cursortype} = 2; + + $self->_dbi_connect_info($dbi_inf); - if (not exists $dbi_attrs->{odbc_cursortype}) { - # turn on support for multiple concurrent statements, unless overridden - $dbi_attrs->{odbc_cursortype} = 2; $self->disconnect; # resetting dbi attrs, so have to reconnect $self->ensure_connected; - $self->_set_dynamic_cursors; } } -sub _set_dynamic_cursors { - my $self = shift; - my $dbh = $self->_get_dbh; - - try { - local $dbh->{RaiseError} = 1; - local $dbh->{PrintError} = 0; - $dbh->do('SELECT @@IDENTITY'); - } catch { - $self->throw_exception (<<'EOF'); - -Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2), -if you're using FreeTDS, make sure to set tds_version to 8.0 or greater. -EOF - }; - - $self->_using_dynamic_cursors(1); - $self->_identity_method('@@identity'); -} - -sub _init { +sub _run_connection_actions { my $self = shift; - no warnings qw/uninitialized/; - + # keep the dynamic_cursors_support and driver-state in sync + # on every reconnect + my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} || 0) > 1; if ( - ref($self->_dbi_connect_info->[0]) ne 'CODE' - && - ref ($self->_dbi_connect_info->[-1]) eq 'HASH' - && - $self->_dbi_connect_info->[-1]{odbc_cursortype} == 2 + $use_dyncursors + xor + !!$self->_using_dynamic_cursors ) { - $self->_set_dynamic_cursors; - return; + if ($use_dyncursors) { + try { + my $dbh = $self->_dbh; + local $dbh->{RaiseError} = 1; + local $dbh->{PrintError} = 0; + $dbh->do('SELECT @@IDENTITY'); + } catch { + $self->throw_exception ( + 'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).' + . ( + $self->_using_freetds + ? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.' + : '' + ) + ); + }; + + $self->_using_dynamic_cursors(1); + $self->_identity_method('@@identity'); + $self->_no_scope_identity_query($self->_using_freetds); + } + else { + $self->_using_dynamic_cursors(0); + $self->_identity_method(undef); + $self->_no_scope_identity_query(undef); + } } - $self->_using_dynamic_cursors(0); + $self->next::method (@_); + + # freetds is too damn broken, some fixups + if ($self->_using_freetds) { + + # no dynamic cursors starting from 0.83 + if ($self->_using_dynamic_cursors) { + my $fv = $self->_using_freetds_version || 999; # assume large if can't be determined + $self->throw_exception( + 'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 ' + . "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in " + . 'their driver.' + ) if $fv > 0.82 + } + + # FreeTDS is too broken wrt execute_for_fetch batching + # just disable it outright until things quiet down + $self->_get_dbh->{odbc_disable_array_operations} = 1; + } } =head2 connect_call_use_server_cursors @@ -134,55 +284,32 @@ C<2>. B: this does not work on all versions of SQL Server, and may lock up your database! +At the time of writing, this option only works on Microsoft's Windows drivers, +later versions of the ODBC driver and the Native Client driver. + =cut sub connect_call_use_server_cursors { my $self = shift; my $sql_rowset_size = shift || 2; - $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size; -} - -=head2 connect_call_use_MARS - -Use as: - - on_connect_call => 'use_MARS' - -Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result -Sets". See L -for more information. - -B: This has implications for the way transactions are handled. - -=cut - -sub connect_call_use_MARS { - my $self = shift; - - my $dsn = $self->_dbi_connect_info->[0]; - - if (ref($dsn) eq 'CODE') { - $self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info'); + if ($^O !~ /win32|cygwin/i) { + $self->throw_exception('Server cursors only work on Windows platforms at ' + .'the time of writing.'); } - if ($dsn !~ /MARS_Connection=/) { - $self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes"; - my $was_connected = defined $self->_dbh; - $self->disconnect; - $self->ensure_connected if $was_connected; - } + $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size; } 1; =head1 AUTHOR -See L. +See L and L. =head1 LICENSE You may distribute this code under the same terms as Perl itself. =cut -# vim: sw=2 sts=2 +# vim:sw=2 sts=2 et