connecting to mssql from rails

ruebenramirez commented 8 years ago

so I've got a rails app that I need to build some rake sync tasks...

ruebenramirez commented 8 years ago

I found TinyTDS - https://github.com/rails-sqlserver/tiny_tds

allows for direct queries
not diggin not using the rails convention of database.yml for environment config

# create a connection to the db
client = TinyTds::Client.new username: 'sa', password: 'secret', host: 'mydb.host.net'

# use the client to execute a query
result = client.execute("SELECT * FROM [datatypes]")

# process results
result.each do |row|
  # By default each row is a hash.
  # The keys are the fields, as you'd expect.
  # The values are pre-built Ruby primitives mapped from their corresponding types.
end

# inserts are a little different
result = client.execute("INSERT INTO [datatypes] ([xml]) VALUES ('<html><br/></html>')")
result.insert # => 420
# having to call result.insert threw me off a bit, but it works... (I guess it commits the transaction?)

ruebenramirez commented 8 years ago

So how do we setup credentials for multiple databases?

http://www.ostinelli.net/setting-multiple-databases-rails-definitive-guide/

ruebenramirez commented 7 years ago

parameterized queries with sequel + tinytds

require "tiny_tds"
require 'sequel' 

DB = Sequel.connect(
    adapter: 'tinytds', 
    host: "dataserver", 
    database: "database", 
    user: "username", 
    password: "password"
)

posts_table = DB[:posts]
posts_table.insert(:author => 'John Smith', :title => 'How to parametrize sql queries')

source: http://stackoverflow.com/a/31386672

ruebenramirez / blog

connecting to mssql from rails #307