TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
Hi all,
I'm upgrading from 6.50 (to 9.7.50) and I notice that TMSMetaData doesn't returns the same set of columns for the otColumns object type. In 6.5 we got the same columns as decribed in the MS doc (https://msdn.microsoft.com/en-us/librar ... .110).aspx) but now I'm far from that.
Does there is a way to fetch the same information as MS document (and as it was in previous version) ?
Regards
Jacques
I'm upgrading from 6.50 (to 9.7.50) and I notice that TMSMetaData doesn't returns the same set of columns for the otColumns object type. In 6.5 we got the same columns as decribed in the MS doc (https://msdn.microsoft.com/en-us/librar ... .110).aspx) but now I'm far from that.
Does there is a way to fetch the same information as MS document (and as it was in previous version) ?
Regards
Jacques
Re: TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
SDAC does not have versions 6.50 and 9.7.50 . The latest SDAC version is 7.3.16 : https://www.devart.com/sdac/revision_history.html . Please specify the full product version where the specified problem is not reproduced, as well as the full product version which you are using now.
Re: TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
Old one:
==============================================
DacVer.inc
//////////////////////////////////////////////////
// Data Access Components
// Copyright © 1998-2008 Devart. All right reserved.
const
DACVersion = '6.50.0.39';
//////////////////////////////////////////////////
// SQL Server Data Access Components
// Copyright © 1998-2008 Devart. All right reserved.
//////////////////////////////////////////////////
const
SdacVersion = '4.50.0.39';
=============================================
New one (the last I beleive)
//////////////////////////////////////////////////
// Data Access Components
// Copyright © 1998-2016 Devart. All right reserved.
//////////////////////////////////////////////////
const
DACVersion = '9.7.29';
//////////////////////////////////////////////////
// SQL Server Data Access Components
// Copyright © 1998-2016 Devart. All right reserved.
//////////////////////////////////////////////////
const
SDACVersion = '7.3.16';
=============================================
Test program:
aMSMetaData := TMSMetaData.Create (Nil);
Try
aMSMetaData.Connection := MSConnection1;
aMSMetaData.ObjectType := otColumns;
aMSMetaData.TableName := 'nd_sysconf';
MSConnection1.Open;
aMSMetaData.Open;
for i := 0 to Pred (aMSMetaData.Fields.Count) do begin
Memo1.Lines.Add (aMSMetaData.Fields.FieldName);
end;
finally
aMSMetaData.Free;
end;
With New version:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
POSITION
DATA_TYPE
DATA_LENGTH
DATA_PRECISION
DATA_SCALE
NULLABLE
DEFAULT_VALUE
With Old version:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION
COLUMN_LCID
COLUMN_COMPFLAGS
COLUMN_SORTID
COLUMN_TDSCOLLATION
IS_COMPUTED
SS_XML_SCHEMACOLLECTION_CATALOGNAME
SS_XML_SCHEMACOLLECTION_SCHEMANAME
SS_XML_SCHEMACOLLECTIONNAME
SS_UDT_CATALOGNAME
SS_UDT_SCHEMANAME
SS_UDT_NAME
SS_UDT_ASSEMBLY_TYPENAME
SS_IS_SPARSE
SS_IS_COLUMN_SET
==============================================
DacVer.inc
//////////////////////////////////////////////////
// Data Access Components
// Copyright © 1998-2008 Devart. All right reserved.
const
DACVersion = '6.50.0.39';
//////////////////////////////////////////////////
// SQL Server Data Access Components
// Copyright © 1998-2008 Devart. All right reserved.
//////////////////////////////////////////////////
const
SdacVersion = '4.50.0.39';
=============================================
New one (the last I beleive)
//////////////////////////////////////////////////
// Data Access Components
// Copyright © 1998-2016 Devart. All right reserved.
//////////////////////////////////////////////////
const
DACVersion = '9.7.29';
//////////////////////////////////////////////////
// SQL Server Data Access Components
// Copyright © 1998-2016 Devart. All right reserved.
//////////////////////////////////////////////////
const
SDACVersion = '7.3.16';
=============================================
Test program:
aMSMetaData := TMSMetaData.Create (Nil);
Try
aMSMetaData.Connection := MSConnection1;
aMSMetaData.ObjectType := otColumns;
aMSMetaData.TableName := 'nd_sysconf';
MSConnection1.Open;
aMSMetaData.Open;
for i := 0 to Pred (aMSMetaData.Fields.Count) do begin
Memo1.Lines.Add (aMSMetaData.Fields.FieldName);
end;
finally
aMSMetaData.Free;
end;
With New version:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
POSITION
DATA_TYPE
DATA_LENGTH
DATA_PRECISION
DATA_SCALE
NULLABLE
DEFAULT_VALUE
With Old version:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION
COLUMN_LCID
COLUMN_COMPFLAGS
COLUMN_SORTID
COLUMN_TDSCOLLATION
IS_COMPUTED
SS_XML_SCHEMACOLLECTION_CATALOGNAME
SS_XML_SCHEMACOLLECTION_SCHEMANAME
SS_XML_SCHEMACOLLECTIONNAME
SS_UDT_CATALOGNAME
SS_UDT_SCHEMANAME
SS_UDT_NAME
SS_UDT_ASSEMBLY_TYPENAME
SS_IS_SPARSE
SS_IS_COLUMN_SET
Re: TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
Any news for me ?
Thanks
Thanks
Re: TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
We reproduced the mentioned behavior and will be investigating it. We will inform you about the results.
Re: TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
Any news for me ?
Currently what I absolutly need is the Delphi data type for that column.
But we loose quite a lot of information with the new format.
Regards
Currently what I absolutly need is the Delphi data type for that column.
But we loose quite a lot of information with the new format.
Regards
Re: TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
We will compose a sample for retrieving metadata about Columns (as in SDAC 4.5 version) and send it to you.
Re: TMSMetaData: upgrade from 6.50 and different results for the otColumns object type
You can get the information about Columns by executing a system stored procedure sp_columns_100_rowset. The sample of such SQL query is:
And the sample of its execution in Delphi:
Below is the list of system stored procedures used by SDAC 4.50.0.39 to get the information about:
Tables - [sys].sp_tables_rowset
Columns - [sys].sp_columns_90_rowset
Procedures - [sys].sp_procedures_rowset2
ProcedureParameters - [sys].sp_procedure_params_90_rowset2
Catalog - [sys].sp_catalogs_rowset2
Indexes - [sys].sp_indexes_90_rowset2
Code: Select all
exec [YOUR_DB_NAME].[sys].sp_columns_90_rowset N'nd_sysconf',NULL,NULL
Code: Select all
aMSStoredProc := TMSStoredProc.Create(nil);
Try
aMSStoredProc.Connection := MSConnection1;
aMSStoredProc.StoredProcName := 'sp_columns_100_rowset';
aMSStoredProc.ParamByName('table_name').AsString := 'nd_sysconf';
MSConnection1.Open;
aMSStoredProc.Open;
for i := 0 to Pred(aMSStoredProc.Fields.Count) do
begin
Memo1.Lines.Add(aMSStoredProc.Fields[i].FieldName);
end;
finally
aMSStoredProc.Free;
end;
Tables - [sys].sp_tables_rowset
Columns - [sys].sp_columns_90_rowset
Procedures - [sys].sp_procedures_rowset2
ProcedureParameters - [sys].sp_procedure_params_90_rowset2
Catalog - [sys].sp_catalogs_rowset2
Indexes - [sys].sp_indexes_90_rowset2