Closed HotFusionMan closed 2 years ago
I think I see why it behaves this way. The last executable line of Blazer::BaseController#process_vars
calls ActiveRecord::Base.connection.quote(value)
, but that may not be the appropriate connection for the database the query is targeting.
I've hacked that line to be the following, and it works, but I doubt it's production quality:
if ["start_time", "end_time"].include?(var)
statement.gsub!("{#{var}}", "#{Blazer.data_sources[data_source].__send__(:adapter_instance).connection_model.connection.quote(value)}::timestamp") if Blazer.data_sources[data_source].settings["url"].start_with?("postgres://")
else
statement.gsub!("{#{var}}", Blazer.data_sources[data_source].__send__(:adapter_instance).connection_model.connection.quote(value))
end
Hey @HotFusionMan, thanks for reporting! More info on the issue: #392
We have both a Postgres database and a (legacy) SQL Server database that Blazer is able to query. When using the {start_time} and {end_time} variables in a query that select from one of the Postgres database's tables, those time variables are interpolated into the query as strings using SQL Server's quoting style that looks like
N'the string contents'
instead of'the string contents'
as Postgres uses.Steps to reproduce:
where BLAZER_POSTGRES_DATABASE_URL points to a Postgres database and BLAZER_SQL_SERVER_DATABASE_URL points at a SQL Server database.
N''
quoting of the datetime values that were interpolatedI tried
gem "blazer", github: "ankane/blazer"
in the Gemfile and generated Gemfile.lock and saw no change in behavior.In addition to omitting the leading "N", I've found that I had to cast the generated datetime strings in order for Postgres to successfully run the query, e.g.,