0

I'm connecting to my company's database with the Sequel Gem using the odbc adaptor.

DB = Sequel.odbc('myserver', :user => "USER1", :password => "1234")

I connected fine but I was not able to get the dataset off of tables that belongs to other users.

When I connect to the database with Interactive SQL, I see the following tables.

USER1.TABLE1
USER1.TABLE2
USER1.TABLE3
USER2.TABLE4
USER2.TABLE5

When in interactive SQL. I have complete right to do anything with all the tables since user1 is a superuser. However, when I try to pull table4 or table5 with Sequel gem.

dataset = DB[:TABLE4].all.count

I get the following error.

Traceback (most recent call last):
       16: from C:/Ruby26-x64/bin/irb:23:in 'load'
       15: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/irb-1.2.0/exe/irb:11:in '<top (required)>'
       14: from (irb):17
       13: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/dataset/actions.rb:51:in 'all'
       12: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/dataset/actions.rb:1006:in `_all'
       11: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/dataset/actions.rb:51:in 'block in all'
       10: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/dataset/actions.rb:152:in 'each'
        9: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/adapters/odbc.rb:90:in 'fetch_rows'
        8: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/dataset/actions.rb:1089:in 'execute'
        7: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/adapters/odbc.rb:40:in 'execute'
        6: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/database/connecting.rb:270:in 'synchronize'
        5: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/connection_pool/threaded.rb:92:in 'hold'
        4: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/adapters/odbc.rb:42:in 'block in execute'
        3: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/database/logging.rb:38:in 'log_connection_yield'
        2: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/adapters/odbc.rb:42:in 'block (2 levels) in execute'
        1: from C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/adapters/odbc.rb:42:in 'run'
Sequel::DatabaseError (ODBC::Error: S0002 (-141) [Sybase][ODBC Driver][SQL Anywhere]Table 'TABLE4' not found)

However when using the same code to pull TABLE1, 2 or 3 is fine tho.

dataset = DB[:TABLE1].all.count

=>999

Maybe because I'm not looking up table 4 or 5 correctly? Or I have to somehow specify the owner of those table? In Interactive SQL I have to do USER2.TABLE4 to identify the table.

Many thanks!

Evilmuffin
  • 214
  • 4
  • 14

1 Answers1

2

You need to use a qualified identifier in Sequel, just as you need to in SQL:

dataset = DB[Sequel[:USER2][:TABLE4]].all.count

For more information, see:

Jeremy Evans
  • 11,959
  • 27
  • 26
  • Just out of curiosity. When I do dataset = DB[Sequel[:USER2][:TABLE4]] then dataset.count I get the following error. C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sequel-5.29.0/lib/sequel/adapters/odbc.rb:42:in `run': ODBC::Error: 37000 (-131) [Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'LIMIT' on line 1 (Sequel::DatabaseError). I will have to do dataset.all.count to get the # of records. Thanks! – Evilmuffin Oct 15 '20 at 14:07
  • Use the Sequel sqlanywhere adapter and not the odbc adapter. The Sequel odbc adapter does not have support for SQL Anywhere syntax. – Jeremy Evans Oct 16 '20 at 17:12