How to obtain the native SQL data type?

jmuehlenhoff · Post by **jmuehlenhoff** » Thu 01 Mar 2012 16:24

Hi,

is it possible to determine what the native SQL data type of a field in a TMyTable is?

If yes, is there a generic method in TCustomDADataSet that could be used for this task in ODAC, PgDAC, etc.?

By SQL data type I mean "INTEGER", "CHAR", "TIMESTAMP", etc.

AndreyZ · Post by **AndreyZ** » Fri 02 Mar 2012 10:12

Hello,

To obtain data type names, you can use the TMyMetaData component. Here is an example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  ts: string;
begin
  MyMetaData1.MetaDataKind := 'Columns';
  MyMetaData1.Restrictions.Values['TABLE_SCHEMA'] := 'database';
  MyMetaData1.Restrictions.Values['TABLE_NAME'] := 'table';
  MyMetaData1.Open;
  ts := '';
  while not MyMetaData1.Eof do begin
    ts := ts + 'Field name: ' + MyMetaData1.FieldByName('COLUMN_NAME').AsString +
          '; Field type: ' + MyMetaData1.FieldByName('DATA_TYPE').AsString + #13#10;
    MyMetaData1.Next;
  end;
  if ts  '' then
    ShowMessage(ts);
end;

You can use the same approach in ODAC and PgDAC.

jmuehlenhoff · Post by **jmuehlenhoff** » Fri 02 Mar 2012 10:27

Thanks, that's a very useful component!

AndreyZ · Post by **AndreyZ** » Fri 02 Mar 2012 11:41

If any other questions come up, please contact us.

jmuehlenhoff · Post by **jmuehlenhoff** » Fri 02 Mar 2012 12:14

As a matter of fact I have a few additional questions:

I've tried this with the TDAMetaData component on several differnt databases.

MySQL: I get exactly what I expect, the data_type is the SQL data type name and the data_length is ok.

Firebird: The data_length is fine, but the data_type returns numbers instead of names.

Microsoft SQL Server: The same as Firebird.

PostgreSQL: The same as Firebird, but in addition CHAR fields have a data_length of -1.

I haven't tried Oracle yet.

So the questions are:

1. Why does PgDAC return -1 for data_length on CHAR fields?

2. Why do some databases return numbers for the data_type and how can I map the numbers to SQL type names?

AndreyZ · Post by **AndreyZ** » Sat 03 Mar 2012 09:15

1. We know about this problem. We will fix it in one of the nearest PgDAC builds.
2. Some database servers return numbers for data types, but not their names. We will investigate the possibility of returning data type names in MetaData components for all DAC products.