[zeos8] Wrong statement does noy triiger an exception when prepare, Why?

[hamacker]

Hi ALL,
I´am using Zeos8 from git repo with Lazarus 3.
Since starting using zeos8, I dont know why, but when I prepare my querys, if statement is wrong (sintax, field, whatever) never trigger an exception, prepare always is fine, exception will appear only when execute it.
Is there any option to turn on exceptions on prepare method?
Sample:

Code: Select all

q1:=TZQuery.Create(nil);
q1.Connection:=AConn;
q1.ParamCheck:=true;  
(...)
q1.SQL.Clear;
q1.SQL.add('SELECT ');
q1.SQL.add('  ret_nf_chave,');
q1.SQL.add('  ret_item,');
q1.SQL.add('  this_field_not_exists ');   // make an exception
q1.SQL.add('FROM SET_NF_RECEB_INICIO (');
q1.SQL.add('  :p_nf_chave,');
q1.SQL.add('  :p_nf_item,');
q1.SQL.add('  :p_avaliacao_embalagem,');
q1.SQL.add('  :p_observacao,');
q1.SQL.add('  :p_evento_tipo,');
q1.SQL.add('  :p_compras_pedido_req,');
q1.SQL.add('  :p_almox_controle_req,');
q1.SQL.add('  :p_recto_por,');
q1.SQL.add('  :p_recto_dt');
q1.SQL.add('  );');
try
  if not q1.Prepared then
    q1.Prepare;  //prepare without any error
  {$IFOPT D+}
    q1.SQL.SaveToFile('c:\temp\SET_NF_RECEB_INICIO.sql');
  {$ENDIF}
except
on e:exception do Result:=e.message;
end;  

[aehimself]

I think preparing a statement is mostly an internal thing. What it does is it basically creates the actual SQL query which will be executed and created the internal statement object. This can be done without talking to the RDBMS system.
As far as I know there is no client-side parsing done in Zeos, exceptions about basically almost everything are always returned by the RDBMS system you are connected to.

I said this without checking any protocol implementation so I might be wrong but I feel like 95% confident saying this.

Based on the theory stated above I'd say no, you have no option to turn exceptions on during the prepare stage.

[hamacker]

Prepare queries need to submit sql code to a server previously and returning true of false from server to proceed.
Some times, we do manually:

Code: Select all

if not query1.prepared then query1.prepare;

Because another instante in same workstation or different point of network already prepared.
Since BDE, IBX, IBO, Firedac with Delphi (and now with Lazarus) the expected behavior is popup an exception If query can not be prepared.
No exception is a behavior of sql prepapre using zeos? It´s sad.

[aehimself]

Well, both of us are right.

You are right that there might be more to preparation than just setting up internal properties.
But, this preparation takes place automatically, somewhere else and not at the Query component.
You can verify this; TZQuery.Prepare actually leads you to ZAbstractRODataSet.pas : 5254 (TZAbstractRODataset.Prepare)

Code: Select all

{**
  Prepares the query.
  If this actually does happen at the database connection level depends on the
  specific implementation.
}
procedure TZAbstractRODataset.Prepare;
begin
  Prepared := True;
end;

I did a quick check, at MySQL preparation is called from ZDBCMySQLStatement.pas : 1047 and is mostly setting up parameters; but as the comment says this can differ for each RDBMS.

Edit:
Nvm. Prepared := True calls a setter, not only a variable. ZAbstractRODataSet.pas : 3709 takes care of preparation:

Code: Select all

    Result := TxnCon.PrepareStatementWithParams(SQL, Temp);

Until I debugged in it there was no error check performed which might explain why we only get an exception during execution.

[hamacker]

Good that already exists implamentation, just waitting for code.
I will wait for a good soul, still there, I will handle with carefull prepared queries.

Code: Select all

procedure TZAbstractRODataset.Prepare;
begin
  Prepared := True;
end;

[marsupilami]

Prepare queries need to submit sql code to a server previously and returning true of false from server to proceed.
Some times, we do manually:

Code: Select all

if not query1.prepared then query1.prepare;

Why would you do that? Just remove these lines and do error handling when you open the query.

Because another instante in same workstation or different point of network already prepared.
Since BDE, IBX, IBO, Firedac with Delphi (and now with Lazarus) the expected behavior is popup an exception If query can not be prepared.

At least for Firedac this is not documented. https://docwiki.embarcadero.com/RADStudio/Sydney/en/Preprocessing_Command_Text_(FireDAC) says:

When calling Prepare, the FireDAC command preprocessor transforms the command text into a form understood by the DBMS when ResourceOptions.MacroExpand is True. This means the macros are not visible to the DBMS.

The Params collection property is filled automatically when the ResourceOptions.ParamCreate value is True. When calling Prepare, FireDAC replaces the FireDAC parameter markers with the DBMS native markers if ResourceOptions.ParamExpand is True.

The escape sequences and conditional substitutions are processed when ResourceOptions.EscapeExpand is True.

This doesn't mention anything about preparing a statement in the database. The rest is the same as Zeos does when you call prepare.

Also documentation on TFDQuery doesn't mention preparing a statement in the database or sending SQL to the database.

Good that already exists implamentation, just waitting for code.

There is an implementation and it already calls code. Prepared = True calls SetPrepared which in tun calls InternalPrepare .....

I will wait for a good soul, still there, I will handle with carefull prepared queries.

If it is critical to you, you could always try to implement this yourself. Zeos is Open Source after all. The IZPreparedStatement could be extended to have a Prepare method. Most drivers do have a prepare method in their statements. The drivers that don't have a prepare method could have an empty Prepare method that just does nothing. TZAbstractRoDataset.Prepare most probably could call this Prepare method.

With best regards,

Jan

[hamacker]

firedac can use both parameters and macros, they are very similar. But, macros can be used anywhere, including replacing verbs and keywords. It would be a great idea to have something similar on Zeos.
Parameters on the other hand can only be used in values. FirebirdSQL will compile and cache them for future runs.
I think that Firedac automatically prepares when it sees parameters, so I could even forget them, however, there are times that before submitting a large number of queries within a loop, before the loop we test each one of them using prepare because it can it may be that within the loop some of them are executed and others are not. That's why it's a good practice to use Prepare to test all the queries and if one of them fails, I simply cancel the loop and don't waste time.