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 ex

Solution 1:

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 procedureexecute

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;

functionregister_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 POSTvar 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 occurredalert("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:

classProcedureController < ApplicationController@@running = falsedefexecuteif request.post?
      plsql.connection = OCI8.new("hr","hr","xe")
      notice = nilif@@running
        notice = "A stored procedure is currently running.  Try again later"else@@running = true
        proc_params = execute_params
        proc = proc_params.delete(:proc)
        caseprocwhen"stored_proc_1"@results = plsql.my_stored_proc(proc_params)
        when"stored_proc_2"@results = plsql.my_other_stored_proc(proc_params)
        end@@running = falseendif request.xhr?
        render text:@results.to_json andreturnelse@is_running = @@running
        redirect_to procedure_execute_path, notice: notice
      endelse@is_running = @@runningendend

private

  defexecute_params
    params.permit(:utf8, :authenticity_token, :proc, :date1, :date2, :string1, :number1)
  endend

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.