0

I am trying to execute a query that selects recipes that match a search term from user input stored in the query variable. This is the portion of relevant code:

class DatabasePersistence
  def initialize(logger)
    @db = if Sinatra::Base.production?
        PG.connect(ENV['DATABASE_URL'])
      else
        PG.connect(dbname: "recipes")
      end
    @logger = logger
  end

  def search_recipes(query)
    p "Query parameter is:"
    p query
    p query.class
    sql = <<~SQL
    SELECT * FROM recipes
    WHERE labels ILIKE '%$1::text%'
    SQL
    results = query(sql, query)
    # ... more code
  end

  def query(statement, *params)
    @logger.info "#{statement}: #{params}"
    @db.exec_params(statement, params)
  end

end

The following error is raised on when this line results = query(sql, query) is executed.

PG::IndeterminateDatatype at /search
ERROR: could not determine data type of parameter $1

Another post suggested adding an explicit type cast which is why I added the type cast. I could be doing it incorrectly. I also tried it like the following:

WHERE labels ILIKE '%text($1)%'
WHERE labels ILIKE '%cast($1 as text)%'
WHERE labels ~ '$1::text'
WHERE labels ~ 'cast($1 as text'

In all of the above cases it returned the same error "could not determine the datatype of parameter. I added some #p method calls to make sure the query variable is referencing a real value for debuggin. I have confirmed that this error occurs when the query references a string object with value oats.

What is causing this error to still occur if I am casting the datatype and it is not nil? Am I passing the parameters incorrectly? Am I casting the parameters incorrectly? Is it possible there is a way to pass datatypes as arguments to the #exec_params method? Is there another way to safely pass parameters to be executed by the instance of the PG.connect class?

1 Answer 1

0

Simply:

WHERE labels ILIKE $1::text

I assume labels is a plain character type like text, too.

Sign up to request clarification or add additional context in comments.

1 Comment

Yes labels is plain text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.