Do ADO components, especially TADOCommand, work more reliably with un-named, or named parameters, in some cases?

Question

There are several questions on StackOverflow that seem to have answers that contradict each other on the subject of ADO/OleDB, delphi TADOQuery/TADOCommand and the subject of parameters.

Parameters can be introduced two ways, in the CommandText or SQL property of an ADO component. Named parameters, which work most of the time for me, are introduced with colons:

  select a, b, c from bar where bat = :baz 

This works, 99% of the time for me, just fine. Every now and then I find that ADO or Delphi's wrappers around it, won't accept ":baz" and requires that I write this instead:

  select f, g, h from bar where bat = ? 

This results in an unnamed parameter, instead of a named parameter. When an ADO Query or ADO Command contains only one parameter, this isn't a big deal. But that's not when ADO acts up on me. Yesterday it acted one way, and today, a different way with a dual-command in a single TADOCommand object, like this, with two commands in one CommandText string:

delete from bar where id = :id1 delete from bat where id = :id2 

I had to change it to this:

delete from bar where id = ? delete from bat where id = ? 

It worked all day yesterday. Today, I had to change it back to the first version, to get it to work. The symptom was that the ADO parameters disappeared and would not come back, and when I try to execute the command I get an error, index out of range, when I try to access Parameters[0]. Nothing gives me any warning that the parameters are going away. It seems that a few connections to the ADO dataset, at designtime, jogs the TADOCommand component, in particular, and it "just breaks on me". It is particularly maddening when you're trying to write a query or a command, and you know it works, but the ADO component has decided not to accept "?" or ":x" right now. You can get around its total inability to function by switching from one to the other. But it frustrates me, and probably actually completely blocks other people. I know some people always dynamically build their SQL in code, and avoid using Parameters, and maybe this is why.

Possible answers to my question that I'm anticipating are:

1. ADO doesn't support multiple commands, or at least Delphi's wrappers don't. Or maybe TADOCommand just doesn't work reliably here.

2. Parameters are a buggy area in all of ADO, or all of Delphi's ADO wrappers?

3. You're doing it wrong.

I'm using Delphi XE2, but I've seen similarly dodgy behaviour in 2007, 2009, 2010, and XE. I'm using Microsoft OLEDB Provider for SQL Server as my OLEDB Provider.

Answer 1

Named parameters with :? I always used it with @, even on Visual Studio (ADO.NET). And in T-SQL parameters and variable are prefixed with @.

Do not remember having problems with that... Are you sure you doesn't choose Native Client (installed with an SQL server client install) instead of OLEDB Provider for SQL Server (which comes with Windows)?