Searching column names of table which may or may not exist in Adobe Campaign

[#1]

Hi guys,

I have been tasked by my senior to find a few column name within a Database that I can't have an access to via mySQL but connected to campaign.

From my understanding, there is a schema called "Data Schema" that contains all schemas inside campaign which means it contains all the table that exist within the database. Problem is, how do I configure list that "data schema" so that it gives me the full XML information that each schema have, and from there I can do advance filter and find if the column name exist or not.

Hope my question is clear.

Regards,

Felix

[#2 · Accepted solution]

Hello Felix,

I have quite often to do similar searches as your request.

Regarding my own Adobe Campaign configuration (AC 6.1.1 On Premise), I can't use directly the Data tab of the Schema xtk:sqlSchema

So I don't think that implementing a dedicated form could do the job correctly.

Hence, I use to manage similar needs with different ways, depending on the exact situation/goal:

1. using Generic Requester (advanced requester) tool, selecting srcSchema entity (Schema Source Entity), then filtering on mData column with "contains" operator.
It is useful when you know the exact pattern to look for, and so you know all the AC XML schemas referencing this pattern.

2. As Ankur mentioned, doing a workflow with a Javascript activity, and either code it with queryDef or sqlSelect etc (much more appropriate for very complex searches).

3. Using the standard admin tool Update database structure (in the global menu Tools/Advanced), intended to synchronise discrepancies between Adobe Campaign XML schemas and the underlying DB tables/column/index definition.
But use it carefully, WITHOUT changing your structure (Synchronize column of main list, or Sync columns of zoom frame (columns of the table or the indexes differences).

4. Similar to 3, but without any risk  change DB structure (if you want to delegate such tool use to non admin people, change the attribute and menu location):

• create a new navtree entry to add the new menu item

• duplicate the xtk:updatedb form (a wizard type form) to new one and change it by deleting the 2 last containers (the SQL queries generated and the execution of the SQL queries), changing the Synchronize column attribute (checkbox) to false value (so no more modification), give appropriate labels and voilà
  (result below: wizard limited to the first 2 pages, no sql script generated, no DB modified, no risks).

One limitation of the 3. and 4 approaches: the query called in SOAP behind the scene, by the wizard form only returns the database object of the Adobe Campaign db owner (user neolane for my instance), not the tables that could have been created with another sql schema in same instance.

Regards
Jean-Serge

[#3]

Hi Felix,

Seems like your seniors are messing around with you

I have never done it, but an idea might help.

You need right a javascript to read all the records from srcSchema and try to get attribute details.

Regards,

Ankur A.