- changed status to resolved
Firedac will not allow the TFDQuery.Open command when the associated SQL does not return a resultset
Another possible issue with TInsertExecutor.Execute method in Spring.Persistence.SQL.Commands.Insert unit. The code creates an insert statement, but then calls ExecuteQuery on the Statement.
TFireDACStatementAdapter.ExecuteQuery in the Spring.Persistence.Adapters.FireDAC unit then Creates a TFDQuery and calls Open. Firedac then throws an exception because no ResultSet was returned. The exception then recommends that you use TFDQuery.Execute rather than TFDQuery.Open. when your SQL statment does not return a resultset.
I have for now Modified the TFireDACStatementAdapter.ExecuteQuery method to look for Select on the front of the SQL statement and call Open else call execute. This is not pretty but it solves my immediate problem .
function TFireDACStatementAdapter.ExecuteQuery(
serverSideCursor: Boolean): IDBResultSet;
var
query: TFDQuery;
begin
inherited;
query := TFDQuery.Create(nil);
query.Connection := Statement.Connection;
query.SQL.Text := Statement.SQL.Text;
query.Params.AssignValues(Statement.Params);
query.DisableControls;
if serverSideCursor then
query.FetchOptions.CursorKind := ckForwardOnly;
try
if StartsText('Select', Statement.SQL.Text) then
query.Open
else
Query.Execute;
Result := TFireDACResultSetAdapter.Create(query);
except
on E:Exception do
begin
//make sure that resultset is always created to avoid memory leak
Result := TFireDACResultSetAdapter.Create(query);
raise EFireDACAdapterException.CreateFmt(EXCEPTION_CANNOT_OPEN_QUERY, [E.Message]);
end;
end;
end;
After looking at the code in TInsertExecutor.Execute method it looks like some Data Access Libraries might actually return the inserted result in a resultset so as to get the generated Identity Value, as seen in this code
resultSet := statement.ExecuteQuery(false);
value := GetIdentityValue(resultSet);
if not value.IsEmpty then
entityWrapper.SetPrimaryKeyValue(value);
So I wasn't sure it would be a good idea to just change TInsertExecutor.Execute to call the Statements.Execute method for reasons of compatability with other Access Libraries.
Comments (5)
-
-
reporter Even though the code was changed to use OpenOrExecute method of the Firedac query an insert is still failing with the following error.
I would therefore again suggest the following code change in this method.
if StartsText('Select', Statement.SQL.Text) then query.Open else query.Execute;
-
@Toddflora OpenOrExecute should catch this exception and then proceed with the execution. Have you tried running your project without the debugger?
-
reporter My apologies you are correct. the exception is only occurring in the design environment, and is caught by FireDac and swallowed. which then causes it to Execute instead of Open. I don't really care for this type of methodology where an exception is caught and then another action taken, but this is Firedac's issue not yours.
-
repo owner - changed status to closed
- Log in to comment
fixes
#70→ <<cset 90af88de45a0>>