Ruby Grape POSTGRESQL stored procedure

Question

Trying to execute a POSTGRESQL stored procedure/function in Ruby Grape API Package. I have the function getactivesites() in my server which returns the site name.

server code:

BEGIN

    RETURN QUERY SELECT "SITE_ID",
            "SITE_NAME"
        FROM public.sites WHERE "ACTIVE_FLAG" = true
        ORDER BY "SITE_NAME" ASC ;
    END;

ruby code:

resource :getsites do

desc “Get Active Sites“
get do
  results =  ActiveRecord::Base.connection.execute("execute getactivesites")
  return results
end

The error I get when I run it is “PG::InvalidSqlStatementName: ERROR: prepared statement "getactivesites" does not exist : execute getactivesites”

Does the getactivesites procedure work if you run it directly in the postgres console? — Tom Lord
– Tom Lord, Commented Jun 8, 2017 at 13:29
For example, is the procedure defined in your development database but not your test database? (If you have multiple databases?...) — Tom Lord
– Tom Lord, Commented Jun 8, 2017 at 13:32
Yes, getactivesites runs when I run in my postgres db. I have it in my development database and run my ruby on rails project on that same database. — Swish2121
– Swish2121, Commented Jun 8, 2017 at 14:15
...But is that line failing when you run a test? Double check which database ActiveRecord::Base.connection is connecting to, when that line executes, in whatever context you are seeing the failure. — Tom Lord
– Tom Lord, Commented Jun 8, 2017 at 14:47
@mutantkeyboard tried running by removing the execute get the error "PG::SyntaxError: ERROR: syntax error at or near "getactivesites" LINE 1: getactivesites ^ : getactivesites" — Swish2121
– Swish2121, Commented Jun 8, 2017 at 18:34

Pavel Mikhailyuk · Accepted Answer · 2017-06-09 14:55:54Z

2

Try select instead of execute:

ActiveRecord::Base.connection.execute("select getactivesites()")

Here is from my real proj:

Db.execute( 'select my_sp_name( ? )', my_sp_param )

where Db is sugar like:

# -*- frozen-string-literal: true -*-
# Syntax sugar module
module Db
  extend self

  delegate :transaction, to: ApplicationRecord

  def sanitize( *args )
    ApplicationRecord.send( :sanitize_sql, args )
  end

  def execute( *args )
    ApplicationRecord.connection.execute( sanitize( *args ) )
  end

end

edited Jun 9, 2017 at 14:55

answered Jun 9, 2017 at 12:51

Pavel Mikhailyuk

2,87711 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Swish2121 Over a year ago

Worked perfectly, Thanks!

Pavel Mikhailyuk Over a year ago

@Swish2121 Can you mark the answer as correct? Thanks

Swish2121 Over a year ago

To run that first piece of code need to add () to have: ActiveRecord::Base.connection.execute("select getactivesites()")

Pavel Mikhailyuk Over a year ago

@Swish2121 Ok, fixed the example. All SP in my proj. have params, so I couldn't test SP call without ones.

Collectives™ on Stack Overflow

Ruby Grape POSTGRESQL stored procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related