Solved

TField - determining size and precision

Posted on 2002-05-09
17
933 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Virtuailstring tree compare node issue 14 125
Mobile Keyboard covers the display of the TMemo 3 96
oracle global variables 4 76
Magic Software info 18 138
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

828 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