I'd like to have a way to pass in `Execute as User` for raw SQL queries for
tinytds
I can sort of hack my way through it-
*Behavior I get*
2.1.8p440 :001 > a = Sequel.connect(
"tinytds://#{ENV['SQL_SERVER_development_USERNAME']}:#{ENV['SQL_SERVER_development_PASSWORD']}@#{ENV['SQL_SERVER_development_HOST']}:#{ENV['SQL_SERVER_development_PORT']}/#{ENV['SQL_SERVER_development_DB']}"
)
=> #<Sequel::TinyTDS::Database:
"tinytds://user:password@host:port/database">
## First thing we do is connect to the database, then run a query.
2.1.8p440 :002 > d = a['SELECT * FROM [secure].[Employee Snapshot Fact]
[Employee Snapshot Fact]']
=> #<Sequel::TinyTDS::Dataset: "SELECT * FROM [secure].[Employee Snapshot
Fact] [Employee Snapshot Fact]">
2.1.8p440 :003 > d.first
=> nil
2.1.8p440 :004 > d.count
=> 0
## We see that the connection doesn't work without the user passing through
2.1.8p440 :002 > d = a["EXECUTE AS USER='ADND\\user'; SELECT * FROM
[secure].[Employee Snapshot Fact] [Employee Snapshot Fact]"]
=> #<Sequel::TinyTDS::Dataset: "EXECUTE AS USER='ADND\\user'; SELECT *
FROM [secure].[Employee Snapshot Fact] [Employee Snapshot Fact]">
2.1.8p440 :003 > d.first
=> {:"employee key"=>126, :"job key"=>0, :"active faculty key"=>21, :"faculty
appointment key"=>6, :"date key"=>20020630}
## With the Execute as User - We get a result object back working normally
2.1.8p440 :004 > d.count
Sequel::DatabaseError: TinyTds::Error: Incorrect syntax near the keyword
'EXECUTE'.
from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/
tinytds.rb:220:in `fields'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/tinytds.rb:220:in
`block in fetch_rows'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/tinytds.rb:65:in
`block in execute'
from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/database/
connecting.rb:251:in `block in synchronize'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/connection_pool/threaded.rb:105:in
`hold'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/database/connecting.rb:251:in
`synchronize'
from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/
tinytds.rb:34:in `execute'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:952:in
`execute'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/adapters/tinytds.rb:219:in
`fetch_rows'
from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/
actions.rb:835:in `with_sql_each'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:843:in
`with_sql_first'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:661:in
`single_record!'
from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/
actions.rb:649:in `single_record'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:671:in
`single_value'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/actions.rb:249:in
`get'
from /usr/local/rvm/gems/ruby-2.1.8/gems/sequel-4.32.0/lib/sequel/dataset/
actions.rb:105:in `count'
from (irb):4
from
/usr/local/rvm/gems/ruby-2.1.8/gems/railties-4.0.13/lib/rails/commands/console.rb:90:in
`start'
from
/usr/local/rvm/gems/ruby-2.1.8/gems/railties-4.0.13/lib/rails/commands/console.rb:9:in
`start'
from /usr/local/rvm/gems/ruby-2.1.8/gems/railties-4.0.13/lib/rails/commands
.rb:62:in `<top (required)>'
from bin/rails:4:in `require'
## but the count method doesn't work
2.1.8p440 :005 > d = a['SELECT * FROM [secure].[Employee Snapshot Fact]
[Employee Snapshot Fact]']
=> #<Sequel::TinyTDS::Dataset: "SELECT * FROM [secure].[Employee Snapshot
Fact] [Employee Snapshot Fact]">
## We try again, this time WITHOUT the Execute as User statement
2.1.8p440 :006 > d.first
=> {:"employee key"=>126, :"job key"=>0, :"active faculty key"=>21, :"faculty
appointment key"=>6, :"date key"=>20020630}
2.1.8p440 :007 > d.count
=> 4167860
## Somehow the Execute as User statement has been saved? in the query
object and we are able to get back the row count. But then why didn't it
work the first time?
## Is that the ruby object counting or is it returning from the database? I
think it comes from the database because it only takes about a second for
it to compute as 4 million rows in ruby is a lot to compute.
## Maybe my sql query isn't 100% correct
*Behavior I want*
What I'd like to have is a `.count` method that works with Execute as User
statement, that would maybe tack on the Execute as User statement. Or just
have the original statement work.
d.count("EXECUTE AS USER='ADND\\user';")
=> 12345678
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
