Service Operations Insight

Tech Tip: Data Manager 4.x - Custom DA for MySQL, column aliases

  • 1.  Tech Tip: Data Manager 4.x - Custom DA for MySQL, column aliases

    Posted Jan 23, 2015 09:49 AM

    A Data Manager Data Adapter using JDBC to connect to MySQL will not return any data if column name aliases are used on returned fields where no operation is performed:

     

    Select

    a.sample_time as timekey, -- BAD

    (a.usr + a.sys) as CPU_Util, -- GOOD

    a.mem_pct as MEM_Util -- BAD

    from source_table a

    where ... ;

     


    Data will likely be returned in the SQL development tool being used to build the query, but when embedded in Data Manager, a Staging job will return 'No Data found for time range ...' errors.


    The reason is because the JDBC connection does not update the column names to the alias if no operation is performed on the source column.


    Solutions: 

    1. Use the source column names where possible:

    Select

    a.sample_time, -- GOOD

    (a.usr + a.sys) as CPU_Util, -- GOOD

    a.mem_pct -- GOOD

     

    from source_table a

    where ... ;

     


     

     

     

    1. Perform an operation that doesn't actually affect the returned value:

    Select

    concat(a.sample_time,'') as timekey, -- GOOD

    (a.usr + a.sys) as CPU_Util, -- GOOD

    (a.mem_pct + 0.0) as MEM_Util -- GOOD

    from source_table a

    where ... ;