Error while doing a CSV import

Error while doing a CSV import

lintu98lintu98 Posts: 11Questions: 4Answers: 0

Description of problem: I am using DataTables Editor and while doing a CSV import, got the following error.

Error messages shown: A system error has occurred (More information).

From Chrome Develop Tool: Fatal error: Uncaught PDOException: There is no active transaction in /var/app/current/plugins/datatables/lib/Database/Query.php:198 Stack trace: #0 /var/app/current/plugins/datatables/lib/Database/Query.php(198): PDO->rollBack() #1 [internal function]: DataTables\Database\Query::rollback(Object(PDO)) #2 /var/app/current/plugins/datatables/lib/Database.php(314): call_user_func('DataTables\Data...', Object(PDO)) #3 /var/app/current/plugins/datatables/lib/Editor.php(707): DataTables\Database->rollback() #4 /var/app/current/plugins/Cyberator/views/controls_library-data.php(78): DataTables\Editor->process(Array) #5 {main} thrown in /var/app/current/plugins/datatables/lib/Database/Query.php on line 198

Could someone guide me on how to fix this issue? Thank you.

Answers

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Does everything else work normally? Can you add, delete and modify records in the table?

    Colin

  • lintu98lintu98 Posts: 11Questions: 4Answers: 0

    Hi @colin,

    Thanks for your response. Yes, everything else (add, delete and modify records) is working fine.

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    Can you show me the PHP code you are using for this please?

    Thanks,
    Allan

  • lintu98lintu98 Posts: 11Questions: 4Answers: 0

    Hi Allan,

    Sorry for the delay in posting the code snippet below:

    _<head>
    
    <!-- link file-->
    <!-- css and media files -->
        <link rel='stylesheet' type='text/css' href='https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css'>
        <link rel='stylesheet' type='text/css' href='https://cdn.datatables.net/buttons/1.6.1/css/buttons.dataTables.min.css'>
        <link rel='stylesheet' type='text/css' href='https://cdn.datatables.net/select/1.3.1/css/select.dataTables.min.css'>
        <link rel='stylesheet' type='text/css' href='../plugins/datatables/css/editor.dataTables.min.css'>
    
        <script type='text/javascript' language='javascript' src='https://code.jquery.com/jquery-3.3.1.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdn.datatables.net/select/1.3.1/js/dataTables.select.min.js'></script>
        <script type='text/javascript' language='javascript' src='../plugins/datatables/js/dataTables.editor.js'></script>  
        <script type='text/javascript' language='javascript' src='../plugins/datatables/js/dataTables.editor.min.js'></script>  
        <script type='text/javascript' language='javascript' src='https://cdnjs.cloudflare.com/ajax/libs/PapaParse/4.6.3/papaparse.min.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js'></script>
        <script type='text/javascript' language='javascript' src='https://cdn.datatables.net/buttons/1.6.1/js/buttons.print.min.js'></script>
    
    
        <script type='text/javascript' language='javascript' class='init'>
        // Use a global for the submit and return data rendering in the examples.
        // Don't do this outside of the Editor!
    var editor;
     
    // Display an Editor form that allows the user to pick the CSV data to apply to each column
    function selectColumns ( editor, csv, header ) {
        var selectEditor = new $.fn.dataTable.Editor();
        var fields = editor.order();
     
        for ( var i=0 ; i<fields.length ; i++ ) {
            var field = editor.field( fields[i] );
     
            selectEditor.add( {
                label: field.label(),
                name: field.name(),
                type: 'select',
                options: header,
                def: header[i]
            } );
        }
     
        selectEditor.create({
            title: 'Map CSV fields',
            buttons: 'Import '+csv.length+' records',
            message: 'Select the CSV column you want to use the data from for each field.'
        });
     
        selectEditor.on('submitComplete', function (e, json, data, action) {
            // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
            editor.create( csv.length, {
                title: 'Confirm import',
                buttons: 'Submit',
                message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
            } );
     
            for ( var i=0 ; i<fields.length ; i++ ) {
                var field = editor.field( fields[i] );
                var mapped = data[ field.name() ];
     
                for ( var j=0 ; j<csv.length ; j++ ) {
                    field.multiSet( j, csv[j][mapped] );
                }
            }
        } );
    }
     
    $(document).ready(function() {
        // Regular editor for the table
        editor = new $.fn.dataTable.Editor( {
            ajax: '/plugins/AppName/views/controls_library-data.php',
            table: '#example',
            fields: [ {
                    label: 'ID:',               
                    name: 'id'
                },{
                    label: 'Domain:',
                    name: 'domain'
                },{
                    label: 'Control ID:',
                    name: 'control_id'
                },{
                    label: 'Title:',
                    name: 'title'
                },{
                    type:  'textarea',
                    label: 'Description:',
                    name: 'description'
                },{
                    label: 'Tags:',
                    name: 'tags'
                }, {
                    label: 'Frequency:',
                    name: 'frequency'
                }, {
                    label: 'Level:',
                    name: 'level'
                }
            ]
        } );
    
          
        // Upload Editor - triggered from the import button. Used only for uploading a file to the browser
        var uploadEditor = new $.fn.dataTable.Editor( {
            fields: [ {
                label: 'CSV file:',
                name: 'csv',
                type: 'upload',
                ajax: function ( files ) {
                    // Ajax override of the upload so we can handle the file locally. Here we use Papa
                    // to parse the CSV.
                    Papa.parse(files[0], {
                        header: true,
                        skipEmptyLines: true,
                        complete: function (results) {
                            if ( results.errors.length ) {
                                console.log( results );
                                uploadEditor.field('csv').error( 'CSV parsing error: '+ results.errors[0].message );
                            }
                            else {
                                uploadEditor.close();
                                selectColumns( editor, results.data, results.meta.fields );
                            }
                        }
                    });
                }
            } ]
        } );
     
        var myDataTable = $('#example').DataTable( {
            dom: 'Bfrtip',
            ajax: '/plugins/AppName/views/controls_library-data.php',
            
            columns: [
                { data: 'id' },
                { data: 'domain' },
                { data: 'control_id' },
                { data: 'title' },
                { data: 'description' },
                { data: 'tags' },
                { data: 'frequency' },
                { data: 'level' },
                { data: null, render: function ( data, type, row ) {
                    //Create a link to the edit page
                    return '&nbsp;&nbsp;<a href=\"/admin/pluginhelper?sa=sidebody&plugin=AppName&method=actioneditcontrol&section=AppName&var1=khjhg&parentid=$parentid&id='+data.id+'\"><span class=\"icon-edit \"> </span></a>';
                }},
            ],
            select: true,
            buttons: [
                { extend: 'create', editor: editor },
                { extend: 'edit',   editor: editor },
                { extend: 'remove', editor: editor },
                {
                    extend: 'csv',
                    text: 'Export CSV',
                    title: 'Control Library Export',
                    className: 'btn-space',
                    exportOptions: {
                        orthogonal: null
                    }
                },
                {
                    text: 'Import CSV',
                    action: function () {
                        uploadEditor.create( {
                            title: 'CSV file import'
                        } );
                    }
                },
                {
                    extend: 'pdfHtml5', 
                    title: 'Control Library Export',
                    exportOptions: {
                        modifier: {
                            page: 'current'                     
                        }
                    }
                },            
                {
                    extend: 'selectAll',
                    className: 'btn-space'
                },
                'selectNone',
            ]
        } );
        myDataTable.column( 0 ).visible( false );
    } );
    
    </script>
    </head>_
    

    Serverside code:

    _<?php
    include( "../../datatables/lib/DataTables.php" );
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
     
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'org_controls_library' )
        ->fields(
            Field::inst( 'id' ),                             
            Field::inst( 'domain' ),
            Field::inst( 'control_id' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Control ID is required' ) 
                ) ),
            Field::inst( 'title' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Control Title is required' ) 
                ) ),            
            Field::inst( 'description' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Control Description is required' )  
                ) ),  
            Field::inst( 'tags' ),
            Field::inst( 'frequency' ),        
            Field::inst( 'level' )->set( Field::SET_CREATE )
        )
        ->where( 'parent_uid', $_SESSION["parentid"] )
        ->on( 'preCreate', function ( $editor, $values ) {
                $editor
                    ->field('parent_uid')
                    ->setValue($_SESSION["parentid"]);
            } )  
        
        ->process( $_POST )
        ->json();
    ?>_
    

    Thank you!

  • lintu98lintu98 Posts: 11Questions: 4Answers: 0

    Hi @allan,

    Do you see any error on the code that I posted above?

    Thanks

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    There is nothing immediately obviously wrong there. How big is your CSV? Does it work if you just have one or two lines?

    Just before the ->process($_POST) call, could you add:

    ->tryCatch(false)
    ->debug(true)
    

    what does the server then return?

    Thanks,
    Allan

  • lintu98lintu98 Posts: 11Questions: 4Answers: 0

    Hi @allan,

    Here are the responses to your question:

    How big is your CSV? It has 114 lines
    Does it work if you just have one or two lines? I tried it with 3 lines and that failed too.

    I add your code before the ->process($_POST) call and it returned the following:

    Fatal error: Uncaught PDOException: There is no active transaction in /var/app/current/plugins/datatables/lib/Database/Query.php:165
    Stack trace:

    commit()">commit()">0 /var/app/current/plugins/datatables/lib/Database/Query.php(165): PDO->commit()

    1 [internal function]: DataTables\Database\Query::commit(Object(PDO))

    2 /var/app/current/plugins/datatables/lib/Database.php(116): call_user_func('DataTables\Data...', Object(PDO))

    commit()">commit()">3 /var/app/current/plugins/datatables/lib/Editor.php(1040): DataTables\Database->commit()

    _process(Array)">_process(Array)">4 /var/app/current/plugins/datatables/lib/Editor.php(712): DataTables\Editor->_process(Array)

    process(Array)">process(Array)">5 /var/app/current/plugins/Cyberator/views/controls_library-data.php(79): DataTables\Editor->process(Array)

    6 {main}

    thrown in /var/app/current/plugins/datatables/lib/Database/Query.php on line 165

    Appreciate your help!!

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    Interesting! Nothing shown before that?

    What does it return if you do this before your Editor::inst(...):

    print_r(
      $db->select('org_controls_library', '*')->fetchAll()
    );
    

    That will just select all from the table and show the array. It's just to check that there is a valid SQL db connection.

    Thanks,
    Allan

  • sbn111sbn111 Posts: 2Questions: 0Answers: 0

    My data is showing as like this in jquery data table - https://prnt.sc/vxzy3r

    But it not exporting fully field - https://prnt.sc/vxzzb5">https://prnt.sc/vxzzb5https://prnt.sc/vxzzb5

    Kindly help to fix the issue. I need all values from the table to excel.

    Thanks

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    @sbn111 We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Cheers,

    Colin

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    @sbn111 - I'm not clear on how your post relates to the original question in this thread? If it is a different issue, could you open a new thread with a link to a test case showing the issue, and also a sample of the CSV you are using (assuming you are doing a CSV import) so we can figure out what is going on?

    Thanks,
    Allan

  • lintu98lintu98 Posts: 11Questions: 4Answers: 0

    Hi @allan,

    I added print_r( $db->select('org_controls_library', '*')->fetchAll() ); before the code Editor::inst(...):

    When the page loads it throws this error:

    DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Have you followed the diagnostic steps in the technical note that's linked in the error message? That'll be the place to start,

    Colin

  • kilitkilit Posts: 1Questions: 0Answers: 0

    Hello,

    I have the exact same problem here, except the import works up to 47 lines.
    Above this number of lines it throws the same transaction error.

    let editor = null;
    
    function selectColumns(editor, csv, header) {
        const selectEditor = new $.fn.dataTable.Editor();
        const fields = editor.order();
    
        for (let i = 0; i < fields.length; i++) {
    
            let field = editor.field(fields[i]);
    
            selectEditor.add({
                label: field.label(),
                name: field.name(),
                type: 'select',
                options: header,
                def: header[i + 1]
            });
        }
    
        selectEditor.create({
            title: dico.dataTable.import.modal_1.title,
            buttons: dico.dataTable.btn_validate,
            message: dico.dataTable.import.modal_1.message
        });
    
        selectEditor.on('submitComplete', (e, json, data, action) => {
            editor.create(csv.length, {
                title: dico.dataTable.import.modal_2.title,
                buttons: dico.dataTable.btn_validate,
                message: dico.dataTable.import.modal_2.message
            });
    
            for (let i = 0; i < fields.length; i++) {
                const field = editor.field(fields[i]);
                const mapped = data[field.name()];
                for (let j = 0; j < csv.length; j++) {
                    field.multiSet(j, csv[j][mapped]);
                }
            }
        });
    }
    
    let uploadEditor = new $.fn.dataTable.Editor({
            fields: [{
                label: dico.dataTable.export.label,
                name: 'csv',
                type: 'upload',
                ajax: (files, done) => {
                    Papa.parse(files[0], {
                        header: true,
                        skipEmptyLines: true,
                        complete: (results) => {
                            if (results.errors.length) {
                                uploadEditor.field('csv').error(dico.dataTable.export.error_message + results.errors[0].message);
                            } else {
                                uploadEditor.close();
                                selectColumns(editor, results.data, results.meta.fields);
                            }
                            done([0]);
                        }
                    });
                }
            }]
        });
    

    Error


    Exception: PDOException There is no active transaction Stack frames (8): 8 PDOException /var/www/html/workspace/library/datatables/core/Database/Query.php :204 7 PDO /var/www/html/workspace/library/datatables/core/Database/Query.php :204 6 call_user_func /var/www/html/workspace/library/datatables/core/Database.php :314 5 DataTables\Database /var/www/html/workspace/library/datatables/core/Editor.php :708 4 DataTables\Editor /var/www/html/workspace/json/class.Subject.php :73 3 Json\Subject /** * Rollback the database state to the start of the transaction. * @param \PDO $dbh The Database handle (typically a PDO object, but not always). */ public static function rollback ( $dbh ) { $dbh->rollBack(); } /** * Common helper for the drivers to handle a PDO DSN postfix
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    This error sounds to me like you reached some kind of limit on the server-side. Given that it works up to 47 rows, I'm thinking that it is probably a limit on the number of HTTP parameters that PHP has been configured with. What is your max_input_vars currently set to (use phpinfo() if you aren't sure)?

    This article is quite good, relating to how to change it.

    Allan

Sign In or Register to comment.