Passing data between SQL queries and separate Ruby file

I'm using tiny_tds to pull data from a few different databases. As of right now, I have a Ruby file with multiple methods, each one devoted to a specific query (since the databases are very large, and not all of the scripts I'm using require the same kind/amount of data). To make things cleaner and easier, I wanted to separate out the SQL queries themselves into a single file, rather than have them embedded into the Ruby file containing the functions. But the SQL queries depend on certain fields having specific values. In essence, what I'm trying to do is send a variable to the SQL query, get data based on that particular value in a field, and feed that data back into the Ruby file.

So a simplified version of what I'm currently doing is this:

def initialize
    @client = TinyTds::Client.new(:username => '', :password => '', :host => '', timeout: 0)
end

def query_example(value)
    results = @client.execute("SELECT DISTINCT field1, field2, field3
        FROM db
        WHERE field1 = '#{value}'
    ")
    results.each {|x| return x}
end

The script calls the query_example(value) function, and based on the value variable, gets the relevant data for that case.

But what I would like is to essentially have one file that has nothing but the raw SQL queries, like:

SELECT DISTINCT field1, field2, field3
    FROM db
    WHERE field1 = '#{value}'

where the #{value} is populated by an external value fed to it (although I'm not sure how that kind of wildcard would be declared here). Let's say I save this file as "query.sql", then I would just want to read that file into the Ruby function like:

def query_example(value)
    query = File.read("query.sql")
    results = @client.execute(query)
end

The problem is I don't actually know how to pass that value argument into the SQL file itself, so that the data that is pulled with the execute command is the specific data for that value. Is this possible with tiny_tds, or is tiny_tds simply not made for this kind of two-way interaction between external SQL queries, and the Ruby functions that call them? I'm open to considering other SQL libraries, I'm just very unfamiliar with the options as I deal mostly with the Ruby side of things.

Solution

You can use format method to replace placeholders with actual values. Here is the simplest example:

template = "Hello, %{name}!"
format(template, name: "World")
=> "Hello, World!"

And your code may look like this:

# query.sql
SELECT DISTINCT field1, field2, field3
FROM db
WHERE field1 = '%{value}'

# ruby file
def query_example(value)
  query = File.read("query.sql")
  results = @client.execute(format(query, value: value))
end