delphitadoquery

ADO Query treats goto labels as parameters


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?


Solution

  • 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;