lundi 20 avril 2015

Database query method returns different things using same parameters

I have a class that builds a database query called SelectQueryBuilder. In one method, when I use the class, the query returns nil, but when I use the literal values, the query returns the result set.

def first condition, value
  select_query_builder = SelectQueryBuilder.new("*", table)
  select_query_builder.where(condition, value)
  select_query_builder.build

  # connection is a SQLite3::Database object    
  connection.get_first_row(select_query_builder.sql, select_query_builder.values)
end

The method returns nil even when I pass in conditions that are in the database. However, when I inspect select_query_builder.sql and select_query_builder.values and put the literal string and array that it produces, I get a result set. I can even do this:

def first condition, value
  select_query_builder = SelectQueryBuilder.new("*", table)
  select_query_builder.where(condition, value)
  select_query_builder.build

  puts select_query_builder.sql.eql?("SELECT * FROM user WHERE slug = ?")  # => true
  puts select_query_builder.values.eql?(["user1-example"])  # => true
  puts connection.get_first_row("SELECT * FROM user WHERE slug = ?", ["user1-example"])  # returns a result set
  connection.get_first_row(select_query_builder.sql, select_query_builder.values)
end

And the method will still return nil.

Here's the SelectQueryBuilder class:

class SelectQueryBuilder
  attr_reader :sql

  def initialize columns, table
    @columns = columns
    @table = table
  end

  def where condition, value
    conditions << condition
    values << value
  end

  def build
    self.sql = "SELECT #{columns} FROM #{table}#{build_clauses}"
  end

  def values
    @values ||= []
  end

  private
  attr_reader :columns, :table
  attr_writer :sql

  def build_clauses
    build_where + build_order
  end

  def build_where
    return "" if conditions.empty?

    conditions_str = conditions.map {|c| "#{c} = ?" }.join(" AND ")
    " WHERE #{conditions_str}"
  end

  def conditions
    @conditions ||= []
  end
end

I have also:

  • Created a method that returns the literals. This printed the result set.
  • Used the @sql and @values instance variables directly. This printed nil.
  • Set the results of select_query_builder.sql and select_query_builder.values to local variables and passing those in. This printed nil.

I tried to make an SSCCE, but I couldn't even reproduce the problem using the same database. It's like the values shape-shifted for just that particular database query.

How do I get the database to return the same result set when the values are passed in using the SelectQueryBuilder as when they are passed in using literals?

Aucun commentaire:

Enregistrer un commentaire