ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/design/ibselectsqleditor.pas
(Generate patch)

Comparing ibx/trunk/design/ibselectsqleditor.pas (file contents):
Revision 17 by tony, Sat Dec 28 19:22:24 2013 UTC vs.
Revision 81 by tony, Mon Jan 1 11:31:10 2018 UTC

# Line 1 | Line 1
1 < (*
2 < *  IBX For Lazarus (Firebird Express)
3 < *
4 < *  The contents of this file are subject to the Initial Developer's
5 < *  Public License Version 1.0 (the "License"); you may not use this
6 < *  file except in compliance with the License. You may obtain a copy
7 < *  of the License here:
8 < *
9 < *    http://www.firebirdsql.org/index.php?op=doc&id=idpl
10 < *
11 < *  Software distributed under the License is distributed on an "AS
12 < *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
13 < *  implied. See the License for the specific language governing rights
14 < *  and limitations under the License.
15 < *
16 < *  The Initial Developer of the Original Code is Tony Whyman.
17 < *
18 < *  The Original Code is (C) 2011 Tony Whyman, MWA Software
19 < *  (http://www.mwasoftware.co.uk).
20 < *
21 < *  All Rights Reserved.
22 < *
23 < *  Contributor(s): ______________________________________.
24 < *
25 < *)
26 <
27 < unit ibselectsqleditor;
28 <
29 < {$mode objfpc}{$H+}
30 <
31 < interface
32 <
33 < uses
34 <  Classes, SysUtils, FileUtil,  Forms, Controls, Graphics, Dialogs,
35 <  StdCtrls, ComCtrls, IBSystemTables, IBDatabase;
36 <
37 < type
38 <
39 <  { TIBSelectSQLEditorForm }
40 <
41 <  TIBSelectSQLEditorForm = class(TForm)
42 <    Button1: TButton;
43 <    Button2: TButton;
44 <    GenerateBtn: TButton;
45 <    SelectProcedure: TLabel;
46 <    TestBtn: TButton;
47 <    FieldList: TListBox;
48 <    IBTransaction1: TIBTransaction;
49 <    Label1: TLabel;
50 <    Label16: TLabel;
51 <    Label17: TLabel;
52 <    Label18: TLabel;
53 <    Label2: TLabel;
54 <    Label3: TLabel;
55 <    Label4: TLabel;
56 <    PageControl: TPageControl;
57 <    PrimaryKeyList: TListBox;
58 <    ProcedureNames: TComboBox;
59 <    ProcInputList: TListBox;
60 <    ProcOutputList: TListBox;
61 <    QuoteFields: TCheckBox;
62 <    SQLText: TMemo;
63 <    TableNamesCombo: TComboBox;
64 <    SelectPage: TTabSheet;
65 <    ExecutePage: TTabSheet;
66 <    procedure GenerateBtnClick(Sender: TObject);
67 <    procedure TestBtnClick(Sender: TObject);
68 <    procedure ExecutePageShow(Sender: TObject);
69 <    procedure FieldListDblClick(Sender: TObject);
70 <    procedure FormShow(Sender: TObject);
71 <    procedure PrimaryKeyListDblClick(Sender: TObject);
72 <    procedure ProcedureNamesCloseUp(Sender: TObject);
73 <    procedure SelectPageShow(Sender: TObject);
74 <    procedure TableNamesComboCloseUp(Sender: TObject);
75 <  private
76 <    { private declarations }
77 <    FIBSystemTables: TIBSystemTables;
78 <    FExecuteOnly: boolean;
79 <  public
80 <    { public declarations }
81 <    constructor Create(TheOwner: TComponent); override;
82 <    destructor Destroy; override;
83 <    procedure SetDatabase(Database: TIBDatabase);
84 <  end;
85 <
86 < var
87 <  IBSelectSQLEditorForm: TIBSelectSQLEditorForm;
88 <
89 < function EditSQL(Database: TIBDatabase;  SelectSQL: TStrings): boolean;
90 <
91 < implementation
92 <
93 < uses IBSQL;
94 <
95 < {$R *.lfm}
96 <
97 < function EditSQL(Database: TIBDatabase;  SelectSQL: TStrings): boolean;
98 < begin
99 <  Result := false;
100 <  if assigned(Database) then
101 <    try
102 <      Database.Connected := true;
103 <    except on E: Exception do
104 <      ShowMessage(E.Message)
105 <    end;
106 <
107 <  with TIBSelectSQLEditorForm.Create(Application) do
108 <  try
109 <    SetDatabase(Database);
110 <    SQLText.Lines.Assign(SelectSQL);
111 <    Result := ShowModal = mrOK;
112 <    if Result then
113 <     SelectSQL.Assign(SQLText.Lines)
114 <  finally
115 <    Free
116 <  end;
117 < end;
118 <
119 < { TIBSelectSQLEditorForm }
120 <
121 < procedure TIBSelectSQLEditorForm.FormShow(Sender: TObject);
122 < var IsProcedureName: boolean;
123 < begin
124 <  GenerateBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
125 <  TestBtn.Enabled := (IBTransaction1.DefaultDatabase <> nil) and IBTransaction1.DefaultDatabase.Connected;
126 <  if Trim(SQLText.Text) <> '' then
127 <  begin
128 <    try
129 <      FIBSystemTables.GetStatementType(SQLText.Text,IsProcedureName);
130 <    except  end;
131 <    if IsProcedureName then
132 <      PageControl.ActivePage := ExecutePage
133 <    else
134 <      PageControl.ActivePage := SelectPage;
135 <  end
136 <  else
137 <    PageControl.ActivePage := SelectPage;
138 < end;
139 <
140 < procedure TIBSelectSQLEditorForm.PrimaryKeyListDblClick(Sender: TObject);
141 < begin
142 <  SQLText.SelText := PrimaryKeyList.Items[PrimaryKeyList.ItemIndex];
143 <  SQLText.SetFocus
144 < end;
145 <
146 < procedure TIBSelectSQLEditorForm.ProcedureNamesCloseUp(Sender: TObject);
147 < begin
148 <  FIBSystemTables.GetProcParams(ProcedureNames.Text,FExecuteOnly,ProcInputList.Items,ProcOutputList.Items);
149 <  SelectProcedure.Visible := not FExecuteOnly
150 < end;
151 <
152 < procedure TIBSelectSQLEditorForm.SelectPageShow(Sender: TObject);
153 < var TableName: string;
154 < begin
155 <  TableNamesCombo.Items.Clear;
156 <  FIBSystemTables.GetTableNames(TableNamesCombo.Items);
157 <  if TableNamesCombo.Items.Count > 0 then
158 <  begin
159 <    TableNamesCombo.ItemIndex := 0;
160 <    if Trim(SQLText.Text) <> '' then
161 <    begin
162 <      FIBSystemTables.GetTableAndColumns(SQLText.Text,TableName,nil);
163 <      TableNamesCombo.ItemIndex := TableNamesCombo.Items.IndexOf(TableName)
164 <    end;
165 <    FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items);
166 <    FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
167 <  end;
168 < end;
169 <
170 < procedure TIBSelectSQLEditorForm.FieldListDblClick(Sender: TObject);
171 < begin
172 <  SQLText.SelText := FieldList.Items[FieldList.ItemIndex];
173 <  SQLText.SetFocus
174 < end;
175 <
176 < procedure TIBSelectSQLEditorForm.GenerateBtnClick(Sender: TObject);
177 < var FieldNames: TStrings;
178 < begin
179 <  if PageControl.ActivePage = ExecutePage then
180 <    FIBSystemTables.GenerateExecuteSQL(ProcedureNames.Text,QuoteFields.Checked,FExecuteOnly,
181 <          ProcInputList.Items,ProcOutputList.Items,SQLText.Lines)
182 <  else
183 <  begin
184 <    FieldNames :=  FIBSystemTables.GetFieldNames(FieldList);
185 <    try
186 <      FIBSystemTables.GenerateSelectSQL(TableNamesCombo.Text,QuoteFields.Checked,FieldNames,SQLText.Lines)
187 <    finally
188 <      FieldNames.Free
189 <    end;
190 <  end;
191 < end;
192 <
193 < procedure TIBSelectSQLEditorForm.TestBtnClick(Sender: TObject);
194 < begin
195 <  FIBSystemTables.TestSQL(SQLText.Lines.Text)
196 < end;
197 <
198 < procedure TIBSelectSQLEditorForm.ExecutePageShow(Sender: TObject);
199 < var ProcName: string;
200 <    IsProcedureName: boolean;
201 < begin
202 <  FIBSystemTables.GetProcedureNames(ProcedureNames.Items,true);
203 <  if ProcedureNames.Items.Count > 0 then
204 <  begin
205 <    if (FIBSystemTables.GetStatementType(SQLText.Text,IsProcedureName) = SQLExecProcedure) or IsProcedureName then
206 <    begin
207 <      FIBSystemTables.GetTableAndColumns(SQLText.Text,ProcName,nil);
208 <      ProcedureNames.ItemIndex := ProcedureNames.Items.IndexOf(ProcName)
209 <    end
210 <    else
211 <      ProcedureNames.ItemIndex := 0;
212 <  end;
213 <  FIBSystemTables.GetProcParams(ProcedureNames.Text,FExecuteOnly,ProcInputList.Items,ProcOutputList.Items);
214 <  SelectProcedure.Visible := not FExecuteOnly
215 < end;
216 <
217 < procedure TIBSelectSQLEditorForm.TableNamesComboCloseUp(Sender: TObject);
218 < begin
219 <  FIBSystemTables.GetFieldNames(TableNamesCombo.Text,FieldList.Items);
220 <  FIBSystemTables.GetPrimaryKeys(TableNamesCombo.Text,PrimaryKeyList.Items);
221 < end;
222 <
223 < constructor TIBSelectSQLEditorForm.Create(TheOwner: TComponent);
224 < begin
225 <  inherited Create(TheOwner);
226 <  FIBSystemTables := TIBSystemTables.Create;
227 < end;
228 <
229 < destructor TIBSelectSQLEditorForm.Destroy;
230 < begin
231 <  if assigned(FIBSystemTables) then FIBSystemTables.Free;
232 <  inherited Destroy;
233 < end;
234 <
235 < procedure TIBSelectSQLEditorForm.SetDatabase(Database: TIBDatabase);
236 < begin
237 <  IBTransaction1.DefaultDatabase := Database;
238 <  FIBSystemTables.SelectDatabase(Database,IBTransaction1)
239 < end;
240 <
241 < end.
242 <
1 > (*
2 > *  IBX For Lazarus (Firebird Express)
3 > *
4 > *  The contents of this file are subject to the Initial Developer's
5 > *  Public License Version 1.0 (the "License"); you may not use this
6 > *  file except in compliance with the License. You may obtain a copy
7 > *  of the License here:
8 > *
9 > *    http://www.firebirdsql.org/index.php?op=doc&id=idpl
10 > *
11 > *  Software distributed under the License is distributed on an "AS
12 > *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
13 > *  implied. See the License for the specific language governing rights
14 > *  and limitations under the License.
15 > *
16 > *  The Initial Developer of the Original Code is Tony Whyman.
17 > *
18 > *  The Original Code is (C) 2011 Tony Whyman, MWA Software
19 > *  (http://www.mwasoftware.co.uk).
20 > *
21 > *  All Rights Reserved.
22 > *
23 > *  Contributor(s): ______________________________________.
24 > *
25 > *)
26 >
27 > unit ibselectsqleditor;
28 >
29 > {$mode objfpc}{$H+}
30 >
31 > interface
32 >
33 > uses
34 >  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
35 >  ComCtrls, db, IBSQLEditFrame, IBDatabase, IBCustomDataSet,
36 >  IBLookupComboEditBox, IBDynamicGrid, IB;
37 >
38 > type
39 >
40 >  { TIBSelectSQLEditorForm }
41 >
42 >  TIBSelectSQLEditorForm = class(TForm)
43 >    Button1: TButton;
44 >    Button2: TButton;
45 >    InputProcGrid: TIBDynamicGrid;
46 >    OutputProcGrid: TIBDynamicGrid;
47 >    IncludeSysTables: TCheckBox;
48 >    GenerateBtn: TButton;
49 >    GenerateParams: TCheckBox;
50 >    FieldNamesGrid: TIBDynamicGrid;
51 >    PrimaryKeysGrid: TIBDynamicGrid;
52 >    IBSQLEditFrame1: TIBSQLEditFrame;
53 >    ProcedureNames: TIBLookupComboEditBox;
54 >    SelectProcedure: TLabel;
55 >    SelectSelectAll: TCheckBox;
56 >    SelectTableNames: TIBLookupComboEditBox;
57 >    TestBtn: TButton;
58 >    Label1: TLabel;
59 >    Label16: TLabel;
60 >    Label17: TLabel;
61 >    Label18: TLabel;
62 >    Label2: TLabel;
63 >    Label3: TLabel;
64 >    Label4: TLabel;
65 >    PageControl: TPageControl;
66 >    QuoteFields: TCheckBox;
67 >    SelectPage: TTabSheet;
68 >    ExecutePage: TTabSheet;
69 >    procedure FieldNamesGridDblClick(Sender: TObject);
70 >    procedure GenerateBtnClick(Sender: TObject);
71 >    procedure IncludeSysTablesChange(Sender: TObject);
72 >    procedure InputProcGridDblClick(Sender: TObject);
73 >    procedure OutputProcGridDblClick(Sender: TObject);
74 >    procedure PrimaryKeysGridDblClick(Sender: TObject);
75 >    procedure ProcedureNamesDblClick(Sender: TObject);
76 >    procedure SelectSelectAllChange(Sender: TObject);
77 >    procedure SelectTableNamesDblClick(Sender: TObject);
78 >    procedure TestBtnClick(Sender: TObject);
79 >    procedure ExecutePageShow(Sender: TObject);
80 >    procedure FieldListDblClick(Sender: TObject);
81 >    procedure FormShow(Sender: TObject);
82 >    procedure PrimaryKeyListDblClick(Sender: TObject);
83 >    procedure SelectPageShow(Sender: TObject);
84 >    procedure UserProceduresAfterScroll(DataSet: TDataSet);
85 >  private
86 >    { private declarations }
87 >    procedure HandleUserTablesOpened(Sender: TObject);
88 >  protected
89 >    procedure Loaded; override;
90 >    procedure SetSQLStatementType(aType: TIBSQLStatementTypes); virtual;
91 >  public
92 >    { public declarations }
93 >  end;
94 >
95 >
96 > function EditSQL(DataSet: TIBCustomDataSet;  SelectSQL: TStrings): boolean;
97 >
98 > implementation
99 >
100 > {$R *.lfm}
101 >
102 > function EditSQL(DataSet: TIBCustomDataSet; SelectSQL: TStrings): boolean;
103 > begin
104 >  Result := false;
105 >  if assigned(DataSet) and assigned(DataSet.Database) then
106 >    try
107 >      DataSet.Database.Connected := true;
108 >    except on E: Exception do
109 >      ShowMessage(E.Message)
110 >    end;
111 >
112 >  with TIBSelectSQLEditorForm.Create(Application) do
113 >  try
114 >    if assigned(DataSet) then
115 >    begin
116 >      IBSQLEditFrame1.Database := DataSet.Database;
117 >      GenerateParams.Checked := DataSet.GenerateParamNames;
118 >    end;
119 >    IBSQLEditFrame1.SQLText.Lines.Assign(SelectSQL);
120 >    Result := ShowModal = mrOK;
121 >    if Result then
122 >    begin
123 >     SelectSQL.Assign(IBSQLEditFrame1.SQLText.Lines);
124 >     if assigned(DataSet) then
125 >          DataSet.GenerateParamNames := GenerateParams.Checked
126 >    end;
127 >  finally
128 >    Free
129 >  end;
130 > end;
131 >
132 > { TIBSelectSQLEditorForm }
133 >
134 > procedure TIBSelectSQLEditorForm.FormShow(Sender: TObject);
135 > begin
136 >  GenerateBtn.Enabled := (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected;
137 >  TestBtn.Enabled := (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected;
138 >  PageControl.ActivePage := SelectPage;
139 >  if Trim(IBSQLEditFrame1.SQLText.Text) <> '' then
140 >  begin
141 >    try
142 >      SetSQLStatementType(IBSQLEditFrame1.SyncQueryBuilder);
143 >    except  end;
144 >  end;
145 > end;
146 >
147 > procedure TIBSelectSQLEditorForm.PrimaryKeyListDblClick(Sender: TObject);
148 > begin
149 >  IBSQLEditFrame1.InsertSelectedPrimaryKey;
150 > end;
151 >
152 > procedure TIBSelectSQLEditorForm.SelectPageShow(Sender: TObject);
153 > begin
154 >  if (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected then
155 >    IBSQLEditFrame1.UserTables.Active := true;
156 > end;
157 >
158 > procedure TIBSelectSQLEditorForm.UserProceduresAfterScroll(DataSet: TDataSet);
159 > begin
160 >  SelectProcedure.Visible := DataSet.FieldByName('RDB$PROCEDURE_TYPE').AsInteger = 2;
161 > end;
162 >
163 > procedure TIBSelectSQLEditorForm.FieldListDblClick(Sender: TObject);
164 > begin
165 >  IBSQLEditFrame1.InsertSelectedFieldName;
166 > end;
167 >
168 > procedure TIBSelectSQLEditorForm.GenerateBtnClick(Sender: TObject);
169 > begin
170 >  if PageControl.ActivePage = ExecutePage then
171 >    IBSQLEditFrame1.GenerateExecuteSQL(QuoteFields.Checked)
172 >  else
173 >    IBSQLEditFrame1.GenerateSelectSQL(QuoteFields.Checked);
174 > end;
175 >
176 > procedure TIBSelectSQLEditorForm.FieldNamesGridDblClick(Sender: TObject);
177 > begin
178 >  IBSQLEditFrame1.InsertSelectedFieldName;
179 > end;
180 >
181 > procedure TIBSelectSQLEditorForm.IncludeSysTablesChange(Sender: TObject);
182 > begin
183 >  IBSQLEditFrame1.IncludeSystemTables := IncludeSysTables.Checked;
184 > end;
185 >
186 > procedure TIBSelectSQLEditorForm.InputProcGridDblClick(Sender: TObject);
187 > begin
188 >  IBSQLEditFrame1.InsertSelectedInputParam;
189 > end;
190 >
191 > procedure TIBSelectSQLEditorForm.OutputProcGridDblClick(Sender: TObject);
192 > begin
193 >  IBSQLEditFrame1.InsertSelectedOutputParam;
194 > end;
195 >
196 > procedure TIBSelectSQLEditorForm.PrimaryKeysGridDblClick(Sender: TObject);
197 > begin
198 >  IBSQLEditFrame1.InsertSelectedPrimaryKey;
199 > end;
200 >
201 > procedure TIBSelectSQLEditorForm.ProcedureNamesDblClick(Sender: TObject);
202 > begin
203 >    IBSQLEditFrame1.InsertProcName;
204 > end;
205 >
206 > procedure TIBSelectSQLEditorForm.SelectSelectAllChange(Sender: TObject);
207 > begin
208 >  IBSQLEditFrame1.SelectAllFields(SelectSelectAll.Checked);
209 > end;
210 >
211 > procedure TIBSelectSQLEditorForm.SelectTableNamesDblClick(Sender: TObject);
212 > begin
213 >  IBSQLEditFrame1.InsertTableName;
214 > end;
215 >
216 > procedure TIBSelectSQLEditorForm.TestBtnClick(Sender: TObject);
217 > begin
218 >  IBSQLEditFrame1.TestSQL(GenerateParams.Checked)
219 > end;
220 >
221 > procedure TIBSelectSQLEditorForm.ExecutePageShow(Sender: TObject);
222 > begin
223 >  if (IBSQLEditFrame1.Database <> nil) and IBSQLEditFrame1.Database.Connected then
224 >    IBSQLEditFrame1.UserProcedures.Active := true;
225 > end;
226 >
227 > procedure TIBSelectSQLEditorForm.HandleUserTablesOpened(Sender: TObject);
228 > begin
229 >  SelectSelectAll.Checked := true;
230 >  SelectProcedure.Visible := false;
231 > end;
232 >
233 > procedure TIBSelectSQLEditorForm.Loaded;
234 > begin
235 >  inherited Loaded;
236 >  if IBSQLEditFrame1 <> nil then
237 >  begin
238 >    IBSQLEditFrame1.OnUserTablesOpened := @HandleUserTablesOpened;
239 >    if SelectTableNames <> nil then
240 >      SelectTableNames.ListSource :=  IBSQLEditFrame1.UserTableSource;
241 >    if FieldNamesGrid <> nil then
242 >      FieldNamesGrid.DataSource := IBSQLEditFrame1.FieldsSource;
243 >    if PrimaryKeysGrid <> nil then
244 >      PrimaryKeysGrid.DataSource := IBSQLEditFrame1.PrimaryKeySource;
245 >    if ProcedureNames <> nil then
246 >      ProcedureNames.ListSource := IBSQLEditFrame1.UserProcSource;
247 >    if InputProcGrid <> nil then
248 >      InputProcGrid.DataSource := IBSQLEditFrame1.ProcInputSource;
249 >    if OutputProcGrid <> nil then
250 >      OutputProcGrid.DataSource := IBSQLEditFrame1.ProcOutputSource;
251 >  end;
252 > end;
253 >
254 > procedure TIBSelectSQLEditorForm.SetSQLStatementType(aType: TIBSQLStatementTypes
255 >  );
256 > begin
257 >  case aType of
258 >  SQLExecProcedure:
259 >    PageControl.ActivePage := ExecutePage;
260 >  else
261 >    PageControl.ActivePage := SelectPage;
262 >  end;
263 > end;
264 >
265 > end.

Diff Legend

– Removed lines
+ Added lines
< Changed lines
> Changed lines