Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-64

Unica: Connection with MySQL template

    Details

    • Type: Workaround
    • Status: Published
    • Affects Version/s: EXASolution 4.2.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Solution:
      Hide

      Problem

      IBM Unica Campaign with MySQL template

      [EXASOL][EXASolution driver]syntax error, unexpected '<' [line x, column y] of [EXASOL][EXASolution driver]function or script ADDDATE not found [line x, column y]

       Unica generates SQL statements that are not identically supported by EXASolution.

      Solution

      Create a pre-processor script that adjusts the SQL statements to appropriate ones.

      CREATE OR REPLACE LUA SCRIPT DATAMART.PPSCRIPT_MYSQL RETURNS ROWCOUNT AS function unicaproc(sqltext)
        local tokens = sqlparsing.tokenize(sqltext)
        for i=1,#tokens do
          if string.upper(tokens[i]) == 'ADDDATE' then
                 tokens[i] = 'ADD_DAYS'
          end
          if tokens[i] == '<' and string.upper(tokens[i+1]) == 'CURRENT_DATE' then
            tokens[i] = ''
          end
          if string.upper(tokens[i]) == 'UNSUPPORTED' and tokens[i+1] == '>' then
            tokens[i] = ''
            tokens[i+1] = ''
          end
        end
        return table.concat(tokens)
      end
      /
      

      Activate this preprocessor script for all Unica session or system wide:

      ALTER [SESSION|SYSTEM] SET sql_preprocessor_script=DATAMART.PPSCRIPT_MYSQL;
      
      Show
      Problem IBM Unica Campaign with MySQL template [EXASOL] [EXASolution driver] syntax error, unexpected '<' [line x, column y]  of  [EXASOL] [EXASolution driver] function or script ADDDATE not found [line x, column y]  Unica generates SQL statements that are not identically supported by EXASolution. Solution Create a pre-processor script that adjusts the SQL statements to appropriate ones. CREATE OR REPLACE LUA SCRIPT DATAMART.PPSCRIPT_MYSQL RETURNS ROWCOUNT AS function unicaproc(sqltext) local tokens = sqlparsing.tokenize(sqltext) for i=1,#tokens do if string.upper(tokens[i]) == 'ADDDATE' then tokens[i] = 'ADD_DAYS' end if tokens[i] == '<' and string.upper(tokens[i+1]) == 'CURRENT_DATE' then tokens[i] = '' end if string.upper(tokens[i]) == 'UNSUPPORTED' and tokens[i+1] == '>' then tokens[i] = '' tokens[i+1] = '' end end return table.concat(tokens) end / Activate this preprocessor script for all Unica session or system wide: ALTER [SESSION|SYSTEM] SET sql_preprocessor_script=DATAMART.PPSCRIPT_MYSQL;
    • Category 1:
      3rd Party Tools
    • Category 2:
      SQL - SQL preprocessor

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: