+- UserSpice (https://userspice.com/forums)
+-- Forum: Support Center (https://userspice.com/forums/forumdisplay.php?fid=23)
+--- Forum: UserSpice 4.3 and Below (https://userspice.com/forums/forumdisplay.php?fid=26)
+--- Thread: transaction with DB Class (/showthread.php?tid=639)
transaction with DB Class - marceloatmartins - 07-12-2017
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
transaction with DB Class - mudmin - 07-12-2017
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.
transaction with DB Class - marceloatmartins - 07-13-2017
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.
transaction with DB Class - firestorm - 07-13-2017
@marceloatmartins
is this what your referring to?
https://dev.mysql.com/doc/refman/5.7/en/commit.html
transaction with DB Class - mudmin - 07-13-2017
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.
transaction with DB Class - marceloatmartins - 07-14-2017
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
transaction with DB Class - marceloatmartins - 07-14-2017
Oops,
the $db->query(“COMMIT”); , will be in Try not in Finally, lol
transaction with DB Class - faguss - 07-14-2017
>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.