SQL-Error when adding an entry – never had this problem before

SQL-Error when adding an entry – never had this problem before

WALTERMedienWALTERMedien Posts: 6Questions: 1Answers: 0
edited October 10 in DataTables 1.10

We're recently facing issues when adding a new entry to our table via button. We get the following error:


SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "as" LINE 1: ...8, $9, $10, $11, $12, $13, $14 ) RETURNING as dt_pkey ^

We never used to have this problem before – and we don't know where it suddenly could come from.
Anybody out here who could help? Thanks!

Kind regards

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 48,282Questions: 1Answers: 6,967 Site admin

    Hi,

    Could you let me know:

    1. What server-side environment you are using?
    2. What database you are using?
    3. What version of Editor are you using (both client-side and server-side)?

    If you haven't updated to 1.8 (both client and server-side) then it would be worth doing so as we've addressed a few issues like this.

    Allan

  • WALTERMedienWALTERMedien Posts: 6Questions: 1Answers: 0

    Hey Allan,

    sorry I didn't mention these facts in my question already. We're running CentOS 7.4.1708 on our Server, with PHP 5.4.16 and PostgreSQL 9.5.10. Editor is version 1.5.6.

    I took a look at Editor 1.8. Is there anything I need to look for or is it just as simple as replacing old files with new ones?

    Kay

  • allanallan Posts: 48,282Questions: 1Answers: 6,967 Site admin

    From 1.5 it should just be a case of replacing the old files with the new ones. One thing worth noting is that the Editor PHP libraries have changed structure a little. not much, but one or two files such as Editor.php have moved up a directory, so you might be best to remove the old library files before installing the new ones.

    Allan

  • WALTERMedienWALTERMedien Posts: 6Questions: 1Answers: 0

    I tried to remove the old lib files and replaced them with the new 1.8 ones. But now I get the error message "DataTables warning: table id=xxx - Primary key element is not available in data set."

    Are there any paths I need to adjust because of the new structure of the PHP lib?

    Kay

  • allanallan Posts: 48,282Questions: 1Answers: 6,967 Site admin

    No. The autoloader will take care of that.

    Can you show me the PHP you are using? It sounds like you might be using an Mjoin and the primary key from the source table isn't in the data set.

    Allan

  • WALTERMedienWALTERMedien Posts: 6Questions: 1Answers: 0

    Here comes our PHP

    <?php
    
    // Jahr in Variable speichern
    $year = $_GET["year"];
    $name = 'kalenderproduktion_'.$year;
    
    // Zeitzone einstellen
    date_default_timezone_set('Europe/Berlin');
    
    /*
     * Editor server script for DB table kalenderproduktion_2019
     * Created by http://editor.datatables.net/generator
     */
    
    // DataTables PHP library and database connection
    include( "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\Upload,
        DataTables\Editor\Validate;
    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'kalenderproduktion_'.$year, 'id' )
        ->fields(
            Field::inst( 'artnr' )
                ->validator( 'Validate::notEmpty'),
            Field::inst( 'kalnr' ),
            Field::inst( 'kalender' )
                ->validator( 'Validate::notEmpty'),
            Field::inst( 'bildauswahl' ),
            Field::inst( 'gestaltung' ),
            Field::inst( 'bildunt' ),
            Field::inst( 'lyrtext' ),
            Field::inst( 'rueckseiten' )
                ->validator( 'Validate::notEmpty'),
            Field::inst( 'ebvscan' ),
            Field::inst( 'ebvproof' ),
            Field::inst( 'kalsatz' ),
            Field::inst( 'kallesen' ),
            Field::inst( 'busatz' ),
            Field::inst( 'bulesen' ),
            Field::inst( 'lyrsatz' ),
            Field::inst( 'lyrlesen' ),
            Field::inst( 'ruecksatz' ),
            Field::inst( 'ruecklesen' ),
            Field::inst( 'seitensatz' ),
            Field::inst( 'seitenlesen' ),
            Field::inst( 'printeranverl' ),
            Field::inst( 'pdf' ),
            Field::inst( 'jpgwebshop' ),
            Field::inst( 'datanctp' ),
            Field::inst( 'ausschiessen' ),
            Field::inst( 'formvor' ),
            Field::inst( 'formverl' ),
            Field::inst( 'platten' ),
            Field::inst( 'messe' ),
            Field::inst( 'bemerkung' ),
            Field::inst( 'kontrdat' )
                ->validator( 'Validate::dateFormat', array( 'format'=>'d.m.y' ) )
                ->getFormatter( 'Format::date_sql_to_format', 'd.m.y' )
                ->setFormatter( 'Format::date_format_to_sql', 'd.m.y' ),
            Field::inst( 'ferientermine' )
                ->validator( 'Validate::notEmpty'),
            Field::inst( 'sprache_kal' )
                ->validator( 'Validate::notEmpty'),
            Field::inst( 'created' )
                ->validator( 'Validate::dateFormat', array( 'format'=>'d.m.y' ) )
                ->getFormatter( 'Format::date_sql_to_format', 'd.m.y' )
                ->setFormatter( 'Format::date_format_to_sql', 'd.m.y' ),
            Field::inst( 'last_modified' )
                ->validator( 'Validate::dateFormat', array( 'format'=>'d.m.y' ) )
                ->getFormatter( 'Format::date_sql_to_format', 'd.m.y' )
                ->setFormatter( 'Format::date_format_to_sql', 'd.m.y' ),
            Field::inst( 'kis' ),
            Field::inst( 'tasche' )
        )
        ->process( $_POST )
        ->json();
    
    

    Kay

  • WALTERMedienWALTERMedien Posts: 6Questions: 1Answers: 0

    Does anybody else maybe know the problem here? We still couldn't fix it unfortunately. Are there any other informations we could provide to find the problem?

    Kay

  • allanallan Posts: 48,282Questions: 1Answers: 6,967 Site admin

    That is very odd! I don't see anything in your PHP that would cause an issue like this!

    Could you change ->process( $_POST ) to be:

    ->debug( true )
    ->process( $_POST )
    

    and then let me know what the JSON data that the server sends is (see this tech note for how to get that information if you aren't sure).

    Thanks,
    Allan

  • WALTERMedienWALTERMedien Posts: 6Questions: 1Answers: 0

    Hey Alan,

    we were able to solve the initial problem. We figured out someone messed up our database sigh. After solving this we were also able to upgrade to Editor 1.8 without any problems. Sorry for the inconveniences.

    Kay

  • allanallan Posts: 48,282Questions: 1Answers: 6,967 Site admin
    Answer ✓

    No worries. Good to hear you managed to track it down!

    Allan

Sign In or Register to comment.