ADO Query treats goto labels as parameters

Question

I'm facing an issue executing SQL script in a TADOQuery component. The script that's being executed has goto statements along with their corresponding labels, for example:

goto MyLabel

MyLabel:
  --do something else

However, the TADOQuery is seeing these : characters and treating them as parameters (which they are not parameters), and gives me an error:

Parameter object is improperly defined. Inconsistent or incomplete information was provided

How can I instruct the TADOQuery to not try to consider these as parameters?

Have you tried setting the ADOQuery's ParamCheck to false? D7 OLH says "You can suppress the automatic generation of parameter objects [...]by setting the ParamCheck property to False. This is useful for data definition language (DDL) statements that contain parameters as part of the DDL statement that are not parameters for the query itself. For example, the DDL statement to create a stored procedure may define parameters that are part of the stored procedure. By setting ParamCheck to False, you prevent these parameters from being mistaken for parameters of the query." — MartynA, Jul 15 '14 at 14:53
AFAIR, the alternative syntax I was thinking of in my deleted comment is to use a question mark where the param value should go. http://docwiki.embarcadero.com/RADStudio/XE4/en/Using_Parameters_in_Queries, 2nd para — MartynA, Jul 15 '14 at 15:05

MartynA · Accepted Answer · 2014-07-16T12:46:38.030

5

Set AdoQuery.ParamCheck to false.

Update The OP said in a follow-up comment that the above was sufficient for his immediate problem but wouldn't have worked if the query had contained actual :parameters. Initially, I couldn't get it to work with those either.

However, looking at the code of TParameters.ParseSQL in ADODB (D7), the author seems to have anticipated colons being embedded in the SQL (I mean, before any :paramname(s) one might enter to act as place-holders for TParameters), by treating a doubled-up colon (::) as a special case. So I think the intent was that one should double up any colon one doesn't want treated as a TParameter. To see what I mean, see Memo1's contents:

(PartialDFM)

object Memo1: TMemo
  Left = 32
  Top = 112
  Width = 297
  Height = 113
  Lines.Strings = (
    'declare'
    '  @number int'
    'select'
    '  @number = ?'
    'if @number > 0 goto positive'
    'if @number < 0 goto negative'
    ''
    'select ''zero'''
    ''
    'positive::'
    '  select ''positive'''
    '  goto'
    '    exitpoint'
    'negative::'
    '  select ''negative'''
    'exitpoint::')
end
object ADOQuery1: TADOQuery
  Connection = ADOConnection1
  Left = 64
  Top = 24
end

Then, the following works for me (displaying "positive", "negative" or "zero" in a DBGrid according to the value assigned to AdoQuery1.Parameters[0].Value)

procedure TForm1.DoQuery;
begin
  if AdoQuery1.Active
    then AdoQuery1.Close;
  // AdoQuery1.Prepared := True;
  AdoQuery1.SQL.Text := Memo1.Lines.Text;
  AdoQuery1.ParamCheck := False;
  ADOQuery1.Parameters.Clear;
  ADOQuery1.Parameters.CreateParameter('Param1', ftInteger, pdInput, 1, Null);
  AdoQuery1.Parameters[0].Value := 666;
  AdoQuery1.Prepared := True;
  AdoQuery1.Open;
end;

edited Jul 16 '14 at 12:46

answered Jul 15 '14 at 15:05

MartynA

30,454
4
32
73

Although this perfectly solved my problem, if I happened to be using parameters, it wouldn't. – Jerry Dodge Jul 15 '14 at 17:15
Ah, ok. Sounds like that's worth looking into, I'll have a go, it may be fun. Btw, pardon my curiousity, is this related to a q of yours I saw when I first started here last August, something about firing off a massive sequence of statements to a server to re-create stuff on it, and you were having probs with "GO" as a statement separator? – MartynA Jul 15 '14 at 17:43
That is correct, I'm picking back up the project now. The script has over 34,000 lines of script, and I'm working on parsing out comments at the moment :-| – Jerry Dodge Jul 15 '14 at 18:51
Thanks, I'll have a look at that tomorrow when I'm fresh. – MartynA Jul 15 '14 at 19:31
Updated, and it is working: http://pastebin.com/V3DWBtVL - except consideration of `GO` within a comment block such as `/* GO */` – Jerry Dodge Jul 15 '14 at 23:48
The parse behaviour on `::` is (somehow) documented http://docwiki.embarcadero.com/Libraries/en/Data.DB.TParams.ParseSQL – Sir Rufo Jul 16 '14 at 07:51
@Sir Rufo: Tks, good catch. The colon-parsing thing has been added to the OLH since D7, which I tend to use for SO qs just because I find it so much quicker and less fiddly to use that the XE+ IDEs. – MartynA Jul 16 '14 at 09:16
By the way, since I have this working, during my tests, I'm shocked at how lightning fast it is - about 3 times as fast as the SQL management tools. Probably because those tools do all kinds of unnecessary parsing and validation. – Jerry Dodge Jul 16 '14 at 13:58
@MartynA Just in case you were serious about looking into what I've written, I found major unforeseen flaws, and am re-structuring it to pre-populate an object list of "blocks" to be executed prior to actual execution. In the middle of the transition now. – Jerry Dodge Jul 17 '14 at 03:05

ADO Query treats goto labels as parameters

1 Answers1