How to get field-names of a table ?

[Koenraad Lelong]

Hi,

I have a tsqlQuery with as sql : "select * from mytable". I don't add
the fields at design-time by double-clicking on the query and then
adding the fields. I would like to do this at run-time.
I looked at the properties of the sqlQuery and tried some of the
methods, but I can't find anything that works.
I tried in the create of the datamodule :

tmpField:=TStringField.Create(nil);
tmpField.FieldKind:=fkData;
tmpField.FieldName:='COUNT';
MyQuery.Fields.Add(tmpField);
MyQuery.Open;

Destroy of the datamodule :

MyQuery.Close;
tmpField.Free;

This compiles fine and runs fine until I try to use the COUNT-field,
which does exist in the table.
Closing the application without using the field also creates a segfault.

Any hints how to do what I want ?
Database = firebird, lazarus = 0.9.30

Thanks,

Koenraad Lelong.

--

[Maximiliano Duarte]

When you open the query you object is deleted.
If yuo need only a field name is

Query1.sql.text:= "select * from mytable";
Query1.open;
Name:=Query1.Fields[0].fieldname;



Enviado desde mi BlackBerry

-----Original Message-----
From: Koenraad Lelong <***@de-brouwerij.be>
Date: Thu, 05 Jan 2012 17:20:04
To: <***@lists.lazarus.freepascal.org>
Reply-To: Lazarus mailing list <***@lists.lazarus.freepascal.org>
Subject: [Lazarus] How to get field-names of a table ?

Hi,

I have a tsqlQuery with as sql : "select * from mytable". I don't add
the fields at design-time by double-clicking on the query and then
adding the fields. I would like to do this at run-time.
I looked at the properties of the sqlQuery and tried some of the
methods, but I can't find anything that works.
I tried in the create of the datamodule :

tmpField:=TStringField.Create(nil);
tmpField.FieldKind:=fkData;
tmpField.FieldName:='COUNT';
MyQuery.Fields.Add(tmpField);
MyQuery.Open;

Destroy of the datamodule :

MyQuery.Close;
tmpField.Free;

This compiles fine and runs fine until I try to use the COUNT-field,
which does exist in the table.
Closing the application without using the field also creates a segfault.

Any hints how to do what I want ?
Database = firebird, lazarus = 0.9.30

Thanks,

Koenraad Lelong.

--

[Koenraad Lelong]

Post by Maximiliano Duarte
When you open the query you object is deleted.
If yuo need only a field name is
Query1.sql.text:= "select * from mytable";
Query1.open;
Name:=Query1.Fields[0].fieldname;

Thanks for your response, but it does not work.

The table I use has more than five fields. I manually created one field
(double clicking the sqlQuery and adding one field).
When I run the program, I display Query1.FieldCount : 1.
When I try to display Query1.Fields[1].fieldname ([1], i.e. the second
fieldname) I get an "list index out of bounds" error.
Displaying Query1.Fields[0].fieldname gives the fieldname of the field I
manually created.

So is there a way to what Lazarus can do (adding fields) by using the
properties and/or methods of a TSQLQuery, or another component ?

Thanks for any help,

Koenraad Lelong.

--

[Ludo Brands]

Post by Koenraad Lelong

Post by Maximiliano Duarte
When you open the query you object is deleted.
If yuo need only a field name is
Query1.sql.text:= "select * from mytable";
Query1.open;
Name:=Query1.Fields[0].fieldname;

Thanks for your response, but it does not work.

It works if you don't define your fields yourself.

Post by Koenraad Lelong
The table I use has more than five fields. I manually created
one field
(double clicking the sqlQuery and adding one field).
When I run the program, I display Query1.FieldCount : 1.
When I try to display Query1.Fields[1].fieldname ([1], i.e.
the second
fieldname) I get an "list index out of bounds" error.
Displaying Query1.Fields[0].fieldname gives the fieldname of
the field I
manually created.

If you define one field manually at run-time you have only one field.
Opening the dataset won't add the fields automatically once you defined your
own. I haven't seen this mentioned in fpc/lazarus docs but in Delphi
articles on adding fields at run-time it is stated quite clear: if you want
to add fe. a calculated field at run-time to the existing dataset fkData
fields, you have to define all these fields before adding the new one. If
you have fielddefs for the query you can add the existing fields simply
with:
for i:=0 to MyQuery.FieldDefs.Count-1 do
Field:=MyQuery.FieldDefs[i].CreateField(MyQuery);
If you don't have fielddefs (0 items) and you don' want to add them in the
designer then just add a
SQLQuery1.Active:=true;
SQLQuery1.Active:=false;
before the above loop to auto fill the fielddefs. For this to work, as for
fields, Fielddefs should be an empty array. As soon on element is in
fielddefs, the auto fill won't work.

Now, in your previous mail you created the field with

Post by Koenraad Lelong
tmpField:=TStringField.Create(nil);
tmpField.FieldKind:=fkData;
tmpField.FieldName:='COUNT';
MyQuery.Fields.Add(tmpField);
MyQuery.Open;

This is wrong and fpc 2.7.1 gives even a run-time error saying that dataset
is not set. Replace MyQuery.Fields.Add(tmpField); with
tmpField.Dataset:=MyQuery;.

Ludo


--

[Koenraad Lelong]

Post by Ludo Brands
designer then just add a
SQLQuery1.Active:=true;
before the above loop to auto fill the fielddefs. For this to work, as for
fields, Fielddefs should be an empty array. As soon on element is in
fielddefs, the auto fill won't work.

Hi,

Thanks Ludo, opening the query auto-filled the fielddefs, if there were
no manually created fielddefs.

Now I have to give each field an onGetText event. I was looking for some
documents, but I seem unable to find something. Any help ?
I tried several things but only this compiles, and it even works :
for i:=0 to MyQuery.FieldDefs.Count-1 do
MyQuery.Fields.Fields[i].OnGetText:=@MyQueryDATAGetText;

I created MyQueryDATAGetText by manually creating a field, creating
onGetText by double-clicking on it and putting my code in it.
Afterwards I deleted the field.

Is this OK ? To me it seems a hack.

Thanks.

Regards,

Koenraad Lelong

--

[Ludo Brands]

Post by Ludo Brands

Post by Ludo Brands
designer then just add a
SQLQuery1.Active:=true;
before the above loop to auto fill the fielddefs. For this

to work, as

Post by Ludo Brands
for fields, Fielddefs should be an empty array. As soon on

element is

Post by Ludo Brands
in fielddefs, the auto fill won't work.

Hi,
Thanks Ludo, opening the query auto-filled the fielddefs, if
there were
no manually created fielddefs.

That is from inside lazarus. The code snippet did the same at run time.

Post by Ludo Brands
Now I have to give each field an onGetText event. I was
looking for some
documents, but I seem unable to find something. Any help ?
I tried several things but only this compiles, and it even
works : for i:=0 to MyQuery.FieldDefs.Count-1 do
I created MyQueryDATAGetText by manually creating a field, creating
onGetText by double-clicking on it and putting my code in it.
Afterwards I deleted the field.
Is this OK ? To me it seems a hack.

Normally, when you need to use field events, you would create persistent
fields. With Fielddefs populated, double click on the TSQLQuery and click on
the + sign. You'll see a list of all fielfs in fielddefs. Select all fields
and OK. You have now created persistent fields as opposed to fields created
automatically by opening a dataset. All field settings are now permanent,
including events assigned to them.

BTW, I would replace
for i:=0 to MyQuery.FieldDefs.Count-1 do
With
for i:=0 to MyQuery.Fields.Count-1 do

Ludo

Post by Ludo Brands
Thanks.
Regards,
Koenraad Lelong
--
_______________________________________________
Lazarus mailing list
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

--

[Koenraad Lelong]

Op 09-01-12 17:34, Ludo Brands schreef:
...

Post by Ludo Brands
Normally, when you need to use field events, you would create persistent
fields. With Fielddefs populated, double click on the TSQLQuery and click on
the + sign. You'll see a list of all fielfs in fielddefs. Select all fields
and OK. You have now created persistent fields as opposed to fields created
automatically by opening a dataset. All field settings are now permanent,
including events assigned to them.

That's the point of my application. It should to be able to select
various tables (only one at each time) and then use these tables to
create labels from them, using LazReport.
As I experienced with LazReport, there should be fields defined for the
used tables, otherwise, you can't use the fields when you design a report.
Unfortunately, some of the table's fields contain spaces at the end of
the data. Those spaces should be removed. That's what I do in the event.

Now that I'm writing this, maybe I could do this in an event of the
report. I'll try this.

Thanks for your help,

Koenraad

--

[Maximiliano Duarte]

No if you need an new field, use a calcfield. Add this doble click on tsqlobject, new field, put name, in oncalcfield event fill this with anythinks
Enviado desde mi BlackBerry

-----Original Message-----
From: Koenraad Lelong <***@de-brouwerij.be>
Date: Fri, 06 Jan 2012 15:31:17
To: <***@lists.lazarus.freepascal.org>
Reply-To: Lazarus mailing list <***@lists.lazarus.freepascal.org>
Subject: Re: [Lazarus] How to get field-names of a table ?

Post by Maximiliano Duarte
When you open the query you object is deleted.
If yuo need only a field name is
Query1.sql.text:= "select * from mytable";
Query1.open;
Name:=Query1.Fields[0].fieldname;

Thanks for your response, but it does not work.

The table I use has more than five fields. I manually created one field
(double clicking the sqlQuery and adding one field).
When I run the program, I display Query1.FieldCount : 1.
When I try to display Query1.Fields[1].fieldname ([1], i.e. the second
fieldname) I get an "list index out of bounds" error.
Displaying Query1.Fields[0].fieldname gives the fieldname of the field I
manually created.

So is there a way to what Lazarus can do (adding fields) by using the
properties and/or methods of a TSQLQuery, or another component ?

Thanks for any help,

Koenraad Lelong.

--