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

WALTERMedien

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

allan

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

WALTERMedien

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

allan

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

WALTERMedien

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

allan

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

WALTERMedien

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

WALTERMedien

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

allan

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

WALTERMedien

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

allan

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

Allan