Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# TField - determining size and precision

Posted on 2002-05-09
Medium Priority
1,007 Views
Hi,

I need to display the table properties for a given table (of any DB type) and display the field sizes for each field in the table.

How can I firstly determine the actual size of the field.  Secondly, if the field is of floating point type, how do I know it's precision?

If I created a table like this:

create table TEST (
Field1 Char(40),
Field2 Numeric(15,4),
Field3 Integer);

I want to see something that looks like this:

Table: Test

Field1  Character  40
Field2  Floating   15 4
Field3  Integer    4*

*I'm only using four because that's the actual memory size required for an integer type.  If there is a true size for the field it would be great, otherwise 4 is fine.

Any help would be greatly appreciated.

Cheers!

Stu
0
Question by:Stuart_Johnson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 4
• 3
• +3

LVL 7

Expert Comment

ID: 6998497
you can use Table component or Query,
after opening the table or query you will find this information of Fields object:

Table1.Fields[0].FieldName  returns first field name
Table1.Fields[0].DataType   returns first field data type
Table1.Fields[0].DataSize   returns first field size

Fields[1], for seocnd; etc
if you want to use field names instead of it's index then you can use:

Table1.FieldByName('Name').DataSize

Motaz
0

LVL 6

Author Comment

ID: 6998550
Hi Motaz,

Unfortunately, these only provide the memory requirements for the fields, not the physical size of the field.  That's what I was stressing about with the create table script example.

Stu.
0

LVL 7

Expert Comment

ID: 6998565
What did you mean by physical size,
if you mean the actuall length of data, then this only can be used with strings data fields:

Length(Table1.Fields[1].AsString);

This is the actual data size
Motaz
0

LVL 6

Author Comment

ID: 6998638
If you go into SQL Explorer (under the Delphi menu) and then open an alias.  If you drill down to a table then a field, on the right hand pane you'll get a Definition tab.  The contains:

Field order
Field type
Field size
Field scale
Physical length

If I have a NUMERIC field with 15 digitals accurate to 4 decimal places, the definition will look like this:

Field order: 1
Field type: NUMERIC
Field size: 15
Field scale: 4
Phsyical length: 20

This is the sort of info I want to get in my app :)

I know it's possible to do because one of the guys who used to work where I did wrote a program in Delphi which does just this without using any extra controls.  The problem is, he wrote it in his own time and he won't give us any source code for it (he left about a year ago).

If you'd like a screen dump, I'll stick it on my webpage.

Stu
0

LVL 7

Expert Comment

ID: 6999523
It seems that Physical size, and scale are get by special BDE APIs, which I don't know.

Motaz
0

LVL 6

Expert Comment

ID: 6999874
The answer you need will vary on the database engine behind the app.  If you are using DB2 versus Oracle versus Interbase, each will have a different way of retrieving this information.

It is stored in system tables that differs between platforms, but can be retrieved via SQL in all of them.  You will need a DBA's guide to your DBMS.
0

LVL 6

Author Comment

ID: 7000072
Hi Swift99,

Do you think this is how Borland do it with SQL Explorer?  I'm really surprised that this is the case to be honest.  As I mentioned previously, the guy who worked here previously has done it and we've used it against Oracle, SQL Server, IB, AS/400, Access and Paradox/dBase without a hitch.

I'm going to up the points from 50 to 300 to try and get a little more interest in this questions because there must be a way of doing it other than using queries.  I accept that each DB does have a system table containing table definitions, but I can't believe that this is the only way of doing it.

I'll start digging a bit harder too.

Motaz.  Can you give me a clue with the API?  I'm quite happy to take just a hint and I'll do all the research.  I just don't know where to start looking :)

Cheers,

Stu
0

LVL 27

Expert Comment

ID: 7000603
you could also look to the fielddefs of the dataset
0

LVL 6

Author Comment

ID: 7000609
Hi Meikl,

I've tried that, but the Precision property always returns zero even when SQL Explorer (and my script) says that the floating point type field has a precision of 4 decimal places.

Can you provide a quick example that would allow me to do this using FieldDefs?

Stu
0

LVL 17

Expert Comment

ID: 7001138
Hi,
Take a look at DbiOpenFieldList function and its example in your 'BDE Online Reference' help file.

Regards, Geo
0

Expert Comment

ID: 7001945
Look at expamples in bde help file, this will give u a starting point.
maybe look at using this api call --> DbiGetFieldTypeDesc
0

LVL 6

Expert Comment

ID: 7001956
Most of that information is not important to the BDE, so I'm not sure it even retrieves it.  Stormmin66 may be on the right track.
0

LVL 6

Author Comment

ID: 7016128
Stormmin66,

This sounds exactly what I want.  I can't find much info on any Dbi functions - do you have anywhere I could start looking for more details?  I've figured out how to open the database and the table, but I can't work out how to use the DbiGetFieldTypeDesc function.

Thanks for you help,

Stu
0

LVL 7

Expert Comment

ID: 7017129
I found an example in help about that API function
- Drop a button, and memo
- Drop a table component and select Paradox table and open it, inspite of that below code has no thing to do with this table, but at least opening this table initializes BDE, which I get error about BDE not initialized when I don't open a table.

I'm not sure if this is exactly what did you need or not, because this an abstract information about field type, not about certain field in certain table:

the code in unit:

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, BDE, dbTables, Db;

type
TForm1 = class(TForm)
Button1: TButton;
Memo1: TMemo;
Table1: TTable;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{\$R *.DFM}

procedure  fDbiGetFieldTypeDesc(DriverType, TableType, FieldType: PChar;
var  FieldTypeInfo: TStringList);

function  BoolVal(InBool: Boolean):  string ;
begin
if  InBool  then  Result:= 'True'
else  Result:= 'False';
end ;

var
FieldTypeRec: FLDType;
begin
Check(DbiGetFieldTypeDesc(DriverType, TableType, FieldType,  FieldTypeRec));
('Field ID Type: ' + IntToStr(FieldTypeRec.iId));

FieldTypeInfo.Add('Physical / Native Type: ' + IntToStr  (FieldTypeRec.iPhyType));
FieldTypeInfo.Add('Default Translated Type: ' + IntToStr  (FieldTypeRec.iXltType));
FieldTypeInfo.Add('Default Translated Subtype: ' + IntToStr  (FieldTypeRec.iXltSubType));
FieldTypeInfo.Add('Maximum Units Allowed (1): ' + IntToStr  (FieldTypeRec.iMaxUnits1));
FieldTypeInfo.Add('Maximum Units Allowed (2): ' + IntToStr  (FieldTypeRec.iMaxUnits2));

FieldTypeInfo.Add('Physical Size: ' + IntToStr (FieldTypeRec.iPhySize));
FieldTypeInfo.Add('Supports user-specified default: ' +  BoolVal(FieldTypeRec.bDefaultVal));
FieldTypeInfo.Add('Supports Min Val constraint: ' +  BoolVal(FieldTypeRec.bMinVal));
FieldTypeInfo.Add('Supports Max Val constraint: ' +  BoolVal(FieldTypeRec.bMaxVal));
FieldTypeInfo.Add('Supports Referential Integerity: ' +  BoolVal(FieldTypeRec.bRefIntegrity));

FieldTypeInfo.Add('Supports Other Checks: ' +  BoolVal(FieldTypeRec.bOtherChecks));
FieldTypeInfo.Add('Can Be Keyed: ' + BoolVal(FieldTypeRec.bKeyed));
FieldTypeInfo.Add('Multiple Fields of this Type: ' +  BoolVal(FieldTypeRec.bMultiplePerTable));
FieldTypeInfo.Add('Minimum Units Required (1): ' + IntToStr  (FieldTypeRec.iMinUnits1));
FieldTypeInfo.Add('Minimum Units Required (2): ' + IntToStr  (FieldTypeRec.iMinUnits2));
FieldTypeInfo.Add('Field Type Can be Created: ' +  BoolVal(FieldTypeRec.bCreateable));

end ;

procedure TForm1.Button1Click(Sender: TObject);
var
List: TStringList;
begin
List:= TStringList.Create;
Memo1.Lines.Text:= List.Text;
List.Free;
end;

end.

Motaz
0

LVL 17

Accepted Solution

geobul earned 1200 total points
ID: 7017989
Here is how to use DbiOpenFieldList function:
---
type
TForm1 = class(TForm)
Button1: TButton;
Table1: TTable;
Memo1: TMemo;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{\$R *.DFM}

uses BDE;

procedure fDbiOpenFieldList(Table: TTable; Physical: Boolean; List: TStrings);

function BDEFieldIntToStr(FieldType: Word): string;

begin
case FieldType of
fldUNKNOWN: result := 'unknown';
fldZSTRING: result := 'string';               { Null terminated string }
fldDATE: result := 'date';                    { Date     (32 bit) }
fldBLOB: result := 'BLOb';                    { Blob }
fldBOOL: result := 'boolean';                 { Boolean  (16 bit) }
fldINT16: result := 'integer';                { 16 bit signed number }
fldINT32: result := 'long integer';           { 32 bit signed number }

fldFLOAT: result := 'float';                  { 64 bit floating point }
fldBCD: result := 'BCD';                      { BCD }
fldBYTES: result := 'bytes';                  { Fixed number of bytes }
fldTIME: result := 'time';                    { Time        (32 bit) }
fldTIMESTAMP: result := 'timestamp';          { Time-stamp  (64 bit) }
fldUINT16: result := 'unsigned int';          { Unsigned 16 bit integer }
fldUINT32: result := 'unsigned long int';     { Unsigned 32 bit integer }

fldFLOATIEEE: result := 'float IEEE';         { 80-bit IEEE float }
fldVARBYTES: result := 'varbytes';            { Length prefixed var bytes }
fldLOCKINFO: result := 'lockinfo';            { Look for LOCKINFO typedef }
fldCURSOR: result := 'Oracle cursor';         { For Oracle Cursor type }

fldPDXCHAR: result := 'alpha';                { Alpha    (string) }
fldPDXNUM: result := 'numeric';               { Numeric }

fldPDXMONEY: result := 'money';               { Money }
fldPDXDATE: result := 'date';                 { Date }
fldPDXSHORT: result := 'smallint';            { Short }
fldPDXMEMO: result := 'Memo BLOb';            { Text Memo       (blob) }
fldPDXBINARYBLOB: result := 'Binary BLOb';    { Binary data     (blob) }
fldPDXFMTMEMO: result := 'formatted BLOb';    { Formatted text  (blob) }
fldPDXOLEBLOB: result := 'OLE BLOb';          { OLE object      (blob) }

fldPDXGRAPHIC: result := 'Graphic BLOb';      { Graphics object (blob) }
fldPDXLONG: result := 'long integer';         { Long }
fldPDXTIME: result := 'time';                 { Time }
fldPDXDATETIME: result := 'date time';        { Time Stamp }
fldPDXBOOL: result := 'boolean';              { Logical }
fldPDXAUTOINC: result := 'auto increment';    { Auto increment (long) }
fldPDXBYTES: result := 'bytes';               { Fixed number of bytes }

fldPDXBCD: result := 'BCD';                   { BCD (32 digits) }

{ xBASE types (Physical) }
fldDBCHAR: result := 'character';             { Char string }
fldDBNUM: result := 'number';                 { Number }
fldDBMEMO: result := 'Memo BLOb';             { Memo          (blob) }
fldDBBOOL: result := 'logical';               { Logical }
fldDBDATE: result := 'date';                  { Date }
fldDBFLOAT: result := 'float';                { Float }

fldDBLOCK: result := 'LOCKINFO';              { Logical type is LOCKINFO }
fldDBOLEBLOB: result := 'OLE BLOb';           { OLE object    (blob) }
fldDBBINARY: result := 'Binary BLOb';         { Binary data   (blob) }
fldDBBYTES: result := 'bytes';                { Only for TEMPORARY tables }
fldDBLONG: result := 'long integer';          { Long (Integer) }
fldDBDATETIME: result := 'date time';         { Time Stamp }
fldDBDOUBLE: result := 'double';              { Double }

fldDBAUTOINC: result := 'aut increment';      { Auto increment (long) }
else
end;
end;

var
hFieldCur: hDBICur;
rslt: DBIResult;
Field: FLDDesc;
begin
List.Clear;
Check(DbiOpenFieldList(Table.DBHandle, PChar(Table.TableName), nil,
Physical, hFieldCur));
repeat
rslt := DbiGetNextRecord(hFieldCur, dbiNOLOCK, @Field, nil);
if (rslt = DBIERR_NONE) then begin

List.Add(Format('%s %s %d %d', [Field.szName, BDEFieldIntToStr(Field.iFldType), Field.iLen, Field.iUnits2]));
end;
until (rslt <> DBIERR_NONE);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
Table1.Open;
fDbiOpenFieldList(Table1, True, Memo1.Lines);
Table1.Close;
end;

end.
---
Regards, Geo
0

LVL 6

Author Comment

ID: 7018024
Hi geobul,

Thank you!  This worked perfectly!  I have every detail I need within the FLDDesc record.  With just a simple change, it works perfectly for what I asked!

Thank you very much, and thank you to everyone else who contributed.

Stu
0

LVL 17

Expert Comment

ID: 7018103
My pleasure, Stu. Many thanks for the points :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the inâ€¦
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi projâ€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper cornâ€¦
###### Suggested Courses
Course of the Month9 days, 4 hours left to enroll