odbc connect and use of LIMIT in sql

[lukeholder]

Sequel uses LIMIT in many situations. For example .count

I have an ODBC connection to a ODBC driver that does not support LIMIT. (Sage Timberline CRE 300 "Timberline Data" https://gist.github.com/lukeholder/75e517f2723c98562cdd) I know, I know, terrible, but I need to somehow make it work.

for example:

costcodes = CostCodes.where(:cost_code => '23-110',:job=>'5001-261')
p costcodes.count

C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-3.48.0/lib/sequel/adapters/odbc.rb:39:in `run': ODBC::Err
or: 37000 (0) [Simba][SimbaEngine ODBC Driver]SELECT count(*) AS "COUNT" FROM "JCM_MASTER__COST_CODE
" WHERE (("COST_CODE" = '23-110') AND ("JOB" = '5001-261')) LIMIT<< ??? >> 1 (Sequel::DatabaseError)

Is there a way to globally change the way Sequel does the equivalent of LIMIT?

[jeremyevans]

You should probably write a shared adapter for your database. See the existing shared adapters as an example. As you didn't specify the actual SQL syntax to use for a limit, I can't provide example code, but you'll probably want to override select_limit_sql and call Dataset.def_sql_method. Once you've written a shared adapter for your database, you can include it into your database object:

require 'path/to/shared/adapter'
db.extend Sequel::SageTimberline::DatabaseMethods
db.extend_datasets Sequel::SageTimberline::DatasetMethods

[lukeholder]

Thanks @jeremyevans

[lukeholder]

@jeremyevans any example you could share with the below constraints... i'm struggling...

the syntax for the limit is by the the use of TOP in the select

Not:
SELECT * FROM Customers ORDER BY LoginName LIMIT 10

Use:
SELECT TOP 10 * FROM Customers ORDER BY LoginName

This is how I am connecting which is working correctly for normal limits but sequel puts limit 1 on a whole bunch of queries like count.

connection_string = 'driver={Timberline Data};dbq=\\\\doric-server19\\Timberline Office\\Gold\\DORIC GROUP\\;codepage=1252;dictionarymode=0;standardmode=1;maxcolsupport=1536;shortenames=0;databasetype=1;uid=l;pwd=AwesomePassword;'

DB = Sequel.odbc(:driver=>'Timberline Data',:drvconnect=>connection_string)

[lukeholder]

@jeremyevans
it looks like my odbc driver works like the Access shared adapter:

sequel/lib/sequel/adapters/shared/access.rb

Lines 285 to 291 in f1d1ea6

	# Access uses TOP for limits
	def select_limit_sql(sql)
	if l = @opts[:limit]
	sql << TOP
	literal_append(sql, l)
	end
	end

[jeremyevans]

Something like this should work:

DB.extend_datasets do
  Sequel::Dataset.def_sql_method(self, :select, %w'select distinct limit columns into from join where group order having compounds')

  def select_limit_sql(sql)
    if l = @opts[:limit]
      sql << 'TOP '
      literal_append(sql, l)
    end 
  end
end

[lukeholder]

Thanks so much @jeremyevans , i am getting this error

T:\PRECEDA\DEV\odbcconnect>ruby main.rb
main.rb:10:in `block in <main>': undefined method `def_sql_method' for Sequel::Dataset:Class (NoMeth
odError)
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-3.48.0/lib/sequel/database/dataset_defaults.
rb:96:in `initialize'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-3.48.0/lib/sequel/database/dataset_defaults.
rb:96:in `new'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-3.48.0/lib/sequel/database/dataset_defaults.
rb:96:in `extend_datasets'
        from main.rb:9:in `<main>'

full script https://gist.github.com/lukeholder/35767b5750f3ea4c3b3d

[jeremyevans]

Upgrade your version of Sequel to the current version. :)

[lukeholder]

ok! doing now!

[lukeholder]

@jeremyevans

T:\PRECEDA\DEV\odbcconnect>ruby main.rb
C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/sql.rb:228:in `select_sql': und
efined method `select_into_sql' for #<#<Class:0x281c868>:0x27f2208> (NoMethodError)
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:139:in
`each'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:639:in
`single_record'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:647:in
`single_value'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:246:in
`get'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:103:in
`count'
        from main.rb:43:in `<main>'

[lukeholder]

ok changed it to below. (had to put a space in front of the TOP string also to get it separate from SELECT.

DB.extend_datasets do
  Sequel::Dataset.def_sql_method(self, :select, %w'select distinct limit columns into from join where group order having compounds')

  def select_into_sql(sql)
    if i = @opts[:into]
      sql << INTO
      identifier_append(sql, i)
    end
  end

  def select_limit_sql(sql)
    if l = @opts[:limit]
      sql << ' TOP '
      literal_append(sql, l)
    end 
  end
end

and it seems to now error out again at the driver level:

T:\PRECEDA\DEV\odbcconnect>ruby main.rb
C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/adapters/odbc.rb:40:in `run': ODBC::Err
or: 37000 (0) [Simba][SimbaEngine ODBC Driver]SELECT TOP 1<< ??? >> count(*) AS "COUNT" FROM "JCM_MA
STER__COST_CODE" WHERE (("COST_CODE" = '23-110') AND ("JOB" = '5001-261')) (Sequel::DatabaseError)
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/adapters/odbc.rb:40:in `bl
ock (2 levels) in execute'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/database/logging.rb:33:in
`log_yield'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/adapters/odbc.rb:40:in `bl
ock in execute'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/database/connecting.rb:229
:in `block in synchronize'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/connection_pool/threaded.r
b:104:in `hold'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/database/connecting.rb:229
:in `synchronize'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/adapters/odbc.rb:38:in `ex
ecute'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:906:in
`execute'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/adapters/odbc.rb:103:in `f
etch_rows'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:139:in
`each'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:639:in
`single_record'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:647:in
`single_value'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:246:in
`get'
        from C:/Ruby193/lib/ruby/gems/1.9.1/gems/sequel-4.12.0/lib/sequel/dataset/actions.rb:103:in
`count'
        from main.rb:50:in `<main>'

[lukeholder]

hmm it seems i probably need other parts of the Access adapter like emulate_offset_with_reverse_and_count which is in the Access adapter.

How can I try to use that adapter on my odbc connection?

i am connecting like this:

connection_string = 'driver={Timberline Data};dbq=\\\\doric-server19\\Timberline Office\\Gold\\DORIC GROUP\\;codepage=1252;dictionarymode=0;standardmode=1;maxcolsupport=1536;shortenames=0;databasetype=1;uid=l;pwd=AAA;'

DB = Sequel.odbc(:driver=>'Timberline Data',:drvconnect=>connection_string)

Thanks so much for your help, happy to pay @jeremyevans to get this odbc driver working.

[jeremyevans]

emulate_offset_with_reverse_and_count is not actually part of the Access support, it's a separate module that you can require. If you need offset emulation (are you actually using offsets?), this may work:

DB = Sequel.odbc(:driver=>'Timberline Data',:drvconnect=>connection_string)
Sequel.require 'adapters/utils/emulate_offset_with_reverse_and_count'
DB.extend_datasets do
  Sequel::Dataset.def_sql_method(self, :select, %w'select distinct limit columns from join where group order having compounds')

  def select_limit_sql(sql)
    if l = @opts[:limit]
      sql << ' TOP '
      literal_append(sql, l)
    end 
  end
end
DB.extend_datasets Sequel::EmulateOffsetWithReverseAndCount