SQL - Rollback

[leandrolinauer]

Bom dia a todos.
Estou em andamento com a mudança de DBF para SQL.
Mas já para fazer direitinho, vai aí um pergunta que não localizei resposta no forum.

Gravando os dados.
Tenho 3 tabelas exemplo: PEDIDOS, tenho a tabela PEDIDOS_CABEÇALHO, PEDIDOS_PARCELAS, PEDIDO_ITENS, são três tabelas ou mais ou menos, e tenho que gravar os dados de um pedido gerado venda.
Gravo com insert into nas tres tabelas mas vamos supor que ocorreu um erro quando estava gravando as parcelas, ou seja, tenho gravado o cabeçalho e a parcela 1 e faltou o resto das parcelas e dos itens de pedidos na tabela 3.

Pergunto? como eliminar este erro em SQL?
Estou usando firebird 3.0
Já pesquisei sobre ROLLBACK mas não compreendi para usar neste modelo que necessito.

Grato a todos

[alxsts]

Olá!

Já pesquisei sobre ROLLBACK mas não compreendi para usar neste modelo que necessito

ROLLBACK faz parte de um conceito chamado "Transactions" (transações), que existe nos principais bancos de dados relacionais, se não em todos. Para entender um pouco mais sobre o funcionamento, leia o artigo Transação (banco de dados).

Se você está migrando DBF para banco de dados relacional, provavelmente já criou algumas funções em Harbour para rotinas básicas em SQL, como conversão de tipos de dados do banco para Harbour, executar comandos SQL, etc. No código abaixo, supus que você tenha uma função chamada "ExecSQL" para usar na execução de sentenças SQL. Não conheço Firebird. O exemplo é genérico e você precisará adaptá-lo às tuas necessidades e à sintaxe do Firebird. É apenas um ponto de partida, mostrando inclusive a manipulação de erros pelo Harbour.

Código: Selecionar todos

FUNCTION InserirPedido(...)

   LOCAL lRet := .T., oError

   BEGIN SEQUENCE

      ExecSQL( "BEGIN TRANSACTION;" )  // inicia a transação

      ExecSQL( "INSERT INTO tabela1 ( {lista-de-colunas} VALUES ( {lista-de-valores} ) );" )
      ExecSQL( "INSERT INTO tabela2 ( {lista-de-colunas} VALUES ( {lista-de-valores} ) );" )
      ExecSQL( "INSERT INTO tabela3 ( {lista-de-colunas} VALUES ( {lista-de-valores} ) );" )

      ExecSQL( "COMMIT;" )  // confirma a atualização
   RECOVER USING oError

      Alert( "Não foi possível concluir a inclusão do pedido." + hb_osNewLine +;
             "Erro " + LTrim( Str( oError:genCode ) ) + " / " + oError:description ;
           )
      ExecSQL( "ROLLBACK;" )  // desfaz a atualização
      lRet := .F.

   END SEQUENCE

RETURN lRet

TransactionTransacao

[leandrolinauer]

Boa tarde alxsts.
Sim, realmente já tenho minhas funções adaptadas a partir de exemplos aqui do forum para executar tudo.
O que realmente eu necessitava era um pouco de entendimento a respeito de como gravar dados para não falhar ainda mais quando é em varias tabelas, para não ficar gravado aos pedaços.
Então é realmente o que eu necessitava, saber que o melhor é gravar junto em uma unica transação, e tratar se houver erro de gravação.
Valeu, muito obrigado, vou adaptar para firebird.

[asimoes]

Alexandre

Poderia mostrar o código da função ExecSQL ?

[asimoes]

Com MariaDB banco que eu uso, transação só consigo usando BeginTrans(), CommitTrans() e RollBackTrans() do objecto da conexão

Essa forma só vendo como funciona a função ExecSQL( "BEGIN TRANSACTION;" )

[Fernando queiroz]

Com MariaDB banco que eu uso, transação só consigo usando BeginTrans(), CommitTrans() e RollBackTrans() do objecto da conexão

Essa forma só vendo como funciona a função ExecSQL( "BEGIN TRANSACTION;" )

já que você usa o MARIADB queria uma informação , se você usa ADO ?? ou qual você usa ??

NO caso do MARIADB o COMMIT
A declaração COMMIT encerra uma transação, salvando quaisquer alterações nos dados para que eles se tornem visíveis para transações subsequentes. Além disso, desbloqueia metadados alterados pela transação atual. Se o compromisso automático for definido como 1, um compromisso implícito será realizado após cada declaração. Caso contrário, todas as transações que não terminam com um COMMIT explícito são implicitamente revertidas e as alterações são perdidas. A instrução ROLLBACK pode ser usada para fazer isso explicitamente.

Bom eu não faço o COMMIT e todas as transações são gravadas. ( sera que so funciona automatico no MARIADB ?? )

[asimoes]

Uso ADO, e dessa forma que eu uso no Oracle também

Tenho o método para executar as transações:

Usando ::oConexao do método ConexaoOpenMariaDB

Código: Selecionar todos

METHOD AdoBegin()
LOCAL nCod_ret := 0, oErro

   BEGIN SEQUENCE WITH {| oErro | oErro:cargo := {  oClPF:ListaFuncoes(2, oErro) }, Break( oErro ) }
      ::oConexao:BeginTrans()
      ::__lEndTrans := .F.   
   RECOVER USING oErro
      cErro   := oErro:cargo[1] + Hb_Eol() + Hb_Eol() + ::cMensagemUsuario
      oGuiProc:ScreenErro( "Erro", cErro )
      nCod_ret := 1   
   END

RETURN ( nCod_ret )

METHOD AdoCommit()
LOCAL nCod_ret := 0, oErro

   BEGIN SEQUENCE WITH {| oErro | oErro:cargo := {  oClPF:ListaFuncoes(2, oErro) }, Break( oErro ) }
      ::oConexao:CommitTrans()
      ::__lEndTrans := .T.   // VARIAVEL PRIVATE QUE DEVERÁ SER DECLARADA E INICIALIZADA NO PROGRAMA PRINCIPAL
   RECOVER USING oErro
      cErro   := oErro:cargo[1] + Hb_Eol() + Hb_Eol() + ::cMensagemUsuario
      oGuiProc:ScreenErro( "Erro", cErro )
      nCod_ret := 1
   END

RETURN ( nCod_ret )

METHOD AdoRollBack()
LOCAL nCod_ret := 0, oErro

   BEGIN SEQUENCE WITH {| oErro | oErro:cargo := {  oClPF:ListaFuncoes(2, oErro) }, Break( oErro ) }
      ::oConexao:RollBackTrans()
      ::__lEndTrans := .T.   // VARIAVEL PRIVATE QUE DEVERÁ SER DECLARADA E INICIALIZADA NO PROGRAMA PRINCIPAL
   RECOVER USING oErro
      cErro   := oErro:cargo[1] + Hb_Eol() + Hb_Eol() + ::cMensagemUsuario
      oGuiProc:ScreenErro( "Erro", cErro )
      nCod_ret := 1
   END

RETURN ( nCod_ret )

O método da conexão:

Código: Selecionar todos

METHOD ConexaoOpenMariaDB()
LOCAL oErro 

   BEGIN SEQUENCE WITH {| oErro | oErro:cargo := {  oClPF:ListaFuncoes(2, oErro) }, Break( oErro ) }

      IF ! ::lLocalHost
         IF File( [NETIO\SERVER5\SERVIDORIP.INI] )
            ::cServer := hwg_GetIni( 'CONFIGURACAO', 'Addr', '', [NETIO\SERVER5\SERVIDORIP.INI] )
         ENDIF
      ENDIF
         
      WITH OBJECT ::oConexao := Win_OleCreateObject( "ADODB.Connection" )
         :ConnectionString := iif( win_OsIs10(), "Provider=MSDASQL;", "" )
         :ConnectionString += "Driver={MariaDB ODBC 3.1 Driver};"
         :ConnectionString += ;
                                    "Server=" + ::cServer + ";" + ;
                                    "Port=" + Hb_NtoC( ::nPort ) + ";" + ;
                                    "Stmt=;" + ;
                                    "Database=" + ::cDatabase + ";" + ;
                                    "User=" + ::cUser + ";" + ;
                                    "Password=" + ::cPassword + ";" + ;
                                    "Collation=latin1_swedish_ci;" + ;
                                    "AUTO_RECONNECT=1;" + ;
                                    "COMPRESSED_PROTO=1;" + ;
                                    "PAD_SPACE=1"
          
         :CommandTimeOut    := 600 // seconds
         
         :ConnectionTimeOut := 600 // seconds
         
         :CursorLocation    := adUseClient
         
         :Open()
       
      END
        
   RECOVER USING oErro
      cErro := oErro:cargo[1]
      ::lLocalHost := .T.
      ::cServer    := "LOCALHOST"
      ::ConexaoOpenMariaDB()
   END
      
RETURN Nil

[asimoes]

BeginTrans(), CommitTrans() e RollBackTrans() são métodos do objeto ADODB.Connection

O controle de erro você faz da forma como quiser ou deixa estourar pelo errorsys

[Fernando queiroz]

ABRINDO A CONECCÃO e testando se conseguiu , caso contrario sai do sistema

Código: Selecionar todos

	oServer := ::MySqlConnection( cServer, cUser, cPassword, nPort )
	BEGIN SEQUENCE WITH __BreakBlock()
		oServer:Open()
	ENDSEQUENCE
	IF oServer:State != 1
		hwg_MsgInfo( "FALHA NA CONECÇÃO COM O BANCO DE DADOS", "VERIFIQUE!!!")
		QUIT
	endif

rotina de coneccao

Código: Selecionar todos

METHOD MySqlConnection( cServer, cUser, cPassword, nPort )
LOCAL cnConnection

	cnConnection:= win_OleCreateObject( "ADODB.Connection" )
	cnConnection:ConnectionString := iif( win_OsIs10(), "Provider=MSDASQL;", "" )
	cnConnection:ConnectionString += "Driver={MariaDB ODBC 3.1 Driver};"
	cnConnection:ConnectionString += ;
		"Server=" + cServer + ";" + ;
		"Port=" +  nPort  + ";" + ;
		"Stmt=;" + ;
		"User=" + cUser + ";" + ;
		"Password=" + cPassword + ";" + ;
		"Collation=utf8_general_ci;" + ;
		"AUTO_RECONNECT=1;" + ;
		"COMPRESSED_PROTO=1;" + ;
		"PAD_SPACE=1"
	cnConnection:CursorLocation    := 3
	cnConnection:CommandTimeOut    := 600 // seconds
	cnConnection:ConnectionTimeOut := 600 // seconds

RETURN cnConnection

Código: Selecionar todos

Constante	Valor	Descrição
adStateClosed	0	Indica que o objeto está fechado.
adStateOpen	1	Indica que o objeto está aberto.
adStateConnecting	2	Indica que o objeto está se conectando.
adStateExecuting	4	Indica que o objeto está executando um comando.
adStateFetching	8	Indica que as linhas do objeto estão sendo recuperadas.

[alxsts]

Olá!

Poderia mostrar o código da função ExecSQL ?
Essa forma só vendo como funciona a função ExecSQL( "BEGIN TRANSACTION;" )

O exemplo que postei acima é genérico e ExecSQL foi apenas uma referência a uma função que deve ser escrita pelo programador, de acordo com o método de acesso que ele utiliza (ADO, ODBC, SQLRDD...)

BeginTrans(), CommitTrans() e RollBackTrans() são métodos do objeto ADODB.Connection, CommitTrans() e RollBackTrans() são métodos do objeto ADODB.Connection

Verdade. Mas antes de serem estes métodos do ADO, são comandos SQL, entendidos pelo SGBDR. ADO apenas encapsula isto em seus métodos. Quando solicitado, ADO executa o comando correspondente no SGBDR. Portanto, nada impede que, ao invés de consumir o método BeginTrans(), envie-se o correspondente comando ao SGBDR. No caso do MariaDB, pode ser "BEGIN" ou "START TRANSACTION". "COMMIT" e "ROLLBACK" são iguais aos demais bancos.

Código: Selecionar todos

cnn.Execute "BEGIN",0, adExecuteNoRecords
cnn.Execute "ROLLBACK",0, adExecuteNoRecords
cnn.Execute "COMMIT",0, adExecuteNoRecords

Se o comando não se destinar a retornar resultados (por exemplo, uma consulta SQL UPDATE), o provedor não retornará nada enquanto a opção adExecuteNoRecords for especificada; caso contrário, execute retorna um conjunto de registros fechado. Algumas linguagens de aplicativo permitem ignorar esse valor de retorno se nenhum conjunto de registros for desejado.
Fonte: https://docs.microsoft.com/pt-br/sql/ad ... rver-ver15

A função ExecSQL() deve executar comandos SQL. Os comandos SQL poder ser divididos, a grosso modo, em duas categorias:
os que retornam dados (um record set) e os que não fazem isto. Este fato deve ser considerado na construção de uma função tipo ExecSQL(). Tem programador que cria duas funções, uma para cada situação. Outros escrevem apenas uma, controlando a situação através de parâmetros recebidos. Assim podemos ter: ExecSQLQuery( oConn, cQuery ) ou ExecSQLNonQuery( oConn, cQuery ) ou ExecSQL( oConn, cQuery, lNonQuery ). E tem aqueles que usam ADO e não escrevem uma função - usam o método execute do objeto coneXão ou open do objeto recordset.

No tópico escrevi a função abaixo usando open do objeto recordset:

Código: Selecionar todos

STATIC FUNCTION ExecuteSql( cSql )

   LOCAL oRs As Object
   LOCAL oErr As Object

   Try
      oRs := win_OleCreateObject("ADODB.RecordSet")

      With Object oRs
         :activeConnection := oCn:connectionString

         :cursorLocation := adUseClient
         :cursorType := adOpenDynamic

         :lockType := adLockOptimistic

         //:maxRecords := 100000
         :cacheSize := 100
         :source := cSql

         :open()
      End With

      If ! oRs:eof()
         oRs:moveFirst()
      Endif
      
   Catch oErr
      Throw( oErr )
   Finally
      IF oRs:state() = adStateOpen
         oRs:close()
      ENDIF
   End

   RETURN oRs

Bom eu não faço o COMMIT e todas as transações são gravadas. ( sera que so funciona automatico no MARIADB ?? )

Os SGBDR tem como padrão o autocommit. No MariaDB, para desativar este padrão, pode-se executar o comando:

Código: Selecionar todos

SET AUTOCOMMIT=0;  -- desativa 
SET AUTOCOMMIT=1; -- ativa autocommit

Quando autocommit está ativo, o commit é feito após a execução de cada comando.
Não me lembro onde li isto:
Quando se inicia uma transação (BEGIN ou SET TRANSACTION), internamente o SGBDR emite um SET AUTOCOMMIT=0;, desativando
o commit automático. Se aparecer um comando ROLL BACK ele despreza tudo e volta o estado anterior do auto commit. Se aparecer um
COMMIT, ele grava tudo e volta o estado anterior do auto commit.

Se der tempo, depois posto um exemplo.

[asimoes]

Não consegui funcionar no Maria DB dessa forma, não desfez o update que eu fiz na tabela

cnn.Execute "BEGIN",0, adExecuteNoRecords
cnn.Execute "ROLLBACK",0, adExecuteNoRecords
cnn.Execute "COMMIT",0, adExecuteNoRecords

Meu método para execução:

Código: Selecionar todos

oConexao:ExecTrans( "BEGIN" )

oConexao:ExecTrans( "ROLLBACK" )


METHOD ExecTrans( cTransaction )
LOCAL oErro AS OBJECT, cErro AS STRING 

   BEGIN SEQUENCE WITH {| oErro | oErro:cargo := {  oClPF:ListaFuncoes(2, oErro) }, Break( oErro ) }
      ::oConexao:Execute( cTransaction, 0, adExecuteNoRecords )
   RECOVER USING oErro
        cErro   := oErro:cargo[1]
   END

RETURN Nil