TMSMetaData: upgrade from 6.50 and different results for the otColumns object type

[jgv]

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

[azyk]

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.

[jgv]

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

[jgv]

Any news for me ?
Thanks

[azyk]

We reproduced the mentioned behavior and will be investigating it. We will inform you about the results.

[jgv]

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

[azyk]

We will compose a sample for retrieving metadata about Columns (as in SDAC 4.5 version) and send it to you.

[jgv]

cool

[jgv]

cool but when?

[azyk]

You can get the information about Columns by executing a system stored procedure sp_columns_100_rowset. The sample of such SQL query is:

Code: Select all

exec [YOUR_DB_NAME].[sys].sp_columns_90_rowset N'nd_sysconf',NULL,NULL

And the sample of its execution in Delphi:

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;

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