Incorrect field alias name when using static cursor

[gezas]

The alias name is incorrect when using a constant as column value.

To recreate:

CREATE TABLE TEST (ID INT)

Then use the following code:

varQry := TMSQuery.Create(nil);
varQry.Connection := MSConnection1;

varQry.SQL.Text := 'select 1 as column1, id from test';
//Note: the issue only happens with ctStatic cursor type
varQry.CursorType := OLEDBAccess.ctStatic;
varQry.Open;

//Field[0].FieldName became COLUMN1_1.
//This is incorrect. Should be COLUMN1!
if varQry.FindField('COLUMN1') = nil then
ShowMessage('Field is missing!');

The same code works with ADO using static cursor. E.g.:
varADOQry := TADOQuery.Create(nil);
varADOQry.Connection := ADOConnection1;
varADOQry.CursorType := ADODb.ctStatic;
varADOQry.SQL.Text := 'select 1 as column1, id from test';
varADOQry.Open;
//Field[0].FieldName is COLUMN1 correctly
if varQry.FindField('COLUMN1') nil then

We bought SDAC to replace ADO.

This is a serious showstopper issue for us. Changing cursor type is not an option.

Can this be fixed or worked around somehow?

Regards

Geza Szabo
Senior Software Engineer

[Dimon]

This behaviuor occurs because for the static cursor type SQL Server creates a hidden system field with name column1. Therefore, your column1 field is renamed to column1_1.

[gezas]

Thanks for the explanation.

Shouldn't SDAC handle this internally? ADO does.

I mean as a consumer I would expect to have a field name with COLUMN1.

With ADO I have the expected result.

With SDAC I don't

[gezas]

I worked around this issue in our Data Access Layer. If the expected field name is not found then I locate any other unexpected fields in the result set and match them together.

However it would be still nice to have this happening inside SDAC.

[Dimon]

Thank you for information. We have fixed this problem. This fix will be included in the next SDAC build.

[gezas]

Hi Dimon,

excellent support, thank you!