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?