TFDQuery, TFDStoredProc and TFDUpdateSQL Questions

From RAD Studio
Jump to: navigation, search

Go Up to FAQ (FireDAC)

This topic contains a list of questions and answers related to TFDQuery, TFDStoredProc and TFDUpdateSQL.

Q1: Can I use TFDQuery and connect it to a dataset provider and retrieve the data in an Embarcadero client dataset?

A: TFDQuery is a mix of TFDMemTable, TFDTableAdapter and several TFDCommand's. So, TFDQuery has everything inside to execute SQL commands, send parameter data, receive and store result sets, browse result sets and post changes back to a database. There is no reason to use TFDQuery + DSP + CDS.

You can use TFDMemTable, TFDTableAdapter and TFDCommand directly, instead of TFDQuery. They give more flexibility, but also require more coding. Take for example synchronized cached updates across datasets.

In other words, TFDQuery is an optimal "shortcut" for every day data application programming.

Q2: How can I force FDStoredProc to use parameters specified manually?

A: Exclude fiMeta from FetchOptions.Items.

When you create parameters manually, you should exclude fiMeta from FetchOptions.Items. When it is specified, FireDAC will fetch the stored procedure parameter definitions from a database and will repopulate the Params collection.

If you have difficulties with manual definition of parameters, populate the Params collection automatically and check how the parameters are defined. Then compare that to your code.

Q3: '[FireDAC][Phys]-308. Cannot open / define command, which does not return result sets' and '[FireDAC][Phys]-310. Cannot execute command returning results set'. What do these exceptions mean?

A: The '[FireDAC][Phys]-308. Cannot open / define command, which does not return result sets' exception is raised when the application is executing the Open method for a SQL command that does not return a result set. The exception is raised after the SQL command is executed, but the DBMS has not returned any result set.

The '[FireDAC][Phys]-310. Cannot execute command returning results set' exception is raised when the application executes the ExecSQL method for a SQL command that returns a result set. If the command returns a result set or not is determined by the FireDAC SQL command preprocessor. If the command is recognized as SELECT or one of its forms, then it returns a result set; otherwise, it does not.

In some cases, FireDAC may fail to recognize a SQL command as returning or not a result set. And sometimes ad hoc applications need to execute a SQL command, not depending on how many result sets it returns. So, what should be done in these cases? Here are two basic solutions:

1)

FDQuery1.OpenOrExecute;

It may internally raise [FireDAC][Phys]-308, but an exception will not be propagated out of OpenOrExecute and a SQL command will be really executed. Also, the method returns True if the command returns a result set.

2)

FDQuery1.Command.CommandKind := skInsert;
FDQuery1.ExecSQL;

All you need to do is to indicate to FireDAC the type of commands (INSERT or others) that do not return a result set.

Q4: 'Out of memory' exception is raised when calling FDQuery.Execute(FDQuery.Params.ArraySize). ArraySize is about 90,000. What is wrong?

A: 1) 90,000 is too much for any DBMS, because the data will be cached several times (parameters, DBMS API buffer, network packet buffer, etc). Also, probably, each record has a large size. Also, an application may run into DBMS API limitations, like the high limit for the Array size. For Oracle, this is up to $7FFF. For other DBMSs, it depends on the network packet size, etc.

2) Split 90,000 into chunks of 500-5000 items each. See AD03-ArrayDML demo for that. In general, fill an array up to the chunk size, then call Execute with the chunk size, then fill again and Execute, etc.

With Firebird, I use ArraySize = 1,000,000 (60 sec) without problems.

An empirical formula was found, allowing us to determine the maximum size of an array. FireDAC automatically splits one large array into several chunks.

It is not that simple to find a similar one for Oracle. While it supports a size up to $7FFF, AVs or other issues with large arrays are still encountered.

Anyway, if a record size is too big, then you can run out of memory even before calling Execute.

Q5: Is it possible to know if all FDQuery records are fetched?

A: Check SourceEOF property.

Q6: I want to insert a record (via plain SQL) and get back the IDENTITY / SEQUENCE. What is the most efficient, multi-database friendly way?

A: 1) TFDConnection has GetLastAutoGenValue method. Depending on the DBMS, it will return a last auto-generated value in a session. For example, for Oracle it is:

SELECT <AName>.CURRVAL FROM dual

For MySQL, it will access the MYSQL API to get the value without a SQL query. Also, if a DBMS does not support the sequences/generators, then AName parameter value will just be ignored.

2) There is no common way to write a SQL command that will insert a record and return an auto-generated value. For example, for Oracle it is:

INSERT ... INTO ... RETURNING ID INTO :ID

For PostgreSQL, there are two separate commands:

INSERT ... INTO ..
SELECT CURRVAL(...)

When you are posting an insert to a database using TDataSet Insert/Post methods, FireDAC looks at the DBMS kind and generates appropriate efficient SQL commands.

Q7: How can I rollback the transaction after the user canceled the query execution in amCancelDialog mode?

A: There are two options:

1)

FDTransaction1.StartTransaction;
try
  FDQuery1.ExecSQL;
  FDTransaction1.Commit;
except
  on E: EAbort do
    // user canceled the command execution
    ADTransaction1.Rollback;
end;

2) Create TFDQuery.OnError event handler. When a command execution is canceled, this event handler will be called and AException parameter will meet the condition:

EFDDBEngineException(AException).Kind = ekCmdAborted;

Q8: My query containing the '&', '!' characters fails to execute correctly. What is wrong?

A: For example, the following query with default options will fail:

FDQuery1.SQL.Text := 'select * from xy where Fieldname = ''xxx&n''';
FDQuery1.open;

The SQL command received by a DBMS will miss '&n'. That is because '&' specifies the beginning of a macro variable. By default, every variable has empty value. So, '&n' will be replaced by an empty string. If you do not use macros, then set ResourceOptions.MacroCreate and MacroExpand to False.

Q9: I am getting 'Parameter Xxx not found' error when accessing stored procedure parameters. What is wrong?

A: For example, consider the code:

FDStoredProc1.StoredProcName := 'TestProc';
FDStoredProc1.Prepare;
FDStoredProc1.ParamByName('Par').AsInteger := 100;

There may be different reasons for that:

  • The database does not have the "TestProc" stored procedure and it must be created;
  • Depending on the DBMS, "TestProc" may be in invalid state and it must be validated;
  • Depending on the DBMS, the stored procedure name is in mixed case and it must be quoted;
  • The fiMeta is excluded from FetchOptions.Items; it must be included or parameters must be created manually;
  • The stored procedure does not have Par parameter; the parameter name must be corrected or Par parameter must be added;
  • Depending on the DBMS, the parameter name may be prefixed with '@' or ResourceOptions.UnifyParams set to True.