Update to 2.0 create not working

rwgreenwood

We are updating to editor 2.0 and create is not working. In the debugSQL, the error (An SQL error occurred: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type integer: "") shows that the select statement after the create has a blank id instead of the newly created id.

{
"name": ":where_1",
"value": "",
"type": null
}

The database setup is unchanged, it is a postgres database and the id field has a sequence. I use this on several different tables, same result on all of them.

I haven't found anything in release notes to indicate any changes that would impact this.
Thanks in advance for any help.

allan

Hi,

I'm not aware of any changes that would cause that error I'm afraid and it is passing our tests with Postgres. It sounds like the error is happening when trying to get the primary key value for the newly inserted row.

Could you show me your PHP and also the JSON return from the server when creating a new row?

Thanks,
Allan

rwgreenwood

Allan,
here is the json - seems to fail on the SELECT after the INSERT :

{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type integer: \"\"","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"INSERT INTO  \"application\"  ( \"dev_id\", \"appnum\", \"type\", \"status\", \"description\", \"comment_date\", \"comments\" ) VALUES (  :dev_id,  :appnum,  :type,  :status,  :description,  :comment_date,  :comments )","bindings":[{"name":":dev_id","value":"1841","type":null},{"name":":appnum","value":"222","type":null},{"name":":type","value":"7","type":null},{"name":":status","value":"2","type":null},{"name":":description","value":"","type":null},{"name":":comment_date","value":null,"type":null},{"name":":comments","value":"","type":null}]},{"query":"SELECT  \"application\".\"id\" as \"application.id\", \"application\".\"dev_id\" as \"application.dev_id\", \"application\".\"appnum\" as \"application.appnum\", \"application\".\"type\" as \"application.type\", \"application\".\"status\" as \"application.status\", \"application\".\"description\" as \"application.description\", \"application\".\"comment_date\" as \"application.comment_date\", \"application\".\"comment_cal\" as \"application.comment_cal\", \"application\".\"sufficiency_date\" as \"application.sufficiency_date\", \"application\".\"comments\" as \"application.comments\", \"application\".\"comment_status\" as \"application.comment_status\", \"application\".\"plan_comission_date\" as \"application.plan_comission_date\", \"application\".\"elected_date\" as \"application.elected_date\", \"application\".\"elected_decision\" as \"application.elected_decision\", \"application_status\".\"status\" as \"application_status.status\", \"application_type\".\"type\" as \"application_type.type\" FROM  \"application\" LEFT JOIN \"application_type\" ON \"application_type\".\"id\" = \"application\".\"type\"  LEFT JOIN \"application_status\" ON \"application_status\".\"id\" = \"application\".\"status\" WHERE \"dev_id\" = :where_0 AND  \"application\".\"id\" = :where_1 ","bindings":[{"name":":where_0","value":"1841","type":null},{"name":":where_1","value":"","type":null}]}]}

and here is the PHP:

<?php

/*
 * Editor server script for DB table intake_employment
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection

require_once 'GreenwoodMap/housing/vendor/autoload.php';
require_once "datatables-2.0/DataTables.php";


// Alias Editor classes so they are easy to use
use DataTables\Editor;
use DataTables\Editor\Field;

$dev_id = "";

if (isset($_REQUEST['dev_id']) && $_REQUEST['dev_id']) {
    $dev_id = $_REQUEST['dev_id'];
}

// Build our Editor instance and process the data coming from _POST
Editor::inst($db, 'application', 'id')
    ->fields(
        Field::inst('application.dev_id'),
        Field::inst('application.id'), 
        Field::inst('application.appnum')
            ->validator('Validate::notEmpty'),
        Field::inst('application.type')
            ->options('application_type','id','type'),
        Field::inst('application.status')
            ->options('application_status','id','status'), 
        Field::inst('application.description'),
        Field::inst('application.comment_date')
            ->getFormatter('Format::date_sql_to_format', 'm/d/Y')
            ->setFormatter(function ($val, $data, $opts) {
                if ($val !== "") {
                    return strftime('%Y-%m-%d', (strtotime($val)));
                } else {
                    return null;
                }
            })
        ,
        Field::inst('application.comment_cal'),
        Field::inst('application.sufficiency_date'),
        Field::inst('application.comments'),
        Field::inst('application.comment_status'),
        Field::inst('application.plan_comission_date'),
        Field::inst('application.elected_date'),
        Field::inst('application.elected_decision'),
        Field::inst('application_status.status') ,
        Field::inst('application_type.type')


    )

    ->where(function ($q) {
        if (isset($_REQUEST['dev_id']) && $_REQUEST['dev_id']) {
            $q->where('dev_id', $_REQUEST['dev_id'], '=');
        }
    })
   
    ->leftJoin('application_type','application_type.id' , '=', 'application.type')
    ->leftJoin('application_status','application_status.id' , '=', 'application.status')  
    ->debug(true)
    ->process($_POST)
    ->json();

allan

Many thanks.

Could you change Field::inst('application.id') to be:

 Field::inst('application.id')->set(false)

please? (Assuming that you don't want it to writeable by the end user - just auto-generated by the database).

Allan

rwgreenwood

Allan,
That did the trick. I will do that on all auto-generated primary keys.

rwgreenwood

Allan,
I may have responded too quickly. The record is being created without an error, but the table is not refreshing. As you can see in the debug json, it is still not getting the proper parameters in the select statement after the create - the new id is not being valued correctly. Here is the new json:

{"data":[],"debug":[{"query":"INSERT INTO  \"application\"  ( \"dev_id\", \"appnum\", \"type\", \"status\", \"description\", \"comment_date\", \"comments\" ) VALUES (  :dev_id,  :appnum,  :type,  :status,  :description,  :comment_date,  :comments )","bindings":[{"name":":dev_id","value":"1908","type":null},{"name":":appnum","value":"cdsa","type":null},{"name":":type","value":"13","type":null},{"name":":status","value":"2","type":null},{"name":":description","value":"","type":null},{"name":":comment_date","value":null,"type":null},{"name":":comments","value":"","type":null}]},{"query":"SELECT  \"application\".\"id\" as \"application.id\", \"application\".\"dev_id\" as \"application.dev_id\", \"application\".\"appnum\" as \"application.appnum\", \"application\".\"type\" as \"application.type\", \"application\".\"status\" as \"application.status\", \"application\".\"description\" as \"application.description\", \"application\".\"comment_date\" as \"application.comment_date\", \"application\".\"comment_cal\" as \"application.comment_cal\", \"application\".\"sufficiency_date\" as \"application.sufficiency_date\", \"application\".\"comments\" as \"application.comments\", \"application\".\"comment_status\" as \"application.comment_status\", \"application\".\"plan_comission_date\" as \"application.plan_comission_date\", \"application\".\"elected_date\" as \"application.elected_date\", \"application\".\"elected_decision\" as \"application.elected_decision\", \"application_status\".\"status\" as \"application_status.status\", \"application_type\".\"type\" as \"application_type.type\" FROM  \"application\" LEFT JOIN \"application_type\" ON \"application_type\".\"id\" = \"application\".\"type\"  LEFT JOIN \"application_status\" ON \"application_status\".\"id\" = \"application\".\"status\" WHERE \"dev_id\" = :where_0 AND  \"application\".\"id\" = :where_1 ","bindings":[{"name":":where_0","value":"1908","type":null},{"name":":where_1","value":"1908","type":null}]}]}

The where_1 should be the newly created id.

rwgreenwood

Nevermind on the last question, it was an issue on one table related to the definition of the primary key in the database.