Stored Procedures with Editor

Stored Procedures with Editor

Khalid TeliKhalid Teli Posts: 251Questions: 71Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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 TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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 TeliKhalid Teli Posts: 251Questions: 71Answers: 0

    @allan
    Thank you

Sign In or Register to comment.