Ask a Question

How to run a .sql file querys

douglasrs89
New Contributor

How to run a .sql file querys


Hi, I have a .sql file with many querys. This queries are written without following a standard format. I need a way to run this file without having to modify it. 


 


The only way I could was reading letter by letter and each query rewriting in a row. However this made the process very slow. 


 


What a way to run a file that contains formatting queries independent of them?



Here is my code.








function Auditoria(arquivoDiretorio: string): OleVariant;


  arquivoTxt := aqFile.OpenTextFile(arquivoDiretorio, aqFile.faRead, aqFile.ctANSI);


  quantidadeLinhas := arquivoTxt.LinesCount;


  //HERE I READ THE FILE


  .


  .


  .


 


  arrQuery := CreateVariantArray(0, quantidadeLinhas);


  posicaoArray := 0;


  


  while not arquivoTxt.IsEndOfFile() do


  begin


  //HERE I REWRITE ALL QUERYS OF FILE.


  .


  .


  .


 


  end;


  posicaoArray := posicaoArray - 1;


  VarArrayRedim(arrQuery, posicaoArray);


  //HERE ARE MY NEW QUERYS, EACH IN A LINE


  result := arrQuery;


end;
















function AuditoriaBanco(strBanco: string, arrQuery: OleVariant): boolean;


var 


  strQtdErro, strCodErro, strQuery: string;


  oleQuery, oleConexao: OleVariant;


  intContador, totalLinhas: integer;


  


begin


  result := true;


  oleConexao := FG_ConectaBanco(strBanco);


  totalLinhas := VarArrayHighBound(arrQuery, 1);


  for intContador := 0 to totalLinhas do


  begin


    strQuery := aqConvert.VarToStr(arrQuery[intContador]);


    if strQuery <> '' then


    begin


      try

       //HERE I EXECUTE THE QUERY


        oleQuery := oleConexao.Execute_(strQuery);        


        except


          Log.Warning('Ocorreu o Erro: ' + '"' + exceptionmessage + '"' + ' Ao executar a query: ' + strQuery);


      end;


      if (intContador = totalLinhas) then


      begin


        oleQuery := oleConexao.Execute_(strQuery);


        oleQuery.MoveFirst;


        while not aqConvert.VarToBool(oleQuery.EOF) do


        begin


          strQtdErro := aqConvert.VarToStr(oleQuery.Fields('QTD_ERROS').Value);


          strCodErro := aqConvert.VarToStr(oleQuery.Fields('CODIGO').Value);


          if  strQtdErro > 0 then


       begin


         Log.Warning('A auditoria ' + strCodErro + ' retornou ' + strQtdErro + ' erro(s).'); 


            result := false;


       end;


          oleQuery.MoveNext;


        end;      


      end;


    end;


  end;


  oleConexao.close;


end;




3 REPLIES 3
AlexKaras
Community Hero

Hi Douglas,



If I understood your question right...

It looks like you are using ADO to excute every single query. If this is correct, then indeed, ADO cannot execute queries in a batch and your current approach is the only possible one.

However, every database has a command-line utility designed to execure SQL statements in a batch (for example, in SQL server it is called sqlexec). So, you may call the relevant utility from your test code, provide it with the file name and analyze the returned exit code and console output when it completes the work.

See http://support.smartbear.com/viewarticle/8963/ for the sample of how to wait for the command-line utility to complete and get its output.
Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
douglasrs89
New Contributor

Using this form you instructed I could not return errors like "table does not exist" displayed on the console. And at the end of the run I need to extract a result.

AlexKaras
Community Hero

Hi Douglas,



I believe that errors are posted using the stdErr and you will be able to get them if you modify the ReadAllFromAny() routine, so that it returns the content of both stdOut and stdErr streams.
Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
cancel
Showing results for 
Search instead for 
Did you mean: