Save Multiple Records In Transaction

Hello forum,

I have a code that loads an XLS and will be saving each line, the point is that if during processing a line can not be stored all of the above should not be saved.

I’m doing a FOR within a transaction but does not roolBack




..

$transaction = Yii::app()->db->beginTransaction();

try

{

  for($i=1, $i...    <<-- crosses the XLS line by line

  {

    ..

    if ($modelo1->validate())

      $modelo1->save();

    else

      break;      <<-- ends to avoid going all the XLS

    ..

    if ($model2->validate())

      $modelo2->save();

    else

      break;      <<-- ends to avoid going all the XLS

  }

  $transaction->commit();

  Yii::app()->getUser()->setFlash('success','The excel file was successfully imported.');

  $this->refresh();

}

catch(Exception $e)

{

  $transaction->rollBack();

  Yii::app()->user->setFlash('error', "{$e->getMessage()}");

  $this->refresh();

}

..



$ model1 is another table that is filled with some data from XLS if necessary.

Even when an error occurs the data saved are not rolled.

My tables are InnoDB ($modelo1 y 2).

What is happening?

This is the complete code:




public function actionUpload()

{

  $uploaded = false;

  $dir = Yii::getPathOfAlias('webroot')."/protected/uploads/";

  $model=new Upload;

  

  Yii::import('ext.phpexcelreader.JPhpExcelReader');

  $separator=array('$',',','*');

  

  if(isset($_POST['Upload']))

  {

    $model->attributes=$_POST['Upload'];

    

    $file=CUploadedFile::getInstance($model,'file');

    if($model->validate())

    {

      $uploaded = $file->saveAs($dir.$file->getName());

      $data=new JPhpExcelReader($dir.$file->getName());


      $transaction = Yii::app()->db->beginTransaction();

      try

      {

        // Se recorre de nuevo el xls para ir sacando datos e ir insertando

        for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++)

        {

          $wbs_code = substr($data->sheets[0]['cells'][$i][1], 0, 10);  // ---> columna A

          $wbs = Wbs::model()->find('code=:codigo AND active=1', array('codigo'=>$wbs_code));


          $descrip  = $data->sheets[0]['cells'][$i][2];  // ---> columna B

          $commcode = $data->sheets[0]['cells'][$i][3];  // ---> columna C


          $csi_code = substr($data->sheets[0]['cells'][$i][4], 0, 20);  // ---> columna D


          $cliecode = $data->sheets[0]['cells'][$i][5];  // ---> columna E

          $csidescr = $data->sheets[0]['cells'][$i][6];  // ---> columna F

          $csiunit  = $data->sheets[0]['cells'][$i][7];  // ---> columna G


          // Verifica cada valor de D en csi_codes.csi_code, 

          // si alguno no está en el catálogo se tiene que agregar.

          $exists = CsiCodes::model()->exists('csi_code=:codigo AND active=1', array('codigo'=>$csi_code));

          if (!$exists)

          {

            // Se tiene que agregar al catálogo pero se necesita el cost_groups.id para ponerlo en

            // csi_codes.id_cost_group, se obtiene usando el valor de la col. C

            $csicode = new CsiCodes;


            // Obtiene el ID del Commodity Group que está en la col. C

            $commgr = CostGroups::model()->find('csi_cost_group=:codigo AND active=1', 

                         array('codigo'=>$commcode));


            // Llena el nuevo CSI Codes

            $csicode->id_cost_group = $commgr->id;

            $csicode->csi_code      = $csi_code;

            $csicode->client_code   = $cliecode;

            $csicode->descr         = $csidescr;

            $csicode->unit          = $csiunit;

            if ($csicode->validate())

              $csicode->save();

            else

              break;


          }

          else

            $csicode = CsiCodes::model()->find('csi_code=:codigo AND active=1', 

                           array('codigo'=>$csi_code));


          $estimateQty = $data->sheets[0]['cells'][$i][8];  // ---> columna H

          $jobHours    = $data->sheets[0]['cells'][$i][9];  // ---> columna I

          $labor       = $data->sheets[0]['cells'][$i][10];  // ---> columna J

          $material    = $data->sheets[0]['cells'][$i][11];  // ---> columna K

          $equipment   = $data->sheets[0]['cells'][$i][12];  // ---> columna L

          $subcontract = $data->sheets[0]['cells'][$i][13];  // ---> columna M

          $labor2      = $data->sheets[0]['cells'][$i][14];  // ---> columna N

          $material2   = $data->sheets[0]['cells'][$i][15];  // ---> columna O

          $equipment2  = $data->sheets[0]['cells'][$i][16];  // ---> columna P

          $subcontract2= $data->sheets[0]['cells'][$i][17];  // ---> columna Q

          $start       = substr($data->sheets[0]['cells'][$i][18], 0, 10);  // ---> columna R

          $end         = substr($data->sheets[0]['cells'][$i][19], 0, 10);  // ---> columna S


          // Se inserta a bill_of_qtys

          $billOfQtys = new BillOfQtys();

          $billOfQtys->id_wbs = $wbs->id;

          $billOfQtys->id_csi_code = $csicode->id;

          $billOfQtys->estim_qty = $estimateQty;

          $billOfQtys->tot_jobhrs = $jobHours;

          $billOfQtys->tot_labor = $labor;

          $billOfQtys->tot_material = $material;

          $billOfQtys->tot_equipment = $equipment;

          $billOfQtys->tot_subcontract = $subcontract;

          $billOfQtys->ini_date = $start;

          $billOfQtys->end_date = $end;

          $billOfQtys->estim_labor = $labor2;

          $billOfQtys->estim_material = $material2;

          $billOfQtys->estim_equipment = $equipment2;

          $billOfQtys->estim_subcontract = $subcontract2;

          $billOfQtys->active = 1;

          $billOfQtys->cancel_reason = '';

          $billOfQtys->u_crea = 1;

          if ($billOfQtys->validate())

            $billOfQtys->save();

          else

            break;


        }  // fin FOR recorre de nuevo para ir guardando


        /*

         * Se guarda toda la transacción

        */

        $transaction->commit();


        Yii::app()->getUser()->setFlash('success','The excel file was successfully imported.');

        $this->refresh();

      }  

      catch(Exception $e) // si alguna consulta falla se genera una excepción

      {

        $transaction->rollBack();

        Yii::app()->user->setFlash('error', "{$e->getMessage()}");

        $this->refresh();

      }

    }  

    else

    {

      Yii::app()->getUser()->setFlash('error','Invalid file: type, size or undefined.');

      $this->refresh();

    }

  }

  else

    $this->render('uploadxls',array('model'=>$model,'uploaded' => $uploaded));

}



Thanks.

Read documentatio:




$transaction=$connection->beginTransaction();

try

{

    $connection->createCommand($sql1)->execute();

    $connection->createCommand($sql2)->execute();

    //.... other SQL executions

    $transaction->commit();

}

catch(Exception $e) // an exception is raised if a query fails

{

    $transaction->rollback();

}



write sql command and dont use CActiveRecord

Well, then I’ll change it…

Thanks.