0

My Delphi 2006 TADOCommand has it's 

    CommandText (including parameters),  
    Prepared (True),  
    ParamCheck (True)

properties all set at design time.

Is there a way for me to go about deleting some of it's Parameters at run time, without having to change the CommandText to accomodate these changes. This is desireable in the case that some of the table columns that the parameters reference do not need updating/inserting.

I'm thinking of something along the lines of 

TADOCommand.Parameters.ParamByName('MyParam').SafelyRemove;

Many Thanks,
Duncan

RRUZ
  • 134,889
  • 20
  • 356
  • 483
DuncanGiles
  • 3
  • 2

3 Answers3

4

Each item in Parameters collection corresponds to parameter marker in the command text. It is like API to this marker. Removing items from Parameters will not remove the corresponding marker. If you does not need to touch some field in your command text, then you have to modify command text and remove corresponding parameter marker.

When command text is prepared, the DBMS has built command execution plan and allocated some resources, required for command execution. If you will change the command text, then DBMS has to build new command execution plan.

Kind of that ...

da-soft
  • 7,670
  • 28
  • 36
  • 2
    Further, removing the parameter from the command text is not trivial. You'll need an SQL validator to make sure the final SQL is valid, but also a bunch of rules governing what gets removed in each case. Turning "DELETE FROM Customers WHERE CustomerID=:CustID" into "DELETE FROM Customers" is easy, but what about more complex queries like DELETE FROM Orders WHERE OrderLanguage = :language AND ShipToCustomer IN (SELECT CustomerID FROM Customers WHERE Address_Country_Language <> :language)? –  Oct 12 '10 at 04:29
  • Thank you very much to everybody for their answers. Unfortunately you have confirmed my suspicions. In this case I'll rebuild the command text from scratch rather than attempting to selectively modify the existing string. – DuncanGiles Oct 15 '10 at 12:58
0

Because parameters placeholders (:PARAMn) are embedded in the SQL text, I don't see how you can add or remove parameters without changing CommandText. You would have a mismatch.

Francesca
  • 21,452
  • 4
  • 49
  • 90
  • Thank you very much to everybody for their answers. Unfortunately you have confirmed my suspicions. In this case I'll rebuild the command text from scratch rather than attempting to selectively modify the existing string. – DuncanGiles Oct 15 '10 at 13:00
0

In case when the command text is dynamic-updateble i usually working with params like this: *Parameters.Clear; Parameters.ParseSQL(ADOCommand.CommandText, true); * it will automatically create parameter list with correct parameters names.

kutsoff
  • 325
  • 2
  • 7