Incorrect field alias name when using static cursor

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
gezas
Posts: 18
Joined: Thu 15 Apr 2010 01:24

Incorrect field alias name when using static cursor

Post by gezas » Fri 30 Apr 2010 02:45

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 30 Apr 2010 11:55

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
Posts: 18
Joined: Thu 15 Apr 2010 01:24

Post by gezas » Fri 30 Apr 2010 20:24

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
Posts: 18
Joined: Thu 15 Apr 2010 01:24

Post by gezas » Sun 02 May 2010 21:44

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
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 06 May 2010 13:13

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

gezas
Posts: 18
Joined: Thu 15 Apr 2010 01:24

Post by gezas » Sun 09 May 2010 20:52

Hi Dimon,

excellent support, thank you!

Post Reply