I have this prepared statement in Rails (postgres db):
conn = ActiveRecord::Base.connection.raw_connection
conn.prepare('query_fetch_dest_values',
"SELECT q.entity AS source_entity,
q.financial_institution AS source_financial_institution,
q.account_number AS source_account_number,
a2.entity AS destination_entity,
a2.financial_institution AS destination_financial_institution,
a2.account_number AS destination_account_number
FROM
(SELECT *
FROM forecast_entry_and_cash_positions
INNER JOIN accounts a ON forecast_entry_and_cash_positions.source_account_id = a.account_number
WHERE (input_type IN ($1) AND
a.financial_institution IN ($2) AND
forecast_entry_and_cash_positions.source_account_id IN ($3))
)q LEFT JOIN accounts a2 ON q.dest_account_id = a2.account_number")
search_destination_values =
conn.exec_prepared('query_fetch_dest_values',
[decode_input_type(params[:search_input_type]),
params[:search_source_financial_institution],
params[:search_source_account_number]
])
puts "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
puts search_destination_values.inspect
- The above SQL is not getting printed on my console. So how do I get to know if my query is being executed or not?
- In the
inspect
as shown in above code, this is what is getting printed on the console:
#<PG::Result:0x00007f9d32c528b8 status=PGRES_TUPLES_OK ntuples=0 nfields=6 cmd_tuples=0>
I learnt from this blog - https://blog.kiprosh.com/understanding-pg-result-object/ that nTuples=0
and cmd_tuples=0
in the above output means the number of records returned by the SQL is 0.
My query in pgadmin returns 3 records - why am I not getting any output here?
- This documentation https://rubydoc.info/gems/pg/PG/Connection#exec_prepared-instance_method and stackoverflow blogs like this one -> Prepared Statement on Postgresql in Rails show how to pass dynamic values for filters when the filter condition is an
=
(equals) condition on the filtered columns.
However in my condition I have 3 IN
filters. The documentation does not show an example for passing dynamic filters for IN
clauses. In the above code snippet, is the way am passing dynamic filters for my 3 IN
clauses correct? (Again, since my query is not getting printed on my console, I am unable to verify if my filters got added correctly to the SQL or not).