25

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.

Warren P
  • 65,725
  • 40
  • 181
  • 316
  • Does it make a difference if you separate the two commands with (for SQL Server) a semi-colon? –  Jun 14 '12 at 14:35
  • In projects that don't need data-aware controls, I've switched to using ADODB and ADOInt directly some times. Like here: http://xxm.svn.sourceforge.net/viewvc/xxm/trunk/Delphi/demo2/03%20Data/xxmData.pas?view=markup I haven't checked what this does for performance, but really should check one of these days (and post my results here as answer)... – Stijn Sanders Jun 14 '12 at 17:40
  • SilentD: I will try the semicolon. My recollection is that Delphi ado components don't support separators well, either. – Warren P Jun 14 '12 at 20:24
  • Would be interesting to know the Delphi version because I remember some pretty ugly bugs in Delphi 7.1 for example. Not saying they are all gone by now but it might reduce the risk. When talking about parameters, did you try clearing the statement, calling Prepare (iirc). Did you try using TADOQuery or TADODataSet instead? – Stefan Glienke Jun 14 '12 at 22:22
  • This is in XE2, but I have seen the same issues in 2007, 2009, 2010, and XE. – Warren P Jun 14 '12 at 22:38
  • @WarrenP, have you try ParamCheck property set to false ? – Hendra Jun 17 '12 at 04:14
  • What OLEDB provider are you using? Almost certainly this is provider-specific behaviour. – Ben Jun 17 '12 at 16:26
  • I'm using "Microsoft OLEDB Provider for SQL Server." – Warren P Aug 08 '12 at 14:02
  • @WarrenP : Just wondering - is this project, part of a project group? – Bimmer_R Aug 21 '12 at 11:07
  • 1
    How would that matter? Project groups don't change the behaviour of ADO components on a DFM form. – Warren P Aug 24 '12 at 03:28

3 Answers3

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)?

Fabricio Araujo
  • 3,810
  • 3
  • 28
  • 43
0

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.

Stephen Quan
  • 21,481
  • 4
  • 88
  • 75
  • 3
    "Usually, in ADODB, the named parameters are prefixed with an at (@)" -- ADODB doesn't have named parameters except for the parameters of stored procedures, unless you have a special provider that adds support for them. The commonly used SQLOLEDB provider doesn't have them, at least. In ADO.NET, named parameters are possible, with the @ prefix you mention, but ADO.NET cannot be used easily from Delphi. –  Oct 04 '12 at 22:25
0

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.

Warren P
  • 65,725
  • 40
  • 181
  • 316