Cannot capture sql error

Asked

Viewed 138 times

-1

try
       DModuleGrid.ZQuery1.Close;
       DModuleGrid.ZQuery1.SQL.Clear;
       DModuleGrid.ZQuery1.SQL.Add('SELECT * FROM tdcupant');
       DModuleGrid.ZQuery1.SQL.Add('WHERE numcupom = :co2 AND ccf = :cc3 AND dtcompra = :dtc4 AND impcaixa = :ip5');
       DModuleGrid.ZQuery1.ParamByName('co2').AsString := copy(lTemp,53,6);
       DModuleGrid.ZQuery1.ParamByName('cc3').AsString := copy(lTemp,47,6);
       DModuleGrid.ZQuery1.ParamByName('ip5').AsString := copy(lTemp,4,20);
       DModuleGrid.ZQuery1.ParamByName('dtc4').AsDate := StrToDate(dtcompratxt);
       DModuleGrid.ZQuery1.Open;
     except
       on e: Exception do
       begin
       ercp := e.Message;
       StatusBar1.Panels[0].Text := 'Erro ao encontrar registros! ' + ercp;
     end;

I believe it is right. But it keeps indicating the error of the own sql. Well, the mistake is:

inserir a descrição da imagem aqui

To be precise, it only needs to capture an exception in case none of the records I compared in the parameters are found in the database.

mysql delphi
  • 1

    Which error? tested the direct query in the bank?

  • @Yes, the query presented there, is that you need to have the records registered in the database to match the parameters, however, obviously it is not all file . txt that there are the correct records that is in the database, the error is Sql Sintaxe, which is when you don’t find any records, after you go through the entire database and the txt file.

  • @lost, but that’s exactly what I’m doing, causing error selecting a wrong file, for me to treat it, but it’s not treating.

  • 1

    @Ramonruan Exception when performing the open does not mean that it has not encoded records. A syntax error is when your query is misspelled. Transcribe the error so that it is easier to help. And if you treat on e: Exceptionmeans every kind of exception and therefore will never fall into your else. If what you want is to throw an exception if you don’t find records, that’s another thing

  • @Caputo, follow up on the error image. The Else, it was just a little test, as I am re-learning the language, there is a lot that I missed and lack to learn. haha

  • It will not be the lack of a space between SELECT * FROM tdcupant and WHERE...?

  • @Jorgeb. I think not, because they are in different lines when they are added. But only for testing, I did, but this is how it appears.

  • Experiment for a space before WHERE. It’s usually most of my SQL problems.

  • Before the DModuleGrid.ZQuery1.Open gives a Showmessage(DModuleGrid.ZQuery1.SQL.Text) and say what comes, please.

  • @Filipe.Fonseca SELECT * FROM tdcupant WHERE numcupom = :co2 AND ccf = :cc3 AND dtcompra = :dtc4 AND impcaixa = :ip5, this message, with sql.

  • I was going to post as a comment, but it got big d+, so it was as a response. If it does not work edit later.

  • @Fonseca, I was looking at my code, and I debuggged in another corner, I put a point for every while and if beginning existing in that procedure, and I found a line where, "if I didn’t find any record it would give a value=''..." sql accepts nothing in the field but a 0. I will post the answer right now.

Show 7 more comments

3 answers

1

I don’t know your data access component, but I think it’s worth trying:

Substitute 

       DModuleGrid.ZQuery1.Close;
       DModuleGrid.ZQuery1.SQL.Clear;
       DModuleGrid.ZQuery1.SQL.Add('SELECT * FROM tdcupant');
       DModuleGrid.ZQuery1.SQL.Add('WHERE numcupom = :co2 AND ccf = :cc3 AND dtcompra = :dtc4 AND impcaixa = :ip5');
       DModuleGrid.ZQuery1.ParamByName('co2').AsString := copy(lTemp,53,6);
       DModuleGrid.ZQuery1.ParamByName('cc3').AsString := copy(lTemp,47,6);
       DModuleGrid.ZQuery1.ParamByName('ip5').AsString := copy(lTemp,4,20);
       DModuleGrid.ZQuery1.ParamByName('dtc4').AsDate := StrToDate(dtcompratxt);
       DModuleGrid.ZQuery1.Open;

For: 

       DModuleGrid.ZQuery1.Close;
       DModuleGrid.ZQuery1.SQL.Clear;
       DModuleGrid.ZQuery1.SQL.Text := Format('SELECT * FROM tdcupant WHERE numcupom = %s AND ccf = %s AND dtcompra = %s AND impcaixa = %s', [copy(lTemp,53,6),copy(lTemp,47,6),copy(lTemp,4,20),FormatDateTime('yyyymmdd',StrToDate(dtcompratxt))]);
       DModuleGrid.ZQuery1.Open;

  • I tested your way and it worked too, but it continued the error, so I did what I posted and it worked too. Thanks for the attention.

1

So dude, this error is due to SYNTAX error in the SQL command, debug it and see all the values of the variables you are using to compose the command, so this takes the contents and runs the command directly in the database with the values instead of the variables.

It will be much easier for you to find the error in the command this way.

Also check that parameter "IP5", because I didn’t see it being used anywhere in the command.

I hope it helps.

  • is being used yes, look there, in SELEÇÃO and us PARAMETROS.

0


I debugged the code one more time and found

  DModuleGrid.ZQuery2.Close;
  DModuleGrid.ZQuery2.SQL.Clear;
  DModuleGrid.ZQuery2.SQL.Add('SELECT * FROM tabc460 LIMIT 0, '+valor);
  DModuleGrid.ZQuery2.Open;

That one valor, is a case increment find divergent records. But, think about it, if you do not find any record, will not increment any value right?

Well, it’s there, I thought it was just at that moment there, because I was going through the entire database, and it worked.

if (valor = '') then
  begin
    valor := IntToStr(0);
  end;

I just had this treatment, in case you can’t find it nada, find 0;

  • 1

    Did you solve the problem? I didn’t understand how...

  • @Jorgeb. That code up there is only for SELECTION and COMPARISON, that same code goes into a loop to identify divergences between DB fields and FILE fields. txt, then as I said above, the increment valor, will be used in case of divergences between fields, and this code in this reply corresponds to the SELECTION OF THE LAST DATA ENTERED IN THE BD, which in this case are the divergences.

Browser other questions tagged mysql delphi

You are not signed in. Login or sign up in order to post.