[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::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/
  _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.

For more details about using MAS in MSSQL over DBD::ODBC see this excellent
document provided by EasySoft:
L<http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>.

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;

  $self->next::method (@_);

  # 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');
    }
    else {
      $self->_using_dynamic_cursors(0);
      $self->_identity_method(undef);
    }
  }

  $self->_no_scope_identity_query($self->_using_dynamic_cursors
    ? $self->_using_freetds
    : undef
  );

  # 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->_disable_odbc_array_ops;
  }
}

=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;
}

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
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
a89c6fc0	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
5a77aa8b	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
2b6d7e87	81	For more details about using MAS in MSSQL over DBD::ODBC see this excellent
	82	document provided by EasySoft:
	83	L<http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html>.
	84
384b8bce	85	In order of preference, they are:
	86
	87	=over 8
	88
1db83fb9	89	=item * L<mars\|/connect_call_use_mars>
384b8bce	90
1db83fb9	91	=item * L<dynamic_cursors\|/connect_call_use_dynamic_cursors>
384b8bce	92
1db83fb9	93	=item * L<server_cursors\|/connect_call_use_server_cursors>
384b8bce	94
	95	=back
	96
	97	=head1 METHODS
	98
	99	=head2 connect_call_use_mars
	100
	101	Use as:
	102
	103	on_connect_call => 'use_mars'
	104
1db83fb9	105	in your connection info, or alternatively specify it directly:
	106
	107	Your::Schema->connect (
	108	$original_dsn . '; MARS_Connection=Yes',
	109	$user,
	110	$pass,
	111	\%attrs,
	112	)
	113
384b8bce	114	Use to enable a feature of SQL Server 2005 and later, "Multiple Active Result
	115	Sets". See L<DBD::ODBC::FAQ/Does DBD::ODBC support Multiple Active Statements?>
	116	for more information.
	117
	118	This does not work on FreeTDS drivers at the time of this writing, and only
	119	works with the Native Client, later versions of the Windows MS ODBC driver, and
	120	the Easysoft driver.
	121
	122	=cut
	123
	124	sub connect_call_use_mars {
	125	my $self = shift;
	126
	127	my $dsn = $self->_dbi_connect_info->[0];
	128
	129	if (ref($dsn) eq 'CODE') {
	130	$self->throw_exception('cannot change the DBI DSN on a CODE ref connect_info');
	131	}
	132
	133	if ($dsn !~ /MARS_Connection=/) {
aca3b4c3	134	if ($self->_using_freetds) {
94f9fbef	135	$self->throw_exception('FreeTDS does not support MARS at the time of '
94f9fbef	136	.'writing.');
384b8bce	137	}
	138
	139	if (exists $self->_server_info->{normalized_dbms_version} &&
	140	$self->_server_info->{normalized_dbms_version} < 9) {
	141	$self->throw_exception('SQL Server 2005 or later required to use MARS.');
	142	}
	143
	144	if (my ($data_source) = $dsn =~ /^dbi:ODBC:([\w-]+)\z/i) { # prefix with DSN
a469c045	145	warn "Bare DSN in ODBC connect string, rewriting as 'dsn=$data_source'"
	146	." for MARS\n";
	147	$dsn = "dbi:ODBC:dsn=$data_source";
384b8bce	148	}
	149
	150	$self->_dbi_connect_info->[0] = "$dsn;MARS_Connection=Yes";
	151	$self->disconnect;
	152	$self->ensure_connected;
	153	}
	154	}
	155
	156	sub connect_call_use_MARS {
	157	carp "'connect_call_use_MARS' has been deprecated, use "
	158	."'connect_call_use_mars' instead.";
	159	shift->connect_call_use_mars(@_)
	160	}
7b1b2582	161
	162	=head2 connect_call_use_dynamic_cursors
	163
	164	Use as:
	165
	166	on_connect_call => 'use_dynamic_cursors'
	167
1db83fb9	168	Which will add C<< odbc_cursortype => 2 >> to your DBI connection
1db83fb9	169	attributes, or alternatively specify the necessary flag directly:
7b1b2582	170
1db83fb9	171	Your::Schema->connect (@dsn, { ... odbc_cursortype => 2 })
7b1b2582	172
1db83fb9	173	See L<DBD::ODBC/odbc_cursortype> for more information.
7b1b2582	174
41dd5d30	175	If you're using FreeTDS, C<tds_version> must be set to at least C<8.0>.
	176
	177	This will not work with CODE ref connect_info's.
7b1b2582	178
1db83fb9	179	B<WARNING:> on FreeTDS (and maybe some other drivers) this will break
	180	C<SCOPE_IDENTITY()>, and C<SELECT @@IDENTITY> will be used instead, which on SQL
	181	Server 2005 and later will return erroneous results on tables which have an on
	182	insert trigger that inserts into another table with an C<IDENTITY> column.
7b1b2582	183
9ffaf8a3	184	B<WARNING:> on FreeTDS, changes made in one statement (e.g. an insert) may not
	185	be visible from a following statement (e.g. a select.)
	186
25d3127d	187	B<WARNING:> FreeTDS versions > 0.82 seem to have completely broken the ODBC
	188	protocol. DBIC will not allow dynamic cursor support with such versions to
	189	protect your data. Please hassle the authors of FreeTDS to act on the bugs that
	190	make their driver not overly usable with DBD::ODBC.
	191
7b1b2582	192	=cut
	193
	194	sub connect_call_use_dynamic_cursors {
	195	my $self = shift;
	196
9ffaf8a3	197	if (($self->_dbic_connect_attributes->{odbc_cursortype} \|\| 0) < 2) {
7b1b2582	198
9ffaf8a3	199	my $dbi_inf = $self->_dbi_connect_info;
	200
	201	$self->throw_exception ('Cannot set DBI attributes on a CODE ref connect_info')
	202	if ref($dbi_inf->[0]) eq 'CODE';
7b1b2582	203
1db83fb9	204	# reenter connection information with the attribute re-set
9ffaf8a3	205	$dbi_inf->[3] = {} if @$dbi_inf <= 3;
	206	$dbi_inf->[3]{odbc_cursortype} = 2;
	207
	208	$self->_dbi_connect_info($dbi_inf);
	209
75517ea9	210	$self->disconnect; # resetting dbi attrs, so have to reconnect
75517ea9	211	$self->ensure_connected;
7b1b2582	212	}
	213	}
	214
1db83fb9	215	sub _run_connection_actions {
7b1b2582	216	my $self = shift;
7b1b2582	217
23eae175	218	$self->next::method (@_);
23eae175	219
1db83fb9	220	# keep the dynamic_cursors_support and driver-state in sync
	221	# on every reconnect
	222	my $use_dyncursors = ($self->_dbic_connect_attributes->{odbc_cursortype} \|\| 0) > 1;
1a58752c	223	if (
1db83fb9	224	$use_dyncursors
	225	xor
	226	!!$self->_using_dynamic_cursors
1a58752c	227	) {
1db83fb9	228	if ($use_dyncursors) {
	229	try {
	230	my $dbh = $self->_dbh;
	231	local $dbh->{RaiseError} = 1;
	232	local $dbh->{PrintError} = 0;
	233	$dbh->do('SELECT @@IDENTITY');
	234	} catch {
	235	$self->throw_exception (
	236	'Your drivers do not seem to support dynamic cursors (odbc_cursortype => 2).'
	237	. (
aca3b4c3	238	$self->_using_freetds
1db83fb9	239	? ' If you are using FreeTDS, make sure to set tds_version to 8.0 or greater.'
	240	: ''
	241	)
	242	);
	243	};
	244
	245	$self->_using_dynamic_cursors(1);
	246	$self->_identity_method('@@identity');
	247	}
	248	else {
	249	$self->_using_dynamic_cursors(0);
	250	$self->_identity_method(undef);
	251	}
384b8bce	252	}
1db83fb9	253
23eae175	254	$self->_no_scope_identity_query($self->_using_dynamic_cursors
	255	? $self->_using_freetds
	256	: undef
	257	);
25d3127d	258
25d3127d	259	# freetds is too damn broken, some fixups
aca3b4c3	260	if ($self->_using_freetds) {
25d3127d	261
	262	# no dynamic cursors starting from 0.83
	263	if ($self->_using_dynamic_cursors) {
aca3b4c3	264	my $fv = $self->_using_freetds_version \|\| 999; # assume large if can't be determined
25d3127d	265	$self->throw_exception(
	266	'Dynamic cursors (odbc_cursortype => 2) are not supported with FreeTDS > 0.82 '
	267	. "(you have $fv). Please hassle FreeTDS authors to fix the outstanding bugs in "
	268	. 'their driver.'
	269	) if $fv > 0.82
	270	}
	271
	272	# FreeTDS is too broken wrt execute_for_fetch batching
	273	# just disable it outright until things quiet down
11f7049f	274	$self->_disable_odbc_array_ops;
25d3127d	275	}
7b1b2582	276	}
	277
	278	=head2 connect_call_use_server_cursors
	279
	280	Use as:
	281
	282	on_connect_call => 'use_server_cursors'
	283
	284	May allow multiple active select statements. See
	285	L<DBD::ODBC/odbc_SQL_ROWSET_SIZE> for more information.
	286
	287	Takes an optional parameter for the value to set the attribute to, default is
	288	C<2>.
	289
	290	B<WARNING>: this does not work on all versions of SQL Server, and may lock up
	291	your database!
	292
384b8bce	293	At the time of writing, this option only works on Microsoft's Windows drivers,
	294	later versions of the ODBC driver and the Native Client driver.
	295
7b1b2582	296	=cut
	297
	298	sub connect_call_use_server_cursors {
	299	my $self = shift;
	300	my $sql_rowset_size = shift \|\| 2;
	301
384b8bce	302	if ($^O !~ /win32\|cygwin/i) {
	303	$self->throw_exception('Server cursors only work on Windows platforms at '
	304	.'the time of writing.');
	305	}
	306
9ae966b9	307	$self->_get_dbh->{odbc_SQL_ROWSET_SIZE} = $sql_rowset_size;
7b1b2582	308	}
7b1b2582	309
7b1b2582	310	1;
7b1b2582	311
5a77aa8b	312	=head1 AUTHOR
c1cac633	313
9ffaf8a3	314	See L<DBIx::Class/AUTHOR> and L<DBIx::Class/CONTRIBUTORS>.
c1cac633	315
	316	=head1 LICENSE
	317
	318	You may distribute this code under the same terms as Perl itself.
	319
	320	=cut
9ffaf8a3	321	# vim:sw=2 sts=2 et