[Firebird-net-provider] [FB-Tracker] Created: (DNET-972) AppendSQLStatements - drop/create index exception in use

[Marcos R. W. (JIRA) <tr...@fi...>]

AppendSQLStatements - drop/create index exception in use
--------------------------------------------------------

                 Key: DNET-972
                 URL: http://tracker.firebirdsql.org/browse/DNET-972
             Project: .NET Data provider
          Issue Type: Bug
          Components: ADO.NET Provider
         Environment: - Visual Studio 2019 Community,
- Net FrameWork 4.5.2
- FirebirdSql.Data.FirebirdClient 6.4.0 / 7.5.0
            Reporter: Marcos R. Weimer
            Assignee: Jiri Cincura


Delete a second index (unique desc) in the PK and create it again (desc). It is not the PK index, it is a secondary index in field.
Execute at IBExpert works perfectly.

Code:

ASQL = " SET TERM ^ ;
EXECUTE BLOCK AS
BEGIN
    IF(EXISTS(SELECT 1
              FROM RDB$INDICES
              WHERE UPPER(RDB$INDEX_NAME) = 'PK_PARCEIROS_DESC')) THEN
    BEGIN
      EXECUTE STATEMENT 'DROP INDEX PK_PARCEIROS_DESC;';
      EXECUTE STATEMENT 'CREATE DESCENDING INDEX PK_PARCEIROS_DESC ON PARCEIROS (PARCEIRO);';	  
    END
END^
 SET TERM ; ^"

 FbScript sc = new FbScript(ASql);
 sc.Parse();

  FbBatchExecution fbe = new FbBatchExecution(FConexao); 
  fbe.AppendSqlStatements(sc);
   fbe.Execute();

Exeption:

"An exception was thrown when executing command:  -- Author: Thiago Cousseau\r\n-- 2589\r\n-- 24/07/2020\r\n-- Description: Já existe o indice da PK\r\nEXECUTE BLOCK AS\r\nBEGIN\r\n    IF(EXISTS(SELECT 1\r\n              FROM RDB$INDICES\r\n              WHERE UPPER(RDB$INDEX_NAME) = 'PK_PARCEIROS_DESC')) THEN\r\n    BEGIN\r\n      EXECUTE STATEMENT ('DROP INDEX PK_PARCEIROS_DESC;') WITH AUTONOMOUS TRANSACTION;\r\n      EXECUTE STATEMENT ('CREATE DESCENDING INDEX PK_PARCEIROS_DESC ON PARCEIROS (PARCEIRO);') WITH AUTONOMOUS TRANSACTION;\t  \r\n    END\r\nEND.\r\nBatch execution aborted.\r\nThe returned message was: unsuccessful metadata update\r\nobject INDEX \"PK_PARCEIROS_DESC\" is in use\r\nAt block line: 11, col: 7."


StackTrace:

   em FirebirdSql.Data.Isql.FbBatchExecution.Execute(Boolean autoCommit)
   em UltraBanco.Banco.ExecuteScript(String ASql) em C:\Ultra\CSharp\UltraBanco\UltraBanco.cs:linha 576
   em UltraAtualizador.Funcoes.AtualizarSistema(DataRow LinhaAtual, Int32 UltimaVersaoBanco, List`1 executaveisBaixar, DataTable excecoes, String nomeArquivoZip, List`1 EMailEquipe, Boolean VerificarConexoesAtivas, Boolean& aAtualizarFirebird) em C:\Ultra\CSharp\UltraAtualizador\Funcoes.cs:linha 586

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira