Stored Procedures with Editor

Khalid Teli

Hi Allan,

I was just having a look , can stored procedure be used in editor serverside to read data from table?

https://datatables.net/forums/discussion/comment/152791/#Comment_152791

My table name is cont_test, and the stored procedure name is test . How would I tell editor to read data from Stored proceure rather than view?

 $editor  =  Editor::inst( $db, 'cont_test')
 ->readTable('test') // The VIEW to read data from
    ->field(

Thnak you in advance

allan

How would I tell editor to read data from Stored proceure rather than view?

You wouldn't I'm afraid. As I noted in the thread you linked to, Editor's server-side libraries do not work with stored procedures. You could read from one by using a view to access it and instruct Editor to read from the view.

Allan

Khalid Teli

@allan
Thank you.
I am not sure if can read a Mysql stored procedure from within View. Tried to find solution online but didn't find one.

However, I found the solution:

I used raw sql query as follows:

Where $_POST["cont_prod"] is the input parameter to the stored procedure.

    if (isset($_POST["cont_prod"]) && $_POST["cont_prod"] != '')
{
    $abc = $_POST["cont_prod"];

       $rawquery = "CALL contract_prod($abc)";
   $data = $db->sql( $rawquery )->fetchAll();
   echo json_encode( array(
      'data' => $data
   ) );
}

The client side code looks like this:
Because, based on the stored procedure input parameter, the returned columns from database vary and that is the reason table is populate dynamically

HTML code

Select Contracted Product: Tomatoes Milk

<button type="button" class="new" id="myclick" >Try it</button>


<div class="container">
            <table id="example" class="display" width="100%">
            </table>
        </div>

Js code

<script type="text/javascript" language="javascript">

var columns = [];

$(document).ready(function() {


  $('button.new').on( 'click', function () {

   var x = document.getElementById("mySelect").value;

  $.ajax({
                type:'POST',

         url: "/xxxxxxxxxxxxx/rawsql_test_fetch.php",
          data:  {cont_prod: JSON.stringify(x)},

              success: function (data) {
        data = JSON.parse(data);
        columnNames = Object.keys(data.data[0]);
        for (var i in columnNames) {
          columns.push({data: columnNames[i], 
                    title: columnNames[i]});
        }
      var table =    $('#example').DataTable( {

          data: data.data,
            columns: columns
        } );

      }
    });

} );


} );



</script>

Problem1 : on selecting the values from dropdown menu , on the second draw it gives error
DataTables warning: table id=example - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3

I looked at the above website, tried all solution but none is working

**Problem2 ** : are there any drawbacks of dynamically population the table data and headers?

Thank you

allan

are there any drawbacks of dynamically population the table data and headers?

No - we use it a lot ourselves, I know many others do as well.

DataTables warning: table id=example - Cannot reinitialise DataTable.

You've already got a DataTable with a matching ID. So you need to destroy and remove the old one first:

if ( $.fn.dataTable.isDataTable( '#example' ) ) {
    $('#example').DataTable().destroy();
    $('#example').empty();
}

Allan

Khalid Teli

@allan
Thank you