1 (edited by prahousefamily 2018-07-04 08:15:52)

Topic: Example Query Parameters Like Navicat ! [$any_name]

I Will MVD Community Happy In This Script Help Insert parameter  in statement 
Use applied InputBox Function in MVD Insert in  statement query string like feature Navicat Query Parameters


Navicat Feature

Query Parameters

Query Builder and Query Editor both support using of parameters inside the query text. You can set query parameters to add variable values to a query each time you run it. The parameter should appear as an identifier with $ at its beginning, quote with [ ], e.g. [$any_name].

Execute the query and the Input Parameter Dialog is provided for you to enter the desired data you wish to search.

Navicat Screen
https://i.imgur.com/WwWrDoI.png


MVD Screen Like Navicat
https://i.imgur.com/YhGD25O.png


Open Code Script

//Advance Like Navicat
procedure Form1_Button2_OnClick (Sender: TObject; var Cancel: boolean);
var
st_temp : String;
vt_name : tstringlist;
result_sql : String;
get_x,get_y : Integer;
para_x,para_y : Integer;
paraname : string;
begin
    st_temp := Form1.Memo3.Text ;
    result_sql := Form1.Memo3.Text;
    vt_name := tstringlist.Create ;
    vt_name.Clear ;
    Repeat
    begin
        get_x    := Pos('[$',st_temp);
        st_temp  := Copy(st_temp,get_x,Length(st_temp));
        get_y    := Pos(']',st_temp) ;
        paraname := Copy(st_temp,1,get_y);
        IF Pos(paraname,vt_name.Text) <= 0 Then
        vt_name.Add(paraname) ;
        st_temp  := copy(st_temp,get_y+1,Length(st_temp) );
    end;
    until Pos('[$',st_temp) <= 0;
    For para_x := 0 To  vt_name.Count -1 Do
    Begin
        result_sql :=
        ReplaceStr(result_sql ,vt_name[para_x],
        '"'+
        InputBox('Input Parameter',ReplaceStr(ReplaceStr(vt_name[para_x],'[$',''),']','') ,'')+
        '"'
        );
    end;
    Form1.TableGrid1.dbSQL := result_sql;
    Form1.TableGrid1.dbSQLExecute ;
    vt_name.Clear ;
End;
// Core Script Search Parameter And Distinct Insert In Stringlist
procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
st_temp : String;
get_x,get_y : Integer;
paraname : string;
begin
    Form1.Memo2.Clear;
    st_temp  := Form1.Memo1.Text ;
    Repeat
    begin
        get_x    := Pos('[$',st_temp);
        st_temp  := Copy(st_temp,get_x,Length(st_temp));
        get_y    := Pos(']',st_temp) ;
        paraname := Copy(st_temp,1,get_y);
        IF Pos(paraname,Form1.Memo2.Text) <= 0 Then
        Form1.Memo2.Lines.Add(paraname) ;
        st_temp  := copy(st_temp,get_y+1,Length(st_temp) );
    end;
    until Pos('[$',st_temp) <= 0;
End;
procedure Form1_OnShow (Sender: TObject; Action: string);
begin
    Form1.Memo1.lines.loadFromFile('Lorem.txt');
    Form1.Memo3.Text :=
    'SELECT' + #13#10 +
    '    CAST ([$paraname1] AS text) AS paraname1,' + #13#10 +
    '    CAST ([$paraname2] AS text) AS paraname2,' + #13#10 +
    '    CAST ([$paraname1] AS text) AS paraname3_like_paraname1';
end;
begin
end.             
Post's attachments

Attachment icon Parameter.zip 328.33 kb, 482 downloads since 2018-07-04 

My Visual Database : I Love You
Easy For Beginner Student For Me

Re: Example Query Parameters Like Navicat ! [$any_name]

Thank you for the example!

Dmitry.

3 (edited by prahousefamily 2018-08-02 03:44:25)

Re: Example Query Parameters Like Navicat ! [$any_name]

Fix 2018-08-02
---- Check  variable  before execute 

IF vt_name[para_x] <> '' Then

Full Fix Code

//Advance Like Navicat
procedure Form1_Button2_OnClick (Sender: TObject; var Cancel: boolean);
var
st_temp : String;
vt_name : tstringlist;
result_sql : String;
get_x,get_y : Integer;
para_x,para_y : Integer;
paraname : string;
begin
    st_temp := Form1.Memo3.Text ;
    result_sql := Form1.Memo3.Text;
    vt_name := tstringlist.Create ;
    vt_name.Clear ;
    Repeat
    begin
        get_x    := Pos('[$',st_temp);
        st_temp  := Copy(st_temp,get_x,Length(st_temp));
        get_y    := Pos(']',st_temp) ;
        paraname := Copy(st_temp,1,get_y);
        IF Pos(paraname,vt_name.Text) <= 0 Then
        vt_name.Add(paraname) ;
        st_temp  := copy(st_temp,get_y+1,Length(st_temp) );
    end;
    until Pos('[$',st_temp) <= 0;
    For para_x := 0 To  vt_name.Count -1 Do
    Begin
IF vt_name[para_x] <> '' Then
        result_sql :=
        ReplaceStr(result_sql ,vt_name[para_x],
        '"'+
        InputBox('Input Parameter',ReplaceStr(ReplaceStr(vt_name[para_x],'[$',''),']','') ,'')+
        '"'
        );
    end;
    Form1.TableGrid1.dbSQL := result_sql;
    Form1.TableGrid1.dbSQLExecute ;
    vt_name.Clear ;
End;
// Core Script Search Parameter And Distinct Insert In Stringlist
procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
st_temp : String;
get_x,get_y : Integer;
paraname : string;
begin
    Form1.Memo2.Clear;
    st_temp  := Form1.Memo1.Text ;
    Repeat
    begin
        get_x    := Pos('[$',st_temp);
        st_temp  := Copy(st_temp,get_x,Length(st_temp));
        get_y    := Pos(']',st_temp) ;
        paraname := Copy(st_temp,1,get_y);
        IF Pos(paraname,Form1.Memo2.Text) <= 0 Then
        Form1.Memo2.Lines.Add(paraname) ;
        st_temp  := copy(st_temp,get_y+1,Length(st_temp) );
    end;
    until Pos('[$',st_temp) <= 0;
End;
procedure Form1_OnShow (Sender: TObject; Action: string);
begin
    Form1.Memo1.lines.loadFromFile('Lorem.txt');
    Form1.Memo3.Text :=
    'SELECT' + #13#10 +
    '    CAST ([$paraname1] AS text) AS paraname1,' + #13#10 +
    '    CAST ([$paraname2] AS text) AS paraname2,' + #13#10 +
    '    CAST ([$paraname1] AS text) AS paraname3_like_paraname1';
end;
begin
end.   
My Visual Database : I Love You
Easy For Beginner Student For Me

4 (edited by sparrow 2022-09-16 14:02:01)

Re: Example Query Parameters Like Navicat ! [$any_name]

Hi all,


In memory ) of the forgotten Regular Expression (TRegExp) is dedicated.
Code modified without agreement but simplified.

Post's attachments

Attachment icon Parameter-regexp.zip 336.42 kb, 180 downloads since 2022-09-16 

Re: Example Query Parameters Like Navicat ! [$any_name]

sparrow wrote:

Hi all,


In memory ) of the forgotten Regular Expression (TRegExp) is dedicated.
Code modified without agreement but simplified.


Thank You sparrow Best Example TRegExp

My Visual Database : I Love You
Easy For Beginner Student For Me