Cannot set the value for an SQL function field. These fields are read only.

rob1st · June 2021

Getting this message on one of my forms. It was working, but looking at the JSON with debug true I can't see the SQL that the editor is sending to the database to see what or where the issue is.

How to I debug this?

allan · June 2021

The error message is correct - Editor's server-side libraries (assuming that is what you are using here) do not support the ability to write a value to an SQL function.

From the information above, one (or more) of those fields must be generated from an SQL function? Can you show me your server-side code?

Thanks,
Allan

rob1st · June 2021

Hi Allan,

Thanks, here is the server side:

```
<?php
//SESSION START
if(!isset($_SESSION)) {
session_start();
if(isset($_SESSION['userID'])) {
$userID = $_SESSION['userID'];
} else {
$userID = null;
}
}

$del = $_SESSION['del'];

include("../lib/DataTables.php");

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'assettype T', 'T.assetTypeID' )
->field(
Field::inst( 'T.assetType' )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Must have a name' )
) ),
Field::inst( 'T.Nomenclature' )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'What are Tags of this equipment labelled as?' )
) ),
Field::inst( 'T.subsystem' )
->options( Options::inst()
->table('system')
->value('systemID')
->label('systemName')
->where( function ($q) {
$q->where( 'systemID', 0, '!=' );
} )
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Which system is the asset part of?' )
) ),
Field::inst( 'S.systemName' ),
Field::inst( 'T.spec' ),
Field::inst( 'T.PICO' )
->options( Options::inst()
->table('cxprocedure')
->value('procedureID')
->label(array('procedureTag','procedureName'))
->Render( function ( $row ) {
return $row['procedureTag'].' - '.$row['procedureName'];
} )
->where( function ($q) {
$q->where( 'procedureType', 1, '=' );
$q->or_where( 'procedureType', 0, '=' );
} )
->order('procedureType, procedureTag')
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Which PICO will verify the equipment is installed correctly? Choose "NO TEST" if there is no PICO' )
) ),
Field::inst( 'C1.procedureTag' ),
Field::inst( 'T.SAT' )
->options( Options::inst()
->table('cxprocedure')
->value('procedureID')
->label(array('procedureTag','procedureName'))
->Render( function ( $row ) {
return $row['procedureTag'].' - '.$row['procedureName'];
} )
->where( function ($q) {
$q->where( 'procedureType', 2, '=' );
$q->or_where( 'procedureType', 0, '=' );
} )
->order('procedureType, procedureTag')
//->order('procedureTag')
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Which SAT will verify the equipment in installed correctly? Choose "NO TEST" if there is no SAT' )
) ),
Field::inst( 'C2.procedureTag' ),
Field::inst( 'T.entity' )
->options( Options::inst()
->table('entity')
->value('entityID')
->label('entityTLA')
->where( function ($q) {
$q->where( 'entityID', 0, '!=' );
} )
->order('entityID')
)
->validator( Validate::dbValues() )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Which entity will own the asset when in operation?' )
) ),
Field::inst( 'E.entityTLA' ),
Field::inst( 'A.active' ),
Field::inst( 'D.discipline' ),
Field::inst( 'G.aGroup' ),
Field::inst( 'T.status' )
->options( Options::inst()
->table('active')
->value('activeID')
->label('active')
)
->validator( Validate::dbValues() )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Is this asset type active now?' )
) ),
Field::inst( 'T.count' )
->options( Options::inst()
->table('acount')
->value('countID')
->label('count')
)
->validator( Validate::dbValues() )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'How should these assets be counted?' )
) ),
Field::inst( 'T.zCount' ),
Field::inst( 'C.count' ),
Field::inst( 'T.updatedBy' )
->set('true')
->setValue( $userID ),
/* Field::inst( 'T.lastUpdated' )
->set(true)
->setValue( date("Y-m-d H:i:s") ), /
Field::inst( 'assetCount' )
->dbField('(SELECT DISTINCT COUNT() FROM asset A1 WHERE A1.assetType = T.assetTypeID)')
->set(false),

)
->leftJoin( 'entity E', 'E.entityID', '=', 'T.entity' )
->leftJoin( 'system S', 'S.systemID', '=', 'T.subsystem' )
->leftJoin( 'cxprocedure C1', 'C1.procedureID', '=', 'T.PICO' )
->leftJoin( 'cxprocedure C2', 'C2.procedureID', '=', 'T.SAT' )
->leftJoin( 'active A', 'A.activeID', '=', 'T.status' )
->leftJoin( 'acount C', 'C.countID', '=', 'T.count' )
->leftJoin( 'discipline D', 'D.disciplineID', '=', 'S.discipline' )
->leftJoin( 'agroup G', 'G.aGroupID', '=', 'D.FS' )
//->leftJoin( 'asset A1', 'A1.assetType', '=', 'T.assetTypeID' )
->where('T.status', $del, '=')
->debug(true)
->process( $_POST )
->json();

unset($_SESSION['del']);

<?php > ``` ?>

allan · June 2021

Field::inst( 'assetCount' )

Is it this one which is causing the problem? Do you have an assetCount field in your client-side Javascript for Editor?

Allan

rob1st · June 2021

No that was the field that I am using to run the query for the table, there is no input to the database for that. It was added based on your response to this thread.

Is there a way to tell the editor that there is no database entry for that field?

I thought that was what the ->set(false) was doing, but I gather not.

allan · June 2021

Is there a way to tell the editor that there is no database entry for that field?
I thought that was what the ->set(false) was doing, but I gather not.

Yes it is. But it is the only field in the above code that appears to be using an SQL function? Or am I just missing it? Sorry if so!

Perhaps you can show me the JSON returned from the server when you submit an edit please? Since you have debug enabled, that should let me see the SQL being generated.

Allan

rob1st · June 2021

Thanks for the response Allan, JSON below: (I removed the data part to stop it being very long).

{
  "debug": [
    {
      "query": "SELECT  `T`.`assetTypeID` as 'T.assetTypeID', `T`.`assetType` as 'T.assetType', `T`.`Nomenclature` as 'T.Nomenclature', `T`.`subsystem` as 'T.subsystem', `S`.`systemName` as 'S.systemName', `T`.`spec` as 'T.spec', `T`.`PICO` as 'T.PICO', `C1`.`procedureTag` as 'C1.procedureTag', `T`.`SAT` as 'T.SAT', `C2`.`procedureTag` as 'C2.procedureTag', `T`.`entity` as 'T.entity', `E`.`entityTLA` as 'E.entityTLA', `A`.`active` as 'A.active', `D`.`discipline` as 'D.discipline', `G`.`aGroup` as 'G.aGroup', `T`.`status` as 'T.status', `T`.`count` as 'T.count', `T`.`zCount` as 'T.zCount', `C`.`count` as 'C.count', `T`.`updatedBy` as 'T.updatedBy', (SELECT DISTINCT COUNT(*) FROM asset A1 WHERE A1.assetType = T.assetTypeID) as '(SELECT DISTINCT COUNT(*) FROM asset A1 WHERE A1.assetType = T.assetTypeID)' FROM  assettype T LEFT JOIN entity E ON `E`.`entityID` = `T`.`entity`  LEFT JOIN system S ON `S`.`systemID` = `T`.`subsystem`  LEFT JOIN cxprocedure C1 ON `C1`.`procedureID` = `T`.`PICO`  LEFT JOIN cxprocedure C2 ON `C2`.`procedureID` = `T`.`SAT`  LEFT JOIN active A ON `A`.`activeID` = `T`.`status`  LEFT JOIN acount C ON `C`.`countID` = `T`.`count`  LEFT JOIN discipline D ON `D`.`disciplineID` = `S`.`discipline`  LEFT JOIN agroup G ON `G`.`aGroupID` = `D`.`FS` WHERE `T`.`status` IS NULL ",
      "bindings": [
        
      ]
    },
    {
      "query": "SELECT DISTINCT  `systemID` as 'systemID', `systemName` as 'systemName' FROM  `system` WHERE (`systemID` != :where_1 )",
      "bindings": [
        {
          "name": ":where_1",
          "value": 0,
          "type": null
        }
      ]
    },
    {
      "query": "SELECT DISTINCT  `procedureID` as 'procedureID', `procedureTag` as 'procedureTag', `procedureName` as 'procedureName', `proceduretype` as 'proceduretype', `proceduretag` as 'proceduretag' FROM  `cxprocedure` WHERE (`procedureType` = :where_1 OR  `procedureType` = :where_2 ) ORDER BY `procedureType` , `procedureTag`  ",
      "bindings": [
        {
          "name": ":where_1",
          "value": 1,
          "type": null
        },
        {
          "name": ":where_2",
          "value": 0,
          "type": null
        }
      ]
    },
    {
      "query": "SELECT DISTINCT  `procedureID` as 'procedureID', `procedureTag` as 'procedureTag', `procedureName` as 'procedureName', `proceduretype` as 'proceduretype', `proceduretag` as 'proceduretag' FROM  `cxprocedure` WHERE (`procedureType` = :where_1 OR  `procedureType` = :where_2 ) ORDER BY `procedureType` , `procedureTag`  ",
      "bindings": [
        {
          "name": ":where_1",
          "value": 2,
          "type": null
        },
        {
          "name": ":where_2",
          "value": 0,
          "type": null
        }
      ]
    },
    {
      "query": "SELECT DISTINCT  `entityID` as 'entityID', `entityTLA` as 'entityTLA', `entityid` as 'entityid' FROM  `entity` WHERE (`entityID` != :where_1 ) ORDER BY `entityID`  ",
      "bindings": [
        {
          "name": ":where_1",
          "value": 0,
          "type": null
        }
      ]
    },
    {
      "query": "SELECT DISTINCT  `activeID` as 'activeID', `active` as 'active' FROM  `active` ",
      "bindings": [
        
      ]
    },
    {
      "query": "SELECT DISTINCT  `countID` as 'countID', `count` as 'count' FROM  `acount` ",
      "bindings": [
        
      ]
    }
  ]
}

rob1st · June 2021

Just found this goes through the response side:

{
  "fieldErrors": [],
  "error": "Cannot set the value for an SQL function field. These fields are read only.",
  "data": [],
  "ipOpts": [],
  "cancelled": [],
  "debug": [
    {
      "query": "SELECT  `entityID` as 'entityID' FROM  `entity` WHERE `entityID` = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "1",
          "type": null
        }
      ]
    },
    {
      "query": "SELECT  `activeID` as 'activeID' FROM  `active` WHERE `activeID` = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "1",
          "type": null
        }
      ]
    },
    {
      "query": "SELECT  `countID` as 'countID' FROM  `acount` WHERE `countID` = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "1",
          "type": null
        }
      ]
    }
  ]
}

allan · June 2021

I've got to confess, I honestly don't know what is causing this here. Those queries are all simple SELECTs and don't appear to be using functions at all, never mind trying to set the value of one.

If you run those queries directly against the database (mySqlAdmin or whatever), what does it give?

Allan

rob1st · June 2021

All three SQL statements give their respective ID's.

rob1st · June 2021

It's definitely that statement Allan, when I comment it and it's corresponding data tag, the form works.

rob1st · June 2021

and another point, this only seems to affect create, edit works fine.

allan · June 2021

What's odd is that the debug trace SQL statements are only SELECTs not INSERTs (or even UPDATEs).

Are you able to give me a link to the page showing the issue? Between that and the code you've provided above I might be able to figure out what is going wrong here!

Regards,
Allan

rob1st · July 2021

Yes, I'll create a public page with the SQL enabled (I've disabled it on the useable site for functionality) and post.

rob1st · July 2021

Hi @allan,

You can find a version that you can play with here:

https://assettrack.cx/submissions/submissions1.php

All on a dummy table, so play away as much as you like. Appreciate your help as always.

rob1st · July 2021

The server side for his page is below and this page has the same SQL statement as the other which is set to false. (line 89).

```
<?php
//SESSION START
if(!isset($_SESSION)) {
session_start();
if(isset($_SESSION['userID'])) {
$userID = $_SESSION['userID'];
} else {
$userID = null;
}
}

include("../lib/DataTables.php");

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'submissions1 S', 'S.submissionID' )
->field(
Field::inst( 'S.proTag' )
->options( Options::inst()
->table('cxprocedure')
->value('id')
->label(array('procedureTag','procedureName'))
->Render( function ( $row ) {
return $row['procedureTag'].' - '.$row['procedureName'];
} )
->where( function ($q) {
$q->where( 'docStatus', 3, '!=' );
} )
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Which procedure was submitted?' )
) ),
Field::inst( 'C.procedureTag' ),
Field::inst( 'C.procedureName' ),
Field::inst( 'S.PDMCNo' )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'What was the PDMC number?' )
) ),
Field::inst( 'S.rev' )
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Which revision?' )
) ),
Field::inst( 'U1.username' ),
Field::inst( 'S.TxNo' ),
Field::inst( 'S.dateSubmitted' ),
Field::inst( 'S.updated' ),
Field::inst( 'U2.username' ),
Field::inst( 'S.submittedTo' )
->options( Options::inst()
->table('entity')
->value('entityID')
->label('entityTLA')
->where( function ($q) {
$q->where( 'entityID', 0, '!=' );
} )
->order('entityID')
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'Which Entity is receiving the submission?' )
) ),
Field::inst( 'E.entityTLA' ),
Field::inst( 'S.crr' ),
Field::inst( 'S.receivedStatus' )
->options( Options::inst()
->table('docstatus')
->value('docStatusID')
->label('docStatus')
->where( function ($q) {
$q->where( 'docStatusID', 4, '>=' );
$q->and_where( 'docStatusID', 8, '<' );
$q->or_where( 'docStatusID', 11, '=' );
} )
->order('docOrder')
)
->setFormatter( Format::ifEmpty( null )),
Field::inst( 'D.docStatus' ),
Field::inst( 'S.receivedBack' )
->setFormatter( Format::ifEmpty( null )),
Field::inst( 'daysReview' )
->dbField('IF(S.receivedStatus = 11, "-", IF(S.receivedBack IS NULL, DATEDIFF(NOW(),S.dateSubmitted), DATEDIFF(S.receivedBack,S.dateSubmitted)))')
->set(false),

)
->leftJoin( 'entity E', 'E.entityID', '=', 'S.submittedTo' )
->leftJoin( 'cxprocedure C', 'C.id', '=', 'S.proTag' )
->leftJoin( 'users_enc U1', 'U1.userID', '=', 'S.submittedBy' )
->leftJoin( 'users_enc U2', 'U2.userID', '=', 'S.updatedBy' )
->leftJoin( 'docstatus D', 'D.docStatusID', '=', 'S.receivedStatus' )
->debug(true)
->process( $_POST )
->json();

<?php > ``` ?>

allan · July 2021

Thank you! Although I fear I'm still not seeing what is causing the error.

In Field.php can you find this line:

throw new \Exception('Cannot set the value for an SQL function field. These fields are read only.');

And replace it with:

throw new \Exception('Cannot set the value for an SQL function field. These fields are read only: ' . $this->name(). ' === '. $this->dbField());

Then let me know and that might hopefully help me track it down.

Regards,
Allan

rob1st · July 2021

Done, the error now is this:

Cannot set the value for an SQL function field. These fields are read only:
daysReview === IF(S.receivedStatus = 11, "-", IF(S.receivedBack IS NULL, DATEDIFF(NOW(),S.dateSubmitted), DATEDIFF(S.receivedBack,S.dateSubmitted)))

allan · July 2021

Thank you! I believe I know what the issue is and it is something I introduced fairly recently unfortunately.

In your local copy of Editor.php find:

        // Get values to generate the id, including from setValue, not just the
        // submitted values
        $all = array();
        foreach ($this->_fields as $field) {
            $this->_writeProp($all, $field->name(), $field->val( 'set', $values ));
        }

and replace it with:

        // Get values to generate the id, including from setValue, not just the
        // submitted values
        $all = array();
        foreach ($this->_fields as $field) {
            if ($field->apply('set', $values)) {
                $this->_writeProp($all, $field->name(), $field->val( 'set', $values ));
            }
        }

That should resolve the error.

The fix has been committed here.

Allan

rob1st · July 2021

Thanks for the help Allan, all working now.

Cannot set the value for an SQL function field. These fields are read only.

Cannot set the value for an SQL function field. These fields are read only.

Answers

Howdy, Stranger!

Categories

DataTables

Cannot set the value for an SQL function field. These fields are read only.

Cannot set the value for an SQL function field. These fields are read only.

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables