transaction with DB Class

[marceloatmartins]

Hi Everybody,

I need to insert data into two tables, but I have to make sure that both tables are written to the same transaction.

In my case this a form like Master x Detail.

How to use "begin transaction" and "commit" and "rollback" with DB Class ?

Thanks

[mudmin]

Hmm. Not sure what you're talking about, but if you just need to have them share an id or something like that you can

Do your query...

$db->insert('mytable',$fields);
$lastId = $db->lastId();

Then when you insert into the second table, you have your id from the first table in the $lastId variable to use in the second.

[marceloatmartins]

Hi MudMin

Thanks for your tip, but in fact that I need begin transaction make the inserts and after, if all things ok, put a commit at the end. Something into a try .... catch. How to put the database in begin transaction mode and after put in commit mode at the end using DB Class? This is the question.

[firestorm]

@marceloatmartins

is this what your referring to?
https://dev.mysql.com/doc/refman/5.7/en/commit.html

[mudmin]

Hmm. Yeah. I've never done anything like that. You should be able to run pretty much any raw sql query with
$db->query("");

Although you may need to play around with your backticks etc versus things you copy and paste off of the internet for dumping into an actual SQL box.

[marceloatmartins]

Hi Everybody,

Thanks for your suggestions, and I'm trying to see if the code bottom will be work.
I've never use UserSpice before, but I've big transactions (15 tables envolved) here and I've need garantee the data persistence.

I send news as soon.

Bye.

// dispara a validação
$validation = $validate->check ( $_POST, $inputs_for_validate );

if ($validation->passed()){
// database persistence
$successes[] = "Passed.";


try {
$db = DB::getInstance();
$dtHoje=$db->query("SELECT NOW()"); // DATA de HOJE do DB
$db->query("START TRANSACTION");

// table: pac_notifica_paciente_longa_permanencia
// PK: id int(11) AI PK
$fields1 = array(
'id_paciente_fk' =>$leitoInfo->paciente_id,
'id_internacao_fk' =>$leitoInfo->id_internacao,
'id_leito_individual_fk' =>$leitoInfo->id,
'dt_alta_clinica' =>Input::get('dt_alta_clinica'),
'nm_responsavel_alta_clinica' =>Input::get('nm_responsavel_alta_clinica'),
'vl_indice_barthel' =>Input::get('h_vl_idx_barthel'),
'id_unidade_procedencia' =>Input::get('h_cnesUnidade'),
'id_cid_primario' =>Input::get('h_id_cid_primario'),
'id_cid_secundario' =>Input::get('h_id_cid_secundario'),
'fg_familia_localizada' =>Input::get('fg_familia_localizada'),
'tp_moradia' =>Input::get('tp_moradia'),
'fg_apresenta_historico_saude_mental' =>Input::get('historicoSaudeMental'),
'fg_acompanhamento_saude_mental' =>Input::get('acompSaudeMental'),
'id_unidade_acompanhamento_saude_mental' =>Input::get('h_cnes_unidade_acompanhamento_saude_mental'),
'dt_hr_criacao' =>$dtHoje,
'id_usuario_criacao' =>Session::get('user'),
'dt_hr_atualizacao' =>'',
'id_usuario_atualizacao' =>'',
'id_categoria_paciente_longa_permanencia' =>'',
'te_observacao_paciente_longa_permanencia'=>'',
'version' =>'',
);

$db->insert('subhue.pac_notifica_paciente_longa_permanencia',$fields1);
$lastId = $db->lastId(); // FK da Tabela Filha

// table: pac_calculo_indice_barthel_paciente
// PK: id int(11) AI PK
$fields2 = array(
'id_notifica_paciente_longa_permanencia_fk' =>$lastId,
'id_quesito_alimentar' =>Input::get('h_quesito01'),
'id_quesito_banho' =>Input::get('h_quesito01'),
'id_quesito_higiene' =>Input::get('h_quesito01'),
'id_quesito_vestir' =>Input::get('h_quesito01'),
'id_quesito_esfincter' =>Input::get('h_quesito01'),
'id_quesito_urinario' =>Input::get('h_quesito01'),
'id_quesito_banheiro' =>Input::get('h_quesito01'),
'id_quesito_locomocao' =>Input::get('h_quesito01'),
'id_quesito_deambulacao' =>Input::get('h_quesito01'),
'id_quesito_escadas' =>Input::get('h_quesito01'),
'vl_peso_quesito_alimentar'=>'',
'vl_peso_quesito_banho'=>'',
'vl_peso_quesito_higiene'=>'',
'vl_peso_quesito_vestir'=>'',
'vl_peso_quesito_esfincter'=>'',
'vl_peso_quesito_urinario'=>'',
'vl_peso_quesito_banheiro'=>'',
'vl_peso_quesito_locomocao'=>'',
'vl_peso_quesito_deambulacao'=>'',
'vl_peso_quesito_escadas'=>'',
'vl_indice_barthel' =>Input::get('h_vl_idx_barthel'),
'dt_hr_criacao' =>$dtHoje,
'id_usuario_criacao' =>Session::get('user'),
'dt_hr_atualizacao'=>'',
'id_usuario_atualizacao'=>'',
'version'=>'',
);
$db->insert('subhue.pac_calculo_indice_barthel_paciente',$fields2);

} catch(PDOException $e){

$db->query("ROLLBACK");
printf('Não foi possível realizar a operação com a Base de Dados: {3bc1fe685386cc4c3ab89a3f76566d8931e181ad17f08aed9ad73b30bf28114d}s' , $e);
throw new StorageException("Erro..");

} finally {
// echo erro na sql tal
$db->query("COMMIT");
}

} else {
// output errors
$errors[]=$validate->display_errors();
}
} // fim-else

[marceloatmartins]

Oops,

the $db->query(“COMMIT”); , will be in Try not in Finally, lol

[faguss]

>Not sure what you’re talking about,

That's kind of weird because your tables are InnoDB and the point of them is to have transactions.

Anyway, here's an example:

Code:

$commit = false;

Code:

$db->query("START TRANSACTION");

Code:

if ($db->insert("people", ["name"=>"jim"]))

Code:

if ($db->insert("houses", ["personID"=>$db->lastId()]))

Code:

$commit = true;

Code:

$db->query($commit ? "COMMIT" : "ROLLBACK");

Apparently PDO has its own functions for transactions.