A: TADQuery is the mix of the TADMemTable, TADTableAdapter and few TADCommand's. So, the TADQuery has everythings inside to execute SQL command, send parameters data, receive and stored result sets, browse result sets, post changes back to a DB. There is no reason to use TADQuery + DSP + CDS. 

You can use TADMemTable, TADTableAdapter, TADCommand directly, instead of TADQuery. It gives more flexibility. For example, synchronized cached updates accross few datasets. But it also requires more coding. 

IOW, TADQuery is optimal "shortcut" for every day data application programming. 

A: Exclude fiMeta from FetchOptions.Items. 

When you are creating the parameters by hands, you should exclude fiMeta from FetchOptions.Items. When it is specified, then AnyDAC will fetch the stored procedure parameter definitions from a DB and will repopulate the Params collection. 

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

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

The »[AnyDAC][Phys]-310. Cannot execute command returning results set.« exception is raised, when application is executing the ExecSQL method for a SQL command, which is returning a result set. Does the command return a result set or not is determined by the AnyDAC SQL command preprocessor. If the command is recognized as SELECT or one of it forms, then it returns a result set. Otherwise – not. 

In some cases, AnyDAC may fail to recognize a SQL command as returning or not a result set. And sometimes add-hok application need to execute a SQL command, not depending on how many result sets it returns. So, how to be in these case ? Here is two basic ways: 

1)

ADQuery1.OpenOrExecute

It may internally raise [AnyDAC][Phys]-308, but an exception will be not progated out of the OpenOrExecute and a SQL command will be really executed. Also, the method will return True, if the command returned a resultset. 

2)

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

Just say to AnyDAC, that the command is really an INSERT or what else kind, which does not return a result set. 

A: 1) 90,000 is tooooo much for any DBMS, because the data will be cached few times (parameters, DBMS API buffer, network pocket buffer, etc). Also, probably, each record has a "valuable" size. Even, if that is not True, then an application may run into the DBMS API limitations. The most "dark" thing there is the high limit for the Array size. Oracle - up to $7FFF. Other DBMS's - depends on the network pocket size, etc ... 

2) Split 90,000 into the chunks of the 500-5000 items, each one. See AD03-ArrayDML demo regarding 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. 

We were the lucky to find an emperical formula, allowing to determine maximum size of an array. And AnyDAC automatically splits one large array into few chunks. 

It is not that simple to find similar to Oracle. While it supports the size up to $7FFF, I was getting AV's or other issues with large arrays ... 

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

A: Check SourceEOF property. 

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

SELECT <AName>.CURRVAL FROM dual

For MySQL it will access to 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 be just ignored. 

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

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

For PostgreSQL that will be 2 separated commands: 

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

When you are posting an insert to a DB, using TDataSet Insert / Post methods, AnyDAC looks at the DBMS kind and generates appropriate efficient SQL command[s]. 

A: Two options: 

1)

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

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

EADDBEngineException(AException).Kind = ekCmdAborted

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

ADQuery.SQL.Text := 'select * from xy where Fieldname = ''xxx&n''';
ADQuery.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, the '&n' will be replaced by the empty string. If you do not use macros, then set ResourceOptions.MacroCreate and MacroExpand to False. 

A: For example, consider the code: 

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

There may be different reasons for that:

• DB does not have "TestProc" stored procedure, then it must be created;
• depending on a DBMS, "TestProc" may be in invalid state, then it must be validated;
• depending on a DBMS, the stored procedure name is in mixed case, then it must be quoted (more);
• the fiMeta is excluded from FetchOptions.Items, then it must be included or parameters must be created by hands (more);
• stored procedure does not have "Par" parameter, the parameter name must be corrected or "Par" parameter must be added;
• depending on a DBMS, the parameter name may be prefixed with '@' or ResourceOptions.UnifyParams set to True.