Do ADO components, especially TADOCommand, work more reliably with un-named, or named parameters, in some cases?
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. 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:
ADO doesn't support multiple commands, or at least Delphi's wrappers don't. Or maybe TADOCommand just doesn't work reliably here.
Parameters are a buggy area in all of ADO, or all of Delphi's ADO wrappers?
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.
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)?
Unfortunately, I've not used Delphi in awhile, so, and I don't have the means to validate this answer from the Delphi perspective.
This is the first time I've seen named parameters prefixed with a colon (:). Usually, in ADODB, the named parameters are prefixed with an at (@) and, yes, unnamed parameters are given with a question mark (?).
One significant advantage of named parameters is their ability to be reused, i.e.
INSERT INTO TABLE T VALUES (@id, @id, 'Hello World');
At the ADODB level. Once you used parameters, either named, or unnamed, you can use CommandText.Parameters.Refresh as a fast means of creating the parameters.
Yes there are some cases where parameters with ? fail. I have found sometimes I need to use :named parameters. Named parameters have an advantage for working with the DB Parameter values, since having the Name property set makes debugging the ADO query or dataset or table easier as well.
I do not understand why. If you have this problem, first check you are using the correct OLEDB provider, and check what version. Also check for potential parsing errors caused by bad SQL generated by you.
I suspect that an internal behaviour inside the OLEDB provider in code that I do not have source code for is to blame for this quirk. The Delphi ADO class wrappers are translators from Delphi's database component layer architecture to ADO's core query/table/dataset APIs all of which are under the hood wrappers around a set of COM objects that deal with ADO RecordSets.