TFS Integration Tools – What is the new CQQueryTimeDelimiter all about? Q&A-38

  • Article

We introduced another new custom setting for the Rational IBM ClearQuest adapter, which resolves incompatibility of date/time delimiters amongst different databases. … just when I forgot about those Waiting-In-Line-730422adventures they re-emerge.

The following query will run against a SQL backend:

select distinct T1.dbid,T1.headline,T2.login_name,T4.login_name from Defect T1,users T2,users T4 where T1.owner = T2.dbid and T1.submitter = T4.dbid and (T1.dbid <> 0 and ((T4.login_name = 'xyz'))) and (T1.dbid in (SELECT DISTINCT entity_dbid FROM history WHERE action_timestamp > ‘2010-10-28 13:47:11’))

… run it against an Access backend and the smoke appears within the migration pipeline.  If we change the ‘ date/time delimiter to a # character, then we get a similar looking query that runs against Access QC without breaking a sweat:

select distinct T1.dbid,T1.headline,T2.login_name,T4.login_name from Defect T1,users T2,users T4 where T1.owner = T2.dbid and T1.submitter = T4.dbid and (T1.dbid <> 0 and ((T4.login_name = 'xyz'))) and (T1.dbid in (SELECT DISTINCT entity_dbid FROM history WHERE action_timestamp > #2010-10-28 13:47:11#))

To configure the behaviour of the date/time delimiter the latest build (31028.00) allows you to define a new key CQQueryTimeDelimiter, which specifies which delimiter to use. Before you change the default delimiter you should consult your database administrative guide and determine what the correct date/time delimiter is for your database.

Extract from an example configuration file, that changes the default delimiter to a # character. See line 11.

    1: <MigrationSource InternalUniqueId="BB2BD2C6-92B5-4817-AB51-A087B6532F0D"
    2:                  FriendlyName="CQ source" ServerIdentifier="Any string" 
    3:                  ServerUrl="Database Set" 
    4:                  SourceIdentifier="User DB Name" 
    5:                  ProviderReferenceName="D9637401-7385-4643-9C64-31585D77ED16"> 
    6:   <CustomSettings> 
    7:     <CustomSetting SettingKey="LoginCredentialConfigType" 
    8:                    SettingValue="UseTextUsernamePasswordPairInConfig" /> 
    9:     <CustomSetting SettingKey="UserName" SettingValue="userid" /> 
   10:     <CustomSetting SettingKey="Password" SettingValue="password" /> 
   11:     <CustomSetting SettingKey="CQQueryTimeDelimiter" SettingValue="#" />
   12:     <CustomSetting SettingKey="AdminUserName" SettingValue="" /> 
   13:     <CustomSetting SettingKey="AdminPassword" SettingValue="" /> 
   14:   </CustomSettings> 
   15: </MigrationSource>

Relevant to TFS Integration Tools build 31028.00 or higher.