Column type metadata request for MSSQL returns no type

[cytrinox]

Hi,

I've just tried to request the column type of an mdColumns metadata request.

I have many different datatypes used in my tables, but every request to self.FMetadata.FieldByName('DATA_TYPE').AsInteger returns 0 (for MSSQL/ADO connections).

On line 1584 in ZDbcAdoMetadata.pas I have found the following lines commented out:

Code: Select all

//  Datatype will come from the adorecordset
//          Result.UpdateShortByName('DATA_TYPE',
//            Ord(ConvertAdoToSqlType(GetShortByName('DATA_TYPE'))));

If I remove the comments and recompile zeos, everything works fine.

Is it intended that the code is commented out? If yes, what is the best generic way to determine the datatype of columns in a Firebird or MSSQL database?

[mdaems]

I don't know why this code was commented out. So uncommenting may be dangerous for existing applications. (eg. What's the result when updating such fields in a dataset and so on.)

But as you say everything works like expected when uncommenting, I'm giving it a try for the 7.X version. Even uncommenting the lines below which should give additional information for blob/clob fields.

SVN rev. 545

Mark

[cytrinox]

I have tried my "fixed" zeoslib in some existing projects. Every attempt to open a query, an integer overflow exception is raised. The exception is raised in ZDbcCache on line 323 in method TZRowAccessor.GetColumnSize.

Code: Select all

    stString:
      Result := ColumnInfo.Precision + 1;

So, uncommenting the code is ok for table metadata, but does not work for resultset metadata.

Is there any other solution?


UPDATE: the exception is only raised if a mssql "text" datatype, better known as blob/memo, is included in the resulset.

[mdaems]

What's the value of ColumnInfo.Precision when this call is made?

Mark

[cytrinox]

ColumnInfo.Precision is for text datatypes 2147483647


UPDATE:

I have found the reason why the exception is raised:

Code: Select all

    FColumnLengths[I] := GetColumnSize(Current);
    FColumnOffsets[I] := FColumnsSize;
    Inc(FColumnsSize, FColumnLengths[I] + 1);

FColumnsSize is the size of all columns. So, adding 2147483647+1 is an integer overflow...

But how can I fix this?

[mdaems]

What's the column size that's determined in TZAdoDatabaseMetadata.UncachedGetColumns ?
This effect is strange, as that code didn't change...

Mark

[cytrinox]

This effect is strange, as that code didn't change...
Mark

The point is, the exception is only raised if I uncomment the code showed in my first post. In the official version, the exception is not raised!


The Metadata Resultset column "COLUMN_SIZE" is initialized with GetIntByName('CHARACTER_MAXIMUM_LENGTH') and this call returns the value 2147483647.

[mdaems]

The Metadata Resultset column "COLUMN_SIZE" is initialized with GetIntByName('CHARACTER_MAXIMUM_LENGTH') and this call returns the value 2147483647.

But this happened before as well... So why didn't this fail before? Maybe because this failing code never was called before?

In the official version, the exception is not raised!

You mean : the 6.6.4 version or the SVN version http://fisheye2.atlassian.com/changelog/zeos/?cs=545 ?

Mark

[cytrinox]

Oops, my fault - sorry

I have only uncommented

Code: Select all

          Result.UpdateShortByName('DATA_TYPE',
            Ord(ConvertAdoToSqlType(GetShortByName('DATA_TYPE'))));

but not the rest of the code which checks the column flags reported by the server.

At my first post, there wasn't any blob fields in my database, so uncommenting the first two lines has worked for me.

Thanks for your help

[mdaems]

I understand it works correctly now? Also for BLOB fields?

[cytrinox]

Yes, it works correctly with Tables containing TEXT columns.