[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Storage / DBI / ODBC / Microsoft_SQL_Server.pm

package DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server;
use strict;
use warnings;

use base qw/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/
  _using_dynamic_cursors
/);

=head1 NAME

DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server - Support specific
to Microsoft SQL Server over ODBC

=head1 DESCRIPTION

This class implements support specific to Microsoft SQL Server over ODBC.  It is
loaded automatically by by DBIx::Class::Storage::DBI::ODBC when it detects a
MSSQL back-end.

Most of the functionality is provided from the superclass
L<DBIx::Class::Storage::DBI::MSSQL>.

=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</etc/odbcinst.ini>:

  [FreeTDS]
  Description = FreeTDS
  Driver      = /usr/lib/odbc/libtdsodbc.so
  Setup       = /usr/lib/odbc/libtdsS.so
  UsageCount  = 1

Set your C<$dsn> in L<connect_info|DBIx::Class::Storage::DBI/connect_info> as follows:

  dbi:ODBC:server=<my.host.name>;port=1433;driver=FreeTDS;tds_version=8.0

If you use the EasySoft driver (L<http://www.easysoft.com>):

  dbi:ODBC:server=<my.host.name>;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<connect_info|DBIx::Class::Storage::DBI/connect_info>:

  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 and works on
different platforms. Read each section carefully.

In order of preference, they are:

=over 8

=item * L<mars|/connect_call_use_mars>

=item * L<dynamic_cursors|/connect_call_use_dynamic_cursors>

=item * L<server_cursors|/connect_call_use_server_cursors>

=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<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?>
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) {
      $self->throw_exception('FreeTDS does not support MARS at the time of '
                            .'writing.');
    }

    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

Use as:

  on_connect_call => 'use_dynamic_cursors'

Which will add C<< odbc_cursortype => 2 >> to your DBI connection
attributes, or alternatively specify the necessary flag directly:

  Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 })

See L<DBD::ODBC/odbc_cursortype> for more information.

If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>.

This will not work with CODE ref connect_info's.

B<WARNING:> on FreeTDS (and maybe some other drivers) this will break
C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> 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<IDENTITY> column.

B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
be visible from a following statement (e.g. a select.)

B<WARNING:> 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 (($self->_dbic_connect_attributes->{odbc_cursortype} || 0) < 2) {

    my $dbi_inf = $self->_dbi_connect_info;

    $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);

    $self->disconnect; # resetting dbi attrs, so have to reconnect
    $self->ensure_connected;
  }
}

sub _run_connection_actions {
  my $self = shift;

  # 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 (
    $use_dyncursors
      xor
    !!$self->_using_dynamic_cursors
  ) {
    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->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->_dbh_get_info('SQL_DRIVER_VER');
      $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

Use as:

  on_connect_call => 'use_server_cursors'

May allow multiple active select statements. See
L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.

Takes an optional parameter for the value to set the attribute to, default is
C<2>.

B<WARNING>: 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;

  if ($^O !~ /win32|cygwin/i) {
    $self->throw_exception('Server cursors only work on Windows platforms at '
                          .'the time of writing.');
  }

  $self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
}

=head2 using_freetds

Tries to determine, to the best of our ability, whether or not you are using the
FreeTDS driver with L<DBD::ODBC>.

=cut

sub using_freetds {
  my $self = shift;

  my $dsn = $self->_dbi_connect_info->[0];

  $dsn = '' if ref $dsn eq 'CODE';

  return 1 if $dsn =~ /driver=FreeTDS/i
              || ($self->_dbh_get_info('SQL_DRIVER_NAME')||'') =~ /tdsodbc/i;

  return 0;
}

1;

=head1 AUTHOR

See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.

=head1 LICENSE

You may distribute this code under the same terms as Perl itself.

=cut
# vim:sw=2 sts=2 et
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;
70c28808	9	use DBIx::Class::Carp;
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
a89c6fc0	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
5a77aa8b	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
1db83fb9	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
9ffaf8a3	177	B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
	178	be visible from a following statement (e.g. a select.)
	179
25d3127d	180	B<WARNING:> FreeTDS versions > 0.82 seem to have completely broken the ODBC
	181	protocol. DBIC will not allow dynamic cursor support with such versions to
	182	protect your data. Please hassle the authors of FreeTDS to act on the bugs that
	183	make their driver not overly usable with DBD::ODBC.
	184
7b1b2582	185	=cut
	186
	187	sub connect_call_use_dynamic_cursors {
	188	my $self = shift;
	189
9ffaf8a3	190	if (($self->_dbic_connect_attributes->{odbc_cursortype} \|\| 0) < 2) {
7b1b2582	191
9ffaf8a3	192	my $dbi_inf = $self->_dbi_connect_info;
	193
	194	$self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info')
	195	if ref($dbi_inf->[0]) eq 'CODE';
7b1b2582	196
1db83fb9	197	# reenter connection information with the attribute re-set
9ffaf8a3	198	$dbi_inf->[3] = {} if @$dbi_inf <= 3;
	199	$dbi_inf->[3]{odbc_cursortype} = 2;
	200
	201	$self->_dbi_connect_info($dbi_inf);
	202
75517ea9	203	$self->disconnect; # resetting dbi attrs, so have to reconnect
75517ea9	204	$self->ensure_connected;
7b1b2582	205	}
	206	}
	207
1db83fb9	208	sub _run_connection_actions {
7b1b2582	209	my $self = shift;
7b1b2582	210
1db83fb9	211	# keep the dynamic_cursors_support and driver-state in sync
	212	# on every reconnect
	213	my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} \|\| 0) > 1;
1a58752c	214	if (
1db83fb9	215	$use_dyncursors
	216	xor
	217	!!$self->_using_dynamic_cursors
1a58752c	218	) {
1db83fb9	219	if ($use_dyncursors) {
	220	try {
	221	my $dbh = $self->_dbh;
	222	local $dbh->{RaiseError} = 1;
	223	local $dbh->{PrintError} = 0;
	224	$dbh->do('SELECT @@IDENTITY');
	225	} catch {
	226	$self->throw_exception (
	227	'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
	228	. (
	229	$self->using_freetds
	230	? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
	231	: ''
	232	)
	233	);
	234	};
	235
	236	$self->_using_dynamic_cursors(1);
	237	$self->_identity_method('@@identity');
25d3127d	238	$self->_no_scope_identity_query($self->using_freetds);
1db83fb9	239	}
	240	else {
	241	$self->_using_dynamic_cursors(0);
	242	$self->_identity_method(undef);
25d3127d	243	$self->_no_scope_identity_query(undef);
1db83fb9	244	}
384b8bce	245	}
1db83fb9	246
1db83fb9	247	$self->next::method (@_);
25d3127d	248
	249	# freetds is too damn broken, some fixups
	250	if ($self->using_freetds) {
	251
	252	# no dynamic cursors starting from 0.83
	253	if ($self->_using_dynamic_cursors) {
	254	my $fv = $self->_dbh_get_info('SQL_DRIVER_VER');
	255	$self->throw_exception(
	256	'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 '
	257	. "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in "
	258	. 'their driver.'
	259	) if $fv > 0.82
	260	}
	261
	262	# FreeTDS is too broken wrt execute_for_fetch batching
	263	# just disable it outright until things quiet down
	264	$self->_get_dbh->{odbc_disable_array_operations} = 1;
	265	}
7b1b2582	266	}
	267
	268	=head2 connect_call_use_server_cursors
	269
	270	Use as:
	271
	272	on_connect_call => 'use_server_cursors'
	273
	274	May allow multiple active select statements. See
	275	L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
	276
	277	Takes an optional parameter for the value to set the attribute to, default is
	278	C<2>.
	279
	280	B<WARNING>: this does not work on all versions of SQL Server, and may lock up
	281	your database!
	282
384b8bce	283	At the time of writing, this option only works on Microsoft's Windows drivers,
	284	later versions of the ODBC driver and the Native Client driver.
	285
7b1b2582	286	=cut
	287
	288	sub connect_call_use_server_cursors {
	289	my $self = shift;
	290	my $sql_rowset_size = shift \|\| 2;
	291
384b8bce	292	if ($^O !~ /win32\|cygwin/i) {
	293	$self->throw_exception('Server cursors only work on Windows platforms at '
	294	.'the time of writing.');
	295	}
	296
9ae966b9	297	$self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
7b1b2582	298	}
7b1b2582	299
384b8bce	300	=head2 using_freetds
7b1b2582	301
384b8bce	302	Tries to determine, to the best of our ability, whether or not you are using the
384b8bce	303	FreeTDS driver with L<DBD::ODBC>.
7b1b2582	304
	305	=cut
	306
384b8bce	307	sub using_freetds {
7b1b2582	308	my $self = shift;
	309
	310	my $dsn = $self->_dbi_connect_info->[0];
	311
384b8bce	312	$dsn = '' if ref $dsn eq 'CODE';
7b1b2582	313
384b8bce	314	return 1 if $dsn =~ /driver=FreeTDS/i
af1f4f84	315	\|\| ($self->_dbh_get_info('SQL_DRIVER_NAME')\|\|'') =~ /tdsodbc/i;
384b8bce	316
384b8bce	317	return 0;
7b1b2582	318	}
	319
	320	1;
	321
5a77aa8b	322	=head1 AUTHOR
c1cac633	323
9ffaf8a3	324	See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
c1cac633	325
	326	=head1 LICENSE
	327
	328	You may distribute this code under the same terms as Perl itself.
	329
	330	=cut
9ffaf8a3	331	# vim:sw=2 sts=2 et