Page 1 of 1
How to obtain the native SQL data type?
Posted: Thu 01 Mar 2012 16:24
by jmuehlenhoff
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.
Posted: Fri 02 Mar 2012 10:12
by AndreyZ
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.
Posted: Fri 02 Mar 2012 10:27
by jmuehlenhoff
Thanks, that's a very useful component!
Posted: Fri 02 Mar 2012 11:41
by AndreyZ
If any other questions come up, please contact us.
Posted: Fri 02 Mar 2012 12:14
by jmuehlenhoff
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?
Posted: Sat 03 Mar 2012 09:15
by AndreyZ
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.