0

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
  1. 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?
  2. 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?

  1. 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).

halfer
  • 19,824
  • 17
  • 99
  • 186
Biju
  • 820
  • 1
  • 11
  • 34
  • are doing that in a rails console ?. Try setting `ActiveRecord::Base.logger = Logger.new(STDOUT)` and then execute statement, if you see it in the console, then it got executed. – fanta Jan 09 '20 at 18:08
  • I have the logger setup. I am able to see other queries getting printed in my app – Biju Jan 09 '20 at 18:13

0 Answers0