You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
where BLAZER_POSTGRES_DATABASE_URL points to a Postgres database and BLAZER_SQL_SERVER_DATABASE_URL points at a SQL Server database.
2. In Blazer's GUI, create and save a query against the Postgres database that uses the {start_time} and {end_time} variables, e.g.,
SELECT COUNT(*) FROM some_table_in_Postgres WHERE created_at >= {start_time} AND created_at < {end_time}
Run the query. Notice the error and the N'' quoting of the datetime values that were interpolated
I 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.,
SELECT COUNT(*) FROM some_table_in_Postgres WHERE created_at >= '2022-03-16T00:00:00Z'::timestamp AND created_at < '2022-04-14T23:59:59Z'::timestamp
The text was updated successfully, but these errors were encountered:
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
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.
2. In Blazer's GUI, create and save a query against the Postgres database that uses the {start_time} and {end_time} variables, e.g.,
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.,
The text was updated successfully, but these errors were encountered: