Common methods for handling TSQL databases.
# File lib/jdbc_adapter/tsql_helper.rb, line 27 def add_limit_offset!(sql, options) if options[:limit] and options[:offset] total_rows = select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT\\1 TOP 1000000000")}) tally")[0]["TotalRows"].to_i if (options[:limit] + options[:offset]) >= total_rows options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0 end sql.sub!(/^\s*SELECT(\s+DISTINCT)?/, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT\\1 TOP #{options[:limit] + options[:offset]} ") sql << ") AS tmp1" if options[:order] options[:order] = options[:order].split(',').map do |field| parts = field.split(" ") tc = parts[0] if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query tc.gsub!(/\./, '\.\[') tc << '\]' end if sql =~ /#{tc} AS (t\d_r\d\d?)/ parts[0] = $1 elsif parts[0] =~ /\w+\.(\w+)/ parts[0] = $1 end parts.join(' ') end.join(', ') sql << " ORDER BY #{change_order_direction(options[:order])}) AS tmp2 ORDER BY #{options[:order]}" else sql << " ) AS tmp2" end elsif sql !~ /^\s*SELECT (@@|COUNT\()/ sql.sub!(/^\s*SELECT(\s+DISTINCT)?/) do "SELECT#{$1} TOP #{options[:limit]}" end unless options[:limit].nil? end end
Generated with the Darkfish Rdoc Generator 2.