Solved

TField - determining size and precision

Posted on 2002-05-09
17
919 Views
Last Modified: 2010-04-04
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
Comment
Question by:Stuart_Johnson
  • 6
  • 4
  • 3
  • +3
17 Comments
 
LVL 7

Expert Comment

by:Motaz
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

by:Stuart_Johnson
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

by:Motaz
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

by:Stuart_Johnson
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

by:Motaz
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

by:swift99
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

by:Stuart_Johnson
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

by:kretzschmar
ID: 7000603
you could also look to the fielddefs of the dataset
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Author Comment

by:Stuart_Johnson
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

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

Regards, Geo
0
 

Expert Comment

by:Stormmin66
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

by:swift99
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

by:Stuart_Johnson
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

by:Motaz
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));
    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
 
LVL 17

Accepted Solution

by:
geobul earned 300 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 }

    { 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
 
LVL 6

Author Comment

by:Stuart_Johnson
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

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now