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.

rob1strob1st Posts: 84Questions: 22Answers: 0

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    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']);
    ?>
    
  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0
    edited 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.

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    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": [
            
          ]
        }
      ]
    }
    
  • rob1strob1st Posts: 84Questions: 22Answers: 0

    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
            }
          ]
        }
      ]
    }
    
  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0
    edited June 2021

    All three SQL statements give their respective ID's.

  • rob1strob1st Posts: 84Questions: 22Answers: 0

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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

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

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    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.

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    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();
    ?>
    
  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

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

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin
    Answer ✓

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    Thanks for the help Allan, all working now.

Sign In or Register to comment.