TField - determining size and precision

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
LVL 6
Stuart_JohnsonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
geobulConnect With a Mentor Commented:
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 }

    { Paradox types (Physical) }
    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
    Result := 'not found';
  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
 
MotazCommented:
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
 
Stuart_JohnsonAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MotazCommented:
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
 
Stuart_JohnsonAuthor Commented:
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
 
MotazCommented:
It seems that Physical size, and scale are get by special BDE APIs, which I don't know.

Motaz
0
 
swift99Commented:
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
 
Stuart_JohnsonAuthor Commented:
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
 
kretzschmarCommented:
you could also look to the fielddefs of the dataset
0
 
Stuart_JohnsonAuthor Commented:
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
 
geobulCommented:
Hi,
Take a look at DbiOpenFieldList function and its example in your 'BDE Online Reference' help file.

Regards, Geo
0
 
Stormmin66Commented:
Look at expamples in bde help file, this will give u a starting point.
maybe look at using this api call --> DbiGetFieldTypeDesc
0
 
swift99Commented:
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
 
Stuart_JohnsonAuthor Commented:
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
 
MotazCommented:
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));
    FieldTypeInfo.Add
      ('Field ID Type: ' + IntToStr(FieldTypeRec.iId));
    FieldTypeInfo.Add('Symbolic Name: ' + StrPas(FieldTypeRec.szName));

   FieldTypeInfo.Add('Descriptive Text: ' + StrPas(FieldTypeRec.szText));
    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('Field Required: ' + BoolVal(FieldTypeRec.bRequired));
    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;
  fDbiGetFieldTypeDesc(szPARADOX, 'PDOX 7.0', 'ALPHA', List);
  Memo1.Lines.Text:= List.Text;
  List.Free;
end;

end.

Motaz
0
 
Stuart_JohnsonAuthor Commented:
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
 
geobulCommented:
My pleasure, Stu. Many thanks for the points :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.