The list of questions and answers related to TADQuery, TADStoredProc and TADUpdateSQL.
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:
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.
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:
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:
What do you think about this topic? Send feedback!