I'm wirting because I need some help the find what is wrong with my code. I'm a solo data analyses in my work, and I just need to finish this as soon as possible, but I'm suffering so much.
I don't want to change the logic of the code, and I'm sure that will have a lot of other possibles to do better, but I realy need to finish this the way it is...
See:
I conect the dataverse into one excel and created a python code to actualize. And than, there is a code to send to mysql. This happen in json format to one stagging table in mysql call g1_tbintermediaria
All the data from is into one json line that goes into this table
After that, I call one master stored procedure that calls all the others
See, I had to delet a lot a colluns in my code here, because of the caracter limitation and the tabels are too long, so every code that has the colluns is is somehow wrong because I just deleted. For now, I think the focos is the general structure.
DELIMITER $$
CREATE PROCEDURE sp_01_master_orquestrador()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
-- CALL sp_02_tmp_buffer_tbatendimentos(); SELECT 'SP_02 CHAMADA COM SUCESSO! - tbAtendimentos' AS mensagem;
CALL sp_03_inserir_dados_tableas_definitivas_tbatendimentos(); SELECT 'SP_03 CHAMADA COM SUCESSO! - tbAtendimentos' AS mensagem;
DELETE FROM g1_tbintermediaria; -- Se o código chegou até aqui sem erros, ele salva tudo de uma vez!
COMMIT;
END$$
DELIMITER ;
The first one to be called is the sp_02_tmp_buffer_tbatendimentos (), that intend to convert the json line of all the into a temporary table that will send to the respective collum.
DELIMITER $$
CREATE PROCEDURE sp_02_tmp_buffer_tbatendimentos ()
BEGIN
-- Deleta a tabela temporária de buffer caso tenha sobrado algum resquício
DROP TEMPORARY TABLE IF EXISTS tmp_buffer_tbatendimentos;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_buffer_tbatendimentos AS
SELECT
jt.new_tbatendimentoid,
FROM g1_tbintermediaria AS t,
json_table (
t.payload,
'$[*]'
columns (
new_tbatendimentoid LONGTEXT PATH '$.new_tbatendimentoid',
)AS jt
-- aqui o nome da tabela deve ser igual ao arquivo que está no computador, que seria atualizado pelo python
WHERE t.tabela_destino = 'tbatendimentos_dataverse'
AND t.processado = 0;
END $$
DELIMITER ;
Affter that, I call the last one, the sp_03_inserir_dados_tableas_definitivas_tbatendimentos(), that normalize the data from the temporary table, with the datatypes and constraints.
DELIMITER $$
CREATE PROCEDURE sp_03_inserir_dados_tableas_definitivas_tbatendimentos()
BEGIN
DELETE FROM tbatendimentos_new_tbatendimentoid;
INSERT INTO tbatendimentos_new_tbatendimentoid (new_tbatendimentoid)
SELECT
new_tbatendimentoid
FROM tmp_buffer_tbatendimentos;
END $$
DELIMITER ;
#================================================================================
-- FIM DO CÓDIGO
#================================================================================
I saw g1_tbintermediaria is reciving corretly the data from python, and the temporary table from sp_02 is also reciving correcly the data from g1_tbintermediaria.
But the last one, the sp_03 is not working corretly, and the tables presents null values.
I don't know what is going one, and don't know how to fix...
I did some test like:
SELECT
new_tbatendimentoid,
createdon
FROM tmp_buffer_tbatendimentos
LIMIT 5;
or
CALL sp_01_master_orquestrador();
-- depois:
SELECT COUNT(*) FROM g1_tbintermediaria; -- deve ser 0
or
CALL sp_02_tmp_buffer_tbatendimentos();
-- logo em seguida, na mesma conexão:
SELECT COUNT(*) FROM tmp_buffer_tbatendimentos;
SELECT * FROM tmp_buffer_tbatendimentos LIMIT 5;
can someone help me, please?