Expose statement bind parameters

[captn3m0]

For a given prepared statement, it should be possible to fetch the list of parameters that can be bound to it.

The 3 relevant SQLite methods are:

• https://sqlite.org/c3ref/bind_parameter_count.html
• https://sqlite.org/c3ref/bind_parameter_name.html
• https://sqlite.org/c3ref/bind_parameter_index.html

Without these 3, if you are processing parameters on configurable queries, it is hard to bind only stuff that's needed.

For a specific usecase, I do the following in my Jekyll-SQLite extension:

##
# Prepare the query by binding the parameters
# Since we don't know if the query needs them
# we ignore all errors about "no such bind parameter"
def _prepare_query(stmt, params)
  params.each do |key, value|
    stmt.bind_param key, value
  rescue StandardError => e
    raise e unless e.message.include? "no such bind parameter"
  end
end

params is a dict, but because it is inherited from Jekyll's page data structure, it can contain keys that are not required by the query (such as title). So we check the exception and ignore for now.

[flavorjones]

@captn3m0 Thanks for starting this discussion.

One of these is already implemented: https://sparklemotion.github.io/sqlite3-ruby/SQLite3/Statement.html#method-i-bind_parameter_count

I'm open to adding the other binding details. If you're interested in implementing a solution and submitting a pull request, I'd be happy to review it. If you're not sure about how to implement it, I'm happy to discuss and give some advice. But I'm unlikely to do this work myself.

[flavorjones]

Question, though: Is there a reason you don't track of what parameters are in the statement when you construct it and prepare it? I think something like this would be sufficient:

stmt = @db.prepare("select * from foo where title = :title and author = :author")
stmt_params = [:title, :author]

stmt_params.each do |p|
  stmt.bind_param p, params[p]
end

It's not much less clear or longer than the alternative I'm imagining using the sqlite API:

stmt = @db.prepare("select * from foo where title = :title and author = :author")

stmt.named_params.each do |p|
  stmt.bind_param p, params[p]
end

[flavorjones]

Without a specific implementation, it's hard to imagine what you're imagining specifically. But another alternative might be to just improve Statement.bind_params to only bind variables that are found in the bind_vars hash, so something like:

    def bind_params(*bind_vars)
      index = 1
      bind_vars.flatten.each do |var|
        if Hash === var
          var.each { |key, val| bind_param(key, val) if named_params.include?(key) }
        else
          bind_param index, var
          index += 1
        end
      end
    end

Just thinking out loud.

[captn3m0]

Forgot to include the plugin link: https://github.com/captn3m0/jekyll-sqlite

Since this is a plugin used in other sites, the queries come from the end-users in YAML. The bind parameter values are assumed to come from the site/page front-matter.

I can ask users to specify a list of bind parameters but that feels redundant when I can get that list from the SQL statement.

[captn3m0]

stmt.named_params looks perfect for my usecase.

[flavorjones]

Thanks for the additional context.

If you're interested in implementing a solution and submitting a pull request, I'd be happy to review it. If you're not sure about how to implement it, I'm happy to discuss and give some advice.

[captn3m0]

I'll give it a shot and get back, thanks.