Solved

TField - determining size and precision

Posted on 2002-05-09
17
929 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

10 Experts available now in Live!

Get 1:1 Help Now