How to programatically update the FieldAliases?

Comments

• technisoft

  January 2012 edited 9:51PM

  joost wrote: »

  2) Alternatively, can I ignore the FieldAliases and access the fields of the frxADOQuery in my PascalScript to get the field names directly?

  I would try to do that mapping in the query itself.
  
  One query might read:
  SELECT
  FIELD1 AS COLUMN1,
  FIELD2 AS COLUMN2
  FROM TABLE
  
  another query could be:
  SELECT
  FIELD5 AS COLUMN1,
  FIELD6 AS COLUMN2
  FROM TABLE
  
  etc.
  
• joost

  January 2012 edited 9:51PM

  Hi, thanks but the problem is that the query is a cross-tab/pivot query. That means that, for example, based on one user input I might have fields X, A, B, C, D but based on another input I might only have fields X, A, C, D. Since the FieldAliases (property of TfrxADOQuery) are stuck on the first list (including fieldB) I will get an error when looping through the FieldAliases that include the non-existent field B. Hence my questions to 1) programatically update the FieldAliases, or 2) loop through the actual query fields.
  
  

  technisoft wrote: »

  technisoft wrote: »

  2) Alternatively, can I ignore the FieldAliases and access the fields of the frxADOQuery in my PascalScript to get the field names directly?

  I would try to do that mapping in the query itself.
  
  One query might read:
  SELECT
  FIELD1 AS COLUMN1,
  FIELD2 AS COLUMN2
  FROM TABLE
  
  etc.
• technisoft

  January 2012 edited January 2012

  joost wrote: »

  Hi, thanks but the problem is that the query is a cross-tab/pivot query. That means that, for example, based on one user input I might have fields X, A, B, C, D but based on another input I might only have fields X, A, C, D. Since the FieldAliases (property of TfrxADOQuery) are stuck on the first list (including fieldB) I will get an error when looping through the FieldAliases that include the non-existent field B. Hence my questions to 1) programatically update the FieldAliases, or 2) loop through the actual query fields.

  I have never used FieldAliases so I may be well off with my suggestion.
  My suggested method relies on the query being built dynamically, which means it probably has to happen in the application outside of the report/script. The different number of selected columns could then be handled by casting.
  
  SELECT X AS C1, A AS C2, B AS C3, C AS C4, D AS C5 FROM TABLE
  
  SELECT X AS C1, A AS C2, CAST(0 AS INTEGER) AS C3, C AS C4, D AS C5 FROM TABLE
  
  You should also be able to cast NULL to any data type and strings to VARCHAR(xx)
  
• joost

  January 2012 edited 9:51PM

  Thanks for the suggestions but I finally managed to solve it myself along the lines of refreshing the FieldAliases (list of the TfrxADOQuery).
  All that is needed is to explicitly clear the FieldAliases (TfrxDataset.FieldAliases.Clear) which apparently triggers it to refresh based on the current dataset rather than the one used at design time.
• technisoft

  January 2012 edited 9:51PM

  joost wrote: »

  Thanks for the suggestions but I finally managed to solve it myself along the lines of refreshing the FieldAliases (list of the TfrxADOQuery).
  All that is needed is to explicitly clear the FieldAliases (TfrxDataset.FieldAliases.Clear) which apparently triggers it to refresh based on the current dataset rather than the one used at design time.

  Does that not just create a 1 to 1 mapping of the datafields? IOW, it is like using the original name of the field (without alias) and thus the whole aliasing thing is superfluous?
  
  What am I missing here?
  
• joost

  January 2012 edited 9:51PM

  technisoft wrote: »

  technisoft wrote: »

  Thanks for the suggestions but I finally managed to solve it myself along the lines of refreshing the FieldAliases (list of the TfrxADOQuery).
  All that is needed is to explicitly clear the FieldAliases (TfrxDataset.FieldAliases.Clear) which apparently triggers it to refresh based on the current dataset rather than the one used at design time.

  Does that not just create a 1 to 1 mapping of the datafields? IOW, it is like using the original name of the field (without alias) and thus the whole aliasing thing is superfluous?
  
  What am I missing here?

  
  Indeed it does 1:1 mapping of the datafields but that was just what I needed because I could not find how to access the data field fieldnames directly (hence my original Question 2). When I tried to get the fieldnames (using MyDataSet.GetFieldList() ) I would get whatever was (automatically) stored in FieldAliases and that was not up to date with the actual dataset (my Question 1).