| Subject | Re: [firebird-support] Views |
|---|---|
| Author | Nols Smit |
| Post date | 2004-01-01T20:07:48Z |
I got it working except the Create View statements are missing. For
example, my view named KPI is only showing the following statements:
SELECT codetable.id, codetable.description
FROM codetable
WHERE codetable.codetype_id = 2
Instead of:
CREATE VIEW KPIS (
ID,
DESCRIPTION)
AS
SELECT codetable.id, codetable.description
FROM codetable
WHERE codetable.codetype_id = 2
My program code:
procedure TForm1.btnRunClick(Sender: TObject);
var
MyList: TStringList;
i: integer;
begin
MyList := TStringList.Create;
MyList.Duplicates := dupIgnore;
MyList.Sorted := true;
ListBox1.Items.Clear;
Memo1.Clear;
try
with IBQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('SELECT * FROM RDB$VIEW_RELATIONS');
Open;
First;
While not EOF do
begin
MyList.Add(FieldValues['rdb$view_name']);
Next;
end;
end;
finally
for i := 0 to MyList.Count-1 do
begin
ListBox1.Items.Add(MyList[i]);
end;
lblViewCount.Caption := 'Number of views = ' + IntToStr(MyList.Count);
MyList.Free;
end;
end;
procedure TForm1.ListBox1Click(Sender: TObject);
var
i: integer;
begin
Memo1.Clear;
for i := 0 to ListBox1.Items.Count-1 do
begin
if ListBox1.Selected[i] then
begin
lblViewName.Caption := Trim(ListBox1.Items[i]);
with IBQuery2 do
begin
Close;
SQL.Clear;
SQL.Add('SELECT rdb$view_source FROM rdb$relations');
SQL.Add('WHERE rdb$relation_name = :ViewName');
ParamByName('ViewName').AsString := Trim(ListBox1.Items[i]);
Open;
Memo1.Text := (FieldByName('rdb$view_source').AsVariant);
end;
end;
end;
end;
Regards,
Nols Smit
example, my view named KPI is only showing the following statements:
SELECT codetable.id, codetable.description
FROM codetable
WHERE codetable.codetype_id = 2
Instead of:
CREATE VIEW KPIS (
ID,
DESCRIPTION)
AS
SELECT codetable.id, codetable.description
FROM codetable
WHERE codetable.codetype_id = 2
My program code:
procedure TForm1.btnRunClick(Sender: TObject);
var
MyList: TStringList;
i: integer;
begin
MyList := TStringList.Create;
MyList.Duplicates := dupIgnore;
MyList.Sorted := true;
ListBox1.Items.Clear;
Memo1.Clear;
try
with IBQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('SELECT * FROM RDB$VIEW_RELATIONS');
Open;
First;
While not EOF do
begin
MyList.Add(FieldValues['rdb$view_name']);
Next;
end;
end;
finally
for i := 0 to MyList.Count-1 do
begin
ListBox1.Items.Add(MyList[i]);
end;
lblViewCount.Caption := 'Number of views = ' + IntToStr(MyList.Count);
MyList.Free;
end;
end;
procedure TForm1.ListBox1Click(Sender: TObject);
var
i: integer;
begin
Memo1.Clear;
for i := 0 to ListBox1.Items.Count-1 do
begin
if ListBox1.Selected[i] then
begin
lblViewName.Caption := Trim(ListBox1.Items[i]);
with IBQuery2 do
begin
Close;
SQL.Clear;
SQL.Add('SELECT rdb$view_source FROM rdb$relations');
SQL.Add('WHERE rdb$relation_name = :ViewName');
ParamByName('ViewName').AsString := Trim(ListBox1.Items[i]);
Open;
Memo1.Text := (FieldByName('rdb$view_source').AsVariant);
end;
end;
end;
end;
Regards,
Nols Smit
----- Original Message -----
From: "Martijn Tonies" <m.tonies@...>
To: <firebird-support@yahoogroups.com>
Sent: 31 December, 2003 10:35 AM
Subject: Re: [firebird-support] Views
> Hi,
>
> > I would like to programmatically extract the names of the views in a
> > database and then extract each view's syntax as strings.
> >
> > Any suggestions?
>
> Look at system table RDB$RELATIONS - each row where
> RDB$VIEW_BLR is NOT NULL, is a view. The view source
> is available in RDB$VIEW_SOURCE.
>
> With regards,
>
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server.
> Upscene Productions
> http://www.upscene.com
>
>
>
>
>
> Yahoo! Groups Links
>
> To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to:
> http://docs.yahoo.com/info/terms/
>
>