aftk7 aftk7 - 6 months ago 45
Ajax Question

How can I call a PLSQL function in rails, by clicking a button in a view?

I have encountered a problem in Rails, which I cannot solve. I am pretty much a beginner, yet I must solve the following problem:

I need to build a type of dashboard page in an existing rails web app.
This dashboard page should be able to launch PLSQL procedures from an Oracle server, by providing 2 dates and clicking a button.

I tried using the

ruby-plsql
gem in
rails console
, and I am able to successfully call the wanted Oracle stored procedure.

I would like to create a view with a form to accept the date fields for the stored procedure, and then execute the stored procedure when the user clicks the submit button. The
ruby-plsql
method should get launched on submit. I also need to make sure that an already running procedure is not launched again, possibly by greying out the submit button, while the current procedure is running.

How can that be developed?

Answer

Add the ruby-plsql and 'haml' gems to your Gemfile:

gem 'ruby-plsql'
gem 'haml'

Next, you can use the Rails generator to flesh in a controller and the supporting components:

rails generate controller procedure execute

In the config/routes, update the get route to be a post route:

get 'procedure.execute'
post 'procedure/execute'

You can make a simple view (this example uses HAML). Add this code to the file app/views/procedure/execute.html.haml:

%div
  %p#notice
    = "A stored procedure is currently running.  Please wait..." if @is_running

%div
  = field_set_tag "Procedure 1" do
    = form_tag procedure_execute_path, id: "form1" do
      = hidden_field_tag "proc", "stored_proc_1"
      = text_field_tag "date1", nil
      = text_field_tag "date2", nil
      = submit_tag "Execute", disabled: @is_running

%div
  = field_set_tag "Procedure 2" do
    = form_tag procedure_execute_path, id: "form2" do
      = hidden_field_tag "proc", "stored_proc_2"
      = text_field_tag "string1", nil
      = text_field_tag "number1", nil
      = submit_tag "Execute", disabled: @is_running

%div#results
  = @results if @results

%script
  $(document).on("ready", register_ajax);
  $(document).on("page:change", register_ajax);

Make sure to add jQuery and Unobtrusive Javascript support to /app/assets/javascripts/application.js:

//= require jquery
//= require jquery_ujs

Verify that your config/application.rb contains this line within the Application` class:

config.assets.enable = true

Note that the forms now determine which method will be called, and they both send the request with their distinct arguments to the same execute action.

The Ajax code to submit the forms asynchronously and handle the results can be added to the /app/assets/javascripts/procedure.js file:

var ajax_registered = false;

function register_ajax() {
    if (ajax_registered) {
      return;
    }

    $('#form1 input[type="submit"], #form2 input[type="submit"]').click(function () {
        // First, disable all of the form buttons and put up a "running" notice
        $('form input[type="submit"]').prop("disabled", "disabled");
        $("#notice").text("A stored procedure is currently running.  Please wait...")

        var data = $(this).parent().serialize();

        // Submit the Ajax POST
        var jqxhr = $.post("/procedure/execute", data, function(data, status, xhr) {
            // Success: display the results in the #results div
            $("#results").text(data);
        }, "text")
        .fail(function(data, status, xhr) {
            // Notify the user that an error has occurred
            alert("An error has occured with the stored procedure");
        })
        .always(function(data, status, xhr) {
            // Always re-enable the submit buttons after completion
            $('form input[type="submit"]').prop("disabled", "");
            $("#notice").text("");
        });
    });

    ajax_registered = true;
};

Next, implement the execute action in the ProcedureController, which should be located at app/controllers/procedure_controller.rb:

class ProcedureController < ApplicationController
  @@running = false

  def execute
    if request.post?
      plsql.connection = OCI8.new("hr","hr","xe")
      notice = nil
      if @@running
        notice = "A stored procedure is currently running.  Try again later"
      else
        @@running = true
        proc_params = execute_params
        proc = proc_params.delete(:proc)
        case proc
        when "stored_proc_1"
          @results = plsql.my_stored_proc(proc_params)
        when "stored_proc_2"
          @results = plsql.my_other_stored_proc(proc_params)
        end
        @@running = false
      end
      if request.xhr?
        render text: @results.to_json and return
      else
        @is_running = @@running
        redirect_to procedure_execute_path, notice: notice
      end
    else
      @is_running = @@running
    end
  end

private

  def execute_params
    params.permit(:utf8, :authenticity_token, :proc, :date1, :date2, :string1, :number1)
  end
end

The execute action runs synchronously, but also uses the @@running flag to indicate that it's currently handling a request, in case another comes in from the dashboard before the currently-running proc has finished. The proc hidden field in the forms is used to indicate which stored procedure to execute, and the controller dispatches according to which is chosen.

If the request came from an Ajax call, the response will be a JSON object that contains the results of the stored procedure call; otherwise, the page will be fully rendered and the result will be included.

That should be enough to get going. It's not pretty, but the structure is there, and you can improve the execute method to do additional things, as well as style the view to your liking.