Retrieve data from external database where values exceed 255 characters

Description

Environment
Campaign Classic v8

Issue/Symptoms
If someone wants to retrieve the data from an external Google Big Query database, but the table contains a string column for which some values exceed 255 characters, then this article might help them. For this they use the activity Relational Database Management Systems (RDBMS) and by default the size of the string field is maximum 255 characters, but if it exceeds then this causes a Structured Query Language (SQL) error:

"PGS-220000 PostgreSQL error: ERROR: value too long for type character varying(255) CONTEXT: COPY wkf124720_17_1, line 1, column scol: "aaaaaaaaaaaaaaa...."

When we try to change the field length directly in the workflow Extensible Markup Language (XML) code (manually or via JS code), but this activity does not allow to edit the schema and manage string fields with length greater than 255.

Steps:

Create a workflow and use the activity data loading (RDBMS) to retrieve from an external Google Big Query database values from a string column that exceed 255 characters.

The activity RDBMS has the default size of the string field maximum 255 characters and greater than this causes an SQL error.

Resolution

By default, the maximum length for a string field in Adobe Campaign is 255, this is by design of the product: You will find what are the best practices and more details in Choice of data types in Campaign Classic v7 Documentation. Also, here is a link with useful options to find a solution (if problem arises at the time of enrichment): Adobe Campaign Classic v7 & Campaign v8: Controlling WKF table field length.

If the problem arises from the 1st RDBMS box, you can follow these steps:

  1. Add a SQL Box with this code before the RDBMS Box:

    CREATE OR REPLACE FUNCTION trg_create_table_func()

RETURNS event_trigger

LANGUAGE plpgsql

AS $$

DECLARE

rec record;

BEGIN

FOR rec IN

 SELECT table_name

FROM information_schema.columns

WHERE Lower(table_name) like 'wkf' || `<` %= instance.id %`>`  || '_%' AND Lower(column_name)='sproducturl'

LOOP

EXECUTE format('ALTER TABLE IF EXISTS ONLY %I ALTER COLUMN sProductUrl TYPE VARCHAR(2000);', rec.table_name);

EXECUTE format('ALTER TABLE IF EXISTS ONLY %I ALTER COLUMN sDesignation TYPE VARCHAR(2000);', rec.table_name);

EXECUTE format('ALTER TABLE IF EXISTS ONLY %I ALTER COLUMN sUrlImage158 TYPE VARCHAR(2000);', rec.table_name);

EXECUTE format('ALTER TABLE IF EXISTS ONLY %I ALTER COLUMN sUrlImage358 TYPE VARCHAR(2000);', rec.table_name);

EXECUTE format('ALTER TABLE IF EXISTS ONLY %I ALTER COLUMN sUrlImage528 TYPE VARCHAR(2000);', rec.table_name);

END LOOP;

END



$$;



DROP EVENT TRIGGER IF EXISTS trg_create_table;


CREATE EVENT TRIGGER trg_create_table ON ddl_command_end

WHEN TAG IN ('CREATE TABLE')

EXECUTE PROCEDURE trg_create_table_func();

    This allows to create a trigger on the "create table" event which will trigger a function allowing to increase the length of the desired columns to 2000 for the wkfXXX tables of the workflow.

  2. After the RDBMS box, add another SQL Box to drop the trigger:

    DROP EVENT TRIGGER IF EXISTS trg_create_table;

On this page