Error with autocommit and parameters

The forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
lenin
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 27.03.2017, 19:05

Error with autocommit and parameters

Postby lenin » 13.04.2017, 13:48

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
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 27.03.2017, 19:05

Re: Error with autocommit and parameters

Postby lenin » 13.04.2017, 13:55

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
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 27.03.2017, 19:05

Re: Error with autocommit and parameters

Postby lenin » 13.04.2017, 15:33

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
Gold Boarder
Gold Boarder
Posts: 658
Joined: 17.01.2011, 14:17

Re: Error with autocommit and parameters

Postby marsupilami » 15.04.2017, 21:38

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


Return to “ZeosLib 7.2 Forum”

Who is online

Users browsing this forum: No registered users and 1 guest