Check if row exist (server-side) when multiple user can edit table

Benoit87

Hello

I am trying to catch an error when multiple users have loaded same table and edit it.

For example :
2 users have loaded the same table, one of them delete a row. Other one edit this deleted row and received this error :

SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'h_ligne' when IDENTITY_INSERT is set to OFF.

How can i check if row exist server-side and catch this error / abort process when this error happen ?

Thanks for your help

allan

You'd need to use validators to confirm that the row is still present and can be acted upon. A validator on the primary key field to check that the row is still there would be the way to do it.

Another option would be to use a socket to tell the second client that the row has been deleted, which is what we do with CloudTables.

Allan

Benoit87

I did that, it seem to works fine, what do you think about it ?

->validator(function ($editor, $action, $data) use ($id_periode,$id_user){

        if ($action !== Editor::ACTION_READ){
                    
                foreach($data['data'] as $pkey_id => $values_id){
                        $id_row_number = $pkey_id;
                }
            
                $id_row_number = ltrim($id_row_number,"row_");

                $requete = "SELECT [id] FROM [***].[dbo].[***] WHERE id = ?;";
                $param = array($id_row_number);
                
                $result = sqlsrv_query($conn,$requete,$param);
                
                if($result === false){
                    die(print_r(sqlsrv_errors(), true));
                }

                while($row = sqlsrv_fetch_array($result)){
                    $row_id_exist = $row['id'];
                }
                
                if(empty($row_id_exist)){
                    return 'Message';
                }

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Looks good to me - nice one, and thanks for posting back.

The only thing I would say is that personally I would use $editor->db() to get the Editor database class (you can then use the reference() method if you didn't want to use our abstraction layer).

The reason I say that is that Editor operates in a transaction by default - so reusing the object will make the query happen inside the transaction. At the validation stage that probably isn't too important, so not a big thing, but worth being aware of.

Allan