Error while doing a CSV import

lintu98 · November 2020

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.

colin · November 2020

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

Colin

lintu98 · November 2020

Hi @colin,

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

allan · November 2020

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

Thanks,
Allan

lintu98 · December 2020

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();

<?php >_ ``` Thank you! ?>

lintu98 · December 2020

Hi @allan,

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

Thanks

allan · December 2020

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

lintu98 · December 2020

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()" href="#0-/var/app/current/plugins/datatables/lib/Database/Query.php(165):-PDO->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()" href="#3-/var/app/current/plugins/datatables/lib/Editor.php(1040):-DataTables\Database->commit()">3 /var/app/current/plugins/datatables/lib/Editor.php(1040): DataTables\Database->commit()

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

process(Array)">process(Array)" href="#5-/var/app/current/plugins/Cyberator/views/controls_library-data.php(79):-DataTables\Editor->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!!

allan · December 2020

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

sbn111 · December 2020

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/vxzzb5 https://prnt.sc/vxzzb5

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

Thanks

colin · December 2020

@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

allan · December 2020

@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

lintu98 · December 2020

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

colin · December 2020

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

kilit · June 2021

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

allan · June 2021

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

Error while doing a CSV import

Error while doing a CSV import

Answers

commit()">commit()" href="#0-/var/app/current/plugins/datatables/lib/Database/Query.php(165):-PDO->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()" href="#3-/var/app/current/plugins/datatables/lib/Editor.php(1040):-DataTables\Database->commit()">3 /var/app/current/plugins/datatables/lib/Editor.php(1040): DataTables\Database->commit()

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

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

6 {main}

Howdy, Stranger!

Categories

DataTables

Error while doing a CSV import

Error while doing a CSV import

Answers

commit()">commit()" href="#0-/var/app/current/plugins/datatables/lib/Database/Query.php(165):-PDO->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()" href="#3-/var/app/current/plugins/datatables/lib/Editor.php(1040):-DataTables\Database->commit()">3 /var/app/current/plugins/datatables/lib/Editor.php(1040): DataTables\Database->commit()

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

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

6 {main}

Howdy, Stranger!

Quick Links

Categories

DataTables