please help newbie with ZQuery

[ChaosBiker]

gents,

I need some help understanding ZQuery,

goal:
Query: sp_helpdb sgmsdb

from ZQuery itself (so not via a dbgrid) I am trying to:
1) from the sgmsdb database itself get size and maxsize
2) from the associated transaction log get size and max size

if I peform this query in SQL itself
I get a nice table,
now I am trying to understand how ZQuery stores this, so I can grab this dricetly fro, ZQuery...

can you please assist???

[mdaems]

ZQuery returns a TDataset, if you give it a nice sql statement you database library can pass on to the database server. And you need to Open the ZQuery instead of calling ExecuteSQL.

If 'sp_helpdb sgmsdb' is for your database a query as good as 'select * from x' returning only 1 dataset (so not 10 small one-row-datasets) you could write something like this:

Code: Select all

With ZQuery1 do
  begin
   SQL.Clear;
   SQL.Append('sp_helpdb sgmsdb');
   Open;
   While not EOF do
     begin
       ShowMessage(fields[0].asString);
       Next;
     end;
   Close;
  end;

When your procedure returns multiple datasets you'll have to fall back on the dbc layer immediately instead of using the ZQuery component. That's somehing I can't demonstrate, however.

Mark

[ChaosBiker]

how is fields defined?
coz I seem to only find values from the first row?

(run sphelp_db database on your own sql)

I am also looking for the collumn name

say results look smthn like this

[ ][name ][size ][max size]
1 sgmsdb 500MB unlimites
2 sgmsdb_log 100 MB 2 GB

as I am unsure what collumn I am looking for,
I wanna first find the collumn max size
then find the name, then find the size matching to that name

[mdaems]

Next doesn't find a next record? That might mean there's more than one recordset returned from the stored proc.
Unfortunatly : this proc is not available for mysql. So I can't try.

BTW look for documentation at the TDataset and TField descriptions for your compiler. ZQuery is just using these to do the job.

Mark

[ChaosBiker]

i tried to do so,
unfortunatley Delphi help is not very helpfull with these specific componetns..

[mdaems]

Try TQuery that's also a descendant.

[ChaosBiker]

although your hint was vague,

it helped me out with some desk research

here's what I wrote:

procedure TSNWLSQLClientMainForm.GetDBInfo;

var i: Integer;
TempStringList: TStringList;
NameCollumn, SizeCollumn, MaxSizeCollumn, StatusCollumn: Integer;
DBStatus: ShortString;
ExitRepeat: Boolean;

begin
TempStringList:=TStringList.Create;
TempStringList.Add('sp_helpdb '+SQLClientDBEB.Text);
ZQuery.SQL:=TempStringList;
ZQuery.Open;
For i:=0 to(ZQuery.FieldCount-1)
do
If ZQuery.FieldDefs.Name = 'name'
then NameCollumn:=i
else
If ZQuery.FieldDefs.Name = 'size'
then SizeCollumn:=i
else
If ZQuery.FieldDefs.Name = 'maxsize'
then MaxSizeCollumn:=i;
If ZQuery.FieldByName(ZQuery.Fields[NameCollumn].FieldName).asString = SQLClientDBEB.Text
then
begin
SQLClientDBSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[SizeCollumn].FieldName).asString;
SQLClientDBMaxSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[MaxSizeCollumn].FieldName).asString;
end
else
If ZQuery.FieldByName(ZQuery.Fields[NameCollumn].FieldName).asString = SQLClientDBEB.Text +'_log'
then
begin
SQLClientLogSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[SizeCollumn].FieldName).asString;
SQLClientLogMaxSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[MaxSizeCollumn].FieldName).asString;
end;
ZQuery.Next;
If ZQuery.FieldByName(ZQuery.Fields[NameCollumn].FieldName).asString = SQLClientDBEB.Text
then
begin
SQLClientDBSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[SizeCollumn].FieldName).asString;
SQLClientDBMaxSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[MaxSizeCollumn].FieldName).asString;
end
else
If ZQuery.FieldByName(ZQuery.Fields[NameCollumn].FieldName).asString = SQLClientDBEB.Text +'_log'
then
begin
SQLClientLogSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[SizeCollumn].FieldName).asString;
SQLClientLogMaxSizeEB.Text:=ZQuery.FieldByName(ZQuery.Fields[MaxSizeCollumn].FieldName).asString;
end;
ZQuery.Close;
TempStringList.Clear;
TempStringList.Add('sp_helpdb');
ZQuery.SQL:=TempStringList;
ZQuery.Open;
For i:=0 to(ZQuery.FieldCount-1)
do
If ZQuery.FieldDefs.Name = 'name'
then NameCollumn:=i
else
If ZQuery.FieldDefs.Name = 'status'
then StatusCollumn:=i;
While NOT ZQuery.Eof
do
begin
If ZQuery.FieldByName(ZQuery.Fields[NameCollumn].FieldName).asString = SQLClientDBEB.Text
then
begin
DBStatus:=ZQuery.FieldByName(ZQuery.Fields[StatusCollumn].FieldName).asString;
Break;
end;
ZQuery.Next;
end;
ZQuery.Close;
MessageDlg(DBStatus, mtWarning,[mbOk], 0);
TempStringList.Free;
end;

[ChaosBiker]

getting there,
step by step

[mdaems]

Why not simply
If ZQuery.FieldByName('name').asString = SQLClientDBEB.Text then ...

So you dan't have to loop through the fielddefs.

Mark