Get Params of a QueryString and their datatypes

Question

Let's say you have an delphi interface that has a TEdit where an user types and SQL Query

SELECT * FROM X WHERE   X.A = :A and  X.B = :B and X.C= :C

Is there a way to get the types of the :A,:B,:C Params? I managed to get the name of the Params. using this code

procedure TfrmA.Button1Click(Sender: TObject);
var
    queryInput, queryOutput , aux : string;
    pozitie : Integer;
    param : string;
    Ch,Chx : Char;
begin
    queryInput :=  TEdit.Text;
    queryOutput := StringReplace(queryInput,':','@',[rfReplaceAll, rfIgnoreCase]);
    aux := Copy(queryOutput,AnsiPos('@',queryOutput),200);
    while Length(aux ) > 0 do   begin
    if   AnsiPos('@',aux ) = 0 then break;
    for Ch in aux do
    begin
      if Ch = ' ' then begin
      param := param + ' ';
      break
      end else param := param + Ch;
    end;
    ShowMessage(param);
    test := Copy(test,2,200);
    test := Copy(test,AnsiPos('@',test),200);
    end;
end;

Param string will containt now : @A @B @C

Is there anyway I can find out the datatype of the params?

you will need to inform the field and table to know the datatype — Passella, Sep 01 '14 at 14:03
you asked a similar question recently, in this case you need the `sp_describe_undeclared_parameters` function to retrieve the datatypes. — whosrdaddy, Sep 01 '14 at 14:19
nitpicking comment, if you are using D2009 or higher, you should get rid of `AnsiPos`and use `Pos` function instead. — whosrdaddy, Sep 01 '14 at 14:20
@whosrdaddy that question was abount Fields, not abount Params... belive me I won't ask a question unless I tried all possible methoes I could come up with and without surfing the net for answers. — CiucaS, Sep 01 '14 at 18:42
If you read the docs about `dm_exec_describe_first_result_set` (answer from previous question), they also point to `sp_describe_undeclared_parameters`which is the answer in this case... — whosrdaddy, Sep 02 '14 at 07:12

bummi · Accepted Answer · 2014-09-01T15:07:36.970

1

If you are able to use a Adodataset you might use this to get the needed informations. uses TypInfo;

procedure TForm5.Button1Click(Sender: TObject);
var
 i:Integer;
begin
  Ads.CommandText := 'Update Adressen set geboren=:birthdate where name=:n and ID=:i';
  Memo1.Lines.Clear;
  for I := 0 to Ads.Parameters.Count - 1 do
      begin
         Memo1.Lines.Add(Ads.Parameters[i].Name + ' : ' + GetEnumName(TypeInfo(TDataType), Integer(Ads.Parameters[i].DataType)) + ' : '+ IntToStr(Ads.Parameters[i].Size) )
      end;
end;

The Output of the example will be:

birthdate : ftDateTime : 16
n : ftWideString : 40
i : ftInteger : 4

edited Sep 01 '14 at 15:07

answered Sep 01 '14 at 14:57

bummi

27,123
14
62
101

it seems it won't see any params. procedure TfrmRapoarte.est21Click(Sender: TObject); var i:Integer; Ads : TADODataSet; param : string; begin Ads := TADODataSet.Create(Self); param := 'SELECT * FROM X where A= :A and YEAR(B)= :B and C = :C'; Ads.CommandText := param; for I := 0 to Ads.Parameters.Count - 1 do begin Memo1.Lines.Add(Ads.Parameters[i].Name + ' : ' + GetEnumName(TypeInfo(TDataType), Integer(Ads.Parameters[i].DataType)) + ' : '+ IntToStr(Ads.Parameters[i].Size)); end; end; – CiucaS Sep 02 '14 at 06:14
2

You will have to define a connection before setting the commandtext: `Ads.Connection := AdoConnection1;` – bummi Sep 02 '14 at 08:42

Get Params of a QueryString and their datatypes

1 Answers1