Insert Statment with Returning - TSqlDataSet

[wiglan]

Hi,

I am migrating my connection to use Driver DevArt for firebird.

Previously, I used inserts that returned fields with "Returning" without problems.

But now, this same code returns the error:

vSQL := TSQLDataSet.Create(Self);
try
vSQL.SQLConnection := dmDDB.SQLConection;
vSQL.CommandText := 'update or insert into CAD_TAB_IBPT_CAB ' +
'(VERSAO,VIGENCIA_INI,VIGENCIA_FIM,UF,CHAVE) ' +
'values (:VERSAO,:VIGENCIA_INI,:VIGENCIA_FIM,:UF,:CHAVE)' +
'matching(UF,CHAVE) returning (CODIGO)';

vSQL.ParamByName('CHAVE').AsString := '10001';
vSQL.ParamByName('VERSAO').AsString := '2';
vSQL.ParamByName('VIGENCIA_INI').AsDate := now;
vSQL.ParamByName('VIGENCIA_FIM').AsDate := now;
vSQL.ParamByName('UF').AsString := 'RJ';

vSQL.Open;

a := vSQL.Fields[0].asstring;

if a <> '' then
showmessage(a);

finally
FreeAndNil(vSQL);
end;

Error:

exception class EDatabaseError with message 'Cursor not returned from Query'

If I add an output type parameter with the name CODIGO it will work, but I need to maintain compatibility with the 2 drivers.

Why doesn't this code work with the DevArt driver?

Thanks!

[oleg0k]

Hello,

To resolve the issue, you should manually add an output parameter to TSQLDataSet and provide a correct SQL statement. Try to replace

Code: Select all

vSQL.Open;
a := vSQL.Fields[0].asstring;

with

Code: Select all

with TParam(vSQL.Params.Add) do begin
  Name := 'CODIGO';
  DataType := ftInteger;
  ParamType := ptOutput;
end;
vSQL.ExecSQL;
a := vSQL.ParamByName('CODIGO').AsString;

wbr, Oleg
Devart Team

[wiglan]

Well, this code really solves the DevArt driver problem.

However, this way I can no longer get the correct feedback with the old dbExpress driver. The parameter return is always "0".

In addition, I would have to rewrite my entire application with a compilation directive to define which connection driver I am using. It is not a healthy solution.

Is there no other way to get the return value? Shouldn't this feature be compatible with DevArt to address a native DevExpress feature?

In fact, I would like a solution with the least possible impact, and rewriting my application would be the worst of them.

[wiglan]

Guys, the proposed solution doesn't really make sense, it goes against the "Embarcadero" usage guidelines.

According to Delphi Help Docs, the command "ExecSQL" must be used for statments that do not return a data set.

See the complete documentation.

Code: Select all

Data.SqlExpr.TSQLDataSet.ExecSQL

function ExecSQL(ExecDirect: Boolean = False): Integer; override;

Description

Executes a query or stored procedure that does not return a set of records. 

Call ExecSQL to execute an SQL command that does not return a set of records. This command is a query other than a SELECT query, such as an INSERT, UPDATE, DELETE, or CREATE TABLE query. 

ExecDirect indicates that the query does not need to be prepared before it is executed. This parameter can be set to true if the query does not include any parameters. When ExecDirect is false, the query will be prepared before it is executed. Even when ExecDirect is false, you can avoid preparing the query every time ExecSQL is called by setting the Prepared property to true before calling ExecSQL the first time. 

ExecSQL returns the number of rows affected by the executed command. This becomes the value of the RowsAffected property. 

Note: Do not use ExecSQL for commands that return data. These include any use of the ctTable command type, SELECT queries, and stored procedures that return a cursor. When the command returns data, use the Open method or set the Active property to true

That is, I cannot get the return data based on ExecSql, I must use "Open". This is working perfectly in the DevExpress driver, unlike DevArt.


However, I would really like to use DevArt. Didn't we get a different solution for that?

Thanks!

[oleg0k]

Hello,
The ExecSQL method works exactly as described in the documentation. When executing the INSERT ... RETURNING statement, ExecSQL returns the result as parameters rather than a recordset. The behavior of the driver is correct: the Open method throws an exception since no recordset is returned for a query.

wbr, Oleg
Devart Team

[wiglan]

I believe the problem here is that the compatibility of the methods in DevArt in relation to the old DBExpress driver was not maintained, because before I used the Open Method normally to obtain return of insert, update, delete instructions. But now with DBExpress, I need to replace Open with ExecSql and output type parameters.

The big problem with this is that I will have to rewrite my entire application. : /


In relation to documents, what I mean is that "Embarcadero" does not guide the use of the ExecSql method to obtain returns, even as a parameter. I am saying this based on this link, but I may be wrong.

http://docwiki.embarcadero.com/Librarie ... et.ExecSQL

[wiglan]

So, guys, any positioning?

saw the issue of compatibility and documents?

thanks!

[oleg0k]

Hello,
dbExpress is intended to work with different databases through a uniform interface. In most cases, database engines return the result of the INSERT ... RETURNING statement as OUT parameters. Perhaps you were using some other dbExpress driver earlier, which returned the result as a record set rather than OUT parameters. This is not a common behavior, but a custom design decision in a specific driver. Our drivers comply with the dbExpress design standard.

wbr, Oleg
Devart Team