sub last_insert_id { shift->_identity }
#
-# MSSQL is retarded wrt ordered subselects. One needs to add a TOP 100%
-# to *all* subqueries, do it here.
+# MSSQL is retarded wrt ordered subselects. One needs to add a TOP
+# to *all* subqueries, but one also can't use TOP 100 PERCENT
+# http://sqladvice.com/forums/permalink/18496/22931/ShowThread.aspx#22931
#
sub _select_args_to_query {
my $self = shift;
- my $sql_maker = $self->sql_maker;
- my ($op, $bind, $ident, $bind_attrs, $select, $cond, $order) = $self->_select_args(@_);
+ my ($sql, $prep_bind, @rest) = $self->next::method (@_);
- if (not scalar $sql_maker->_order_by_chunks ($order->{order_by}) ) {
- # no ordering, just short circuit
- return $self->next::method (@_);
+ # see if this is an ordered subquery
+ my $attrs = $_[3];
+ if ( scalar $self->sql_maker->_order_by_chunks ($attrs->{order_by}) ) {
+ my $max = 2 ** 32;
+ $sql =~ s/^ \s* SELECT \s/SELECT TOP $max /xi;
}
- my ($sql, $bind, @rest) = $self->next::method (@_);
- $sql =~ s/^ \s* SELECT \s/SELECT TOP 100 PERCENT /xi;
-
return wantarray
- ? ($sql, $bind, @rest)
- : \[ "($sql)", @$bind ]
+ ? ($sql, $prep_bind, @rest)
+ : \[ "($sql)", @$prep_bind ]
;
}
sub sqlt_type { 'SQLServer' }
-sub _sql_maker_opts {
- my ( $self, $opts ) = @_;
+sub _get_mssql_version {
+ my $self = shift;
+
+ my $data = $self->_get_dbh->selectrow_hashref('xp_msver ProductVersion');
+
+ if ($data->{Character_Value} =~ /^(\d+)\./) {
+ return $1;
+ } else {
+ $self->throw_exception(q{Your ProductVersion's Character_Value is missing or malformed!});
+ }
+}
+
+sub sql_maker {
+ my $self = shift;
+
+ unless ($self->_sql_maker) {
+ unless ($self->{_sql_maker_opts}{limit_dialect}) {
+ my $version = eval { $self->_get_mssql_version; } || 0;
+
+ $self->{_sql_maker_opts} = {
+ limit_dialect => ($version >= 9 ? 'RowNumberOver' : 'Top'),
+ %{$self->{_sql_maker_opts}||{}}
+ };
+ }
- if ( $opts ) {
- $self->{_sql_maker_opts} = { %$opts };
+ my $maker = $self->next::method (@_);
}
- return { limit_dialect => 'RowNumberOver', %{$self->{_sql_maker_opts}||{}} };
+ return $self->_sql_maker;
}
1;