Error with autocommit and parameters

[lenin]

Same code in version 7.0.3-stable does not occur ERROR!

Zeoslib- build 3986- versao test 7.2
postgreSQL 9.2
BERLIN UPDATE 2

---------------------------
Debugger Exception Notification
---------------------------
Project raised exception class EZSQLException with message 'SQL Error: ERROR: current transaction is aborted, commands ignored until end of transaction block'.
---------------------------
Break Continue Help
---------------------------

/// ZDbcPostgreSqlStatement.pas
{**
Executes the SQL query in this <code>PreparedStatement</code> object
and returns the result set generated by the query.

@return a <code>ResultSet</code> object that contains the data produced by the
query; never <code>null</code>
}
function TZPostgreSQLPreparedStatement.ExecuteQueryPrepared: IZResultSet;
begin
Result := nil;
Prepare;
PrepareOpenResultSetForReUse;
if Prepared then
if Findeterminate_datatype then
QueryHandle := ExecuteInternal(PrepareAnsiSQLQuery, eicExecute) ///*** Apparently occurs in this line 295 /* Findeterminate_datatype */
else
begin
BindInParameters;
QueryHandle := ExecuteInternal(ASQL, eicExecPrepStmt);
end
else
QueryHandle := ExecuteInternal(ASQL, eicExecute);
if QueryHandle <> nil then
if Assigned(FOpenResultSet) then
Result := IZResultSet(FOpenResultSet)
else
Result := CreateResultSet(QueryHandle)
else
Result := nil;
inherited ExecuteQueryPrepared;
end;




oQy : TzQuery;
begin
sSQL :=
' select ' + sLineBreak +
' (select t.id_geral from ' + sLineBreak +
' (select TPPA.id_geral ' + sLineBreak +
' from tabela_preco_PRODUTO TPPA ' + sLineBreak +
' WHERE TPPA.cd_tabela_preco = TP.cd_tabela ' + sLineBreak +
' AND tPPA.un_medida = :UN_MEDIDA ' + sLineBreak +
' AND TPPA.cd_prazo_medio_preco = :cd_prazo_medio_preco ' + sLineBreak +
' and tppa.id_item = :id_item ' + sLineBreak +
' and tppa.qt_minima = :QT_MINIMA ' + sLineBreak +
' ) T ' + sLineBreak +
' order by 1 desc limit 1) as ID_GERAL, ' + sLineBreak +
' (select t.id_item from ' + sLineBreak +
' (select TPPA.id_item ' + sLineBreak +
' from tabela_preco_PRODUTO TPPA ' + sLineBreak +
' WHERE TPPA.cd_tabela_preco = TP.cd_tabela ' + sLineBreak +
' AND tPPA.un_medida = :UN_MEDIDA ' + sLineBreak +
' AND TPPA.cd_prazo_medio_preco = :cd_prazo_medio_preco ' + sLineBreak +
' and tppa.id_item = :id_item ' + sLineBreak +
' and tppa.qt_minima = :QT_MINIMA ' + sLineBreak +
' ) T ' + sLineBreak +
' order by 1 desc limit 1)as ID_ITEM, ' + sLineBreak +
' :UN_MEDIDA as un_medida, ' + sLineBreak +
' tp.cd_tabela as cd_tabela_preco, ' + sLineBreak +
' :cd_prazo_medio_preco as cd_prazo_medio_preco, ' + sLineBreak +
' '''' as NM_PRAZO_MEDIO_PRECO, ' + sLineBreak +
' :PRECO_NOVO as preco, ' + sLineBreak +
' ROUND(:PRECO_NOVO * (1+((select TPPA.coef_tab_origem from tabela_preco_PRODUTO TPPA ' + sLineBreak +
' WHERE TPPA.cd_tabela_preco = TP.cd_tabela AND tPPA.un_medida = :UN_MEDIDA ' + sLineBreak +
' AND TPPA.cd_prazo_medio_preco = :cd_prazo_medio_preco AND TPPA.id_item = :id_item ' + sLineBreak +
' UNION ' + sLineBreak +
' select MAX(TPPB.coef_tab_origem) from tabela_preco_PRODUTO TPPB ' + sLineBreak +
' WHERE TPPB.cd_tabela_preco = TP.cd_tabela AND tPPB.un_medida = :UN_MEDIDA ' + sLineBreak +
' AND TPPB.cd_prazo_medio_preco = :cd_prazo_medio_preco ) ' + sLineBreak +
' limit 1) / 100),:DECIMAIS)AS NOVO_PRECO , ' + sLineBreak +
' ((select TPPA.coef_tab_origem from tabela_preco_PRODUTO TPPA ' + sLineBreak +
' WHERE TPPA.cd_tabela_preco = TP.cd_tabela AND tPPA.un_medida = :UN_MEDIDA ' + sLineBreak +
' AND TPPA.cd_prazo_medio_preco = :cd_prazo_medio_preco AND TPPA.id_item = :id_item ' + sLineBreak +
' UNION ' + sLineBreak +
' select MAX(TPPB.coef_tab_origem) from tabela_preco_PRODUTO TPPB ' + sLineBreak +
' WHERE TPPB.cd_tabela_preco = TP.cd_tabela AND tPPB.un_medida = :UN_MEDIDA ' + sLineBreak +
' AND TPPB.cd_prazo_medio_preco = :cd_prazo_medio_preco ) ' + sLineBreak +
' limit 1) as COEF_TAB_ORIGEM, ' + sLineBreak +
' tp.nm_tabela ' + sLineBreak +
' from tabela_preco tp ' + sLineBreak +
' LEFT JOIN (select * ' + sLineBreak +
' from tabela_preco_PRODUTO TPPA ' + sLineBreak +
' WHERE tPPA.un_medida = :UN_MEDIDA ' + sLineBreak +
' AND TPPA.cd_prazo_medio_preco = :cd_prazo_medio_preco ' + sLineBreak +
' and tppa.id_item = :id_item ' + sLineBreak +
' and tppa.qt_minima = :QT_MINIMA ' + sLineBreak +
' ) TPP_A ON TPP_A.cd_tabela_preco = TP.cd_tabela ' + sLineBreak +
' where tp.cd_tabela_origem = :CD_TABELA_ORIGEM ';

{ ///*** If you throw the values straight into SQL the error does NOT occur.
sSQL := StringReplace(sSQL2, ':PRECO_NOVO', FloatToStr(PrecoNovo), [rfReplaceAll, rfIgnoreCase]);
sSQL := StringReplace(sSQL2, ':CD_PRAZO_MEDIO_PRECO', FloatToStr(pnPrazoMedio), [rfReplaceAll, rfIgnoreCase]);
sSQL := StringReplace(sSQL2, ':UN_MEDIDA', QuotedStr(psUnidade), [rfReplaceAll, rfIgnoreCase]);
}

///*** The PROBLEM ONLY occurs with Zconnection is with autocommit = true.
///*** The error occurs when giving a rollback before to restart the transaction
///*** Without the rololback apparently the error does not occur.
Banco.Rollback;

oQy := TZQuery.Create(nil);
oQy.Connection := Banco;
oQy.SQL.Text := sSQL;

try
///*** The PROBLEM ONLY occurs with these 3 parameters.
oQy.ParamByName('PRECO_NOVO').AsFloat := PrecoNovo;
oQy.ParamByName('CD_PRAZO_MEDIO_PRECO').Value := pnPrazoMedio;
oQy.ParamByName('UN_MEDIDA').AsString := psUnidade;
////***

oQy.ParamByName('DECIMAIS').AsInteger := FCasasDecimais;
oQy.ParamByName('CD_TABELA_ORIGEM').AsInteger := pCD_TABELA_PRECO;
oQy.ParamByName('ID_ITEM').AsInteger := objItem.id_item;
oQy.ParamByName('QT_MINIMA').AsFloat := pnQtdMinima;

oQy.Open;

[lenin]

THE ERROR HAPPENS ONLY IN AUTOCOMMIT MODE AND WITH SOME PARAMETERS, WHEN A TRANSACTION IS RE-START.
IT HAPPENS WHEN IT HAS NULL PARAMETERS.
IN OTHER CASES IT HAPPENS WITH ALL PARAMETERS POPULATED ALSO AS IN THE CASE ABOVE.

[lenin]

Just putting a cast in the parameters in SQL to force the type it already works.
Without the cast the error remains when opening the query

ex:
NOT Error
cast (:UN_MEDIDA as varchar)
cast (:id_item as integer)

ex:
Error
:UN_MEDIDA
:id_item

It seems some problem in the parse of the parameters that it ends up condemning the query and sending to raize of the transaction.

[marsupilami]

Hello Lenin,

erm - that SQL statement is quite big. Could you please try to create an example application with a minimum SQL statement that triggers this error? Usually this error happens when Zeos tries to free a statement after some other error happened. But also Zeos shouldn't try that anymore because prepared statements now only should be freed when a new transaction was started...

With best regards,

Jan