Creating a WebSphere Commerce E-Marketing Spot w/SQL (Image Assets)

Creating a WebSphere Commerce E-Marketing Spot w/SQL (Image Assets)

The following SQL can be used to create a new e-marketing spot that is used to display an image asset. The image must exist for this SQL to work; however, once you can reference it, this SQL can be used to create the associated asset, content, and e-marketing spot records. If you’re looking for the SQL used to create an e-marketing spot that can be used to display text and text-based creative, you can reference the following article:

Creating a WebSphere Commerce e-Marketing Spot w/SQL (Text Content)

The following tables are used or referenced when creating an e-marketing spot with the SQL shown below:

  • STOREENT : Each row of this table represents a StoreEntity. A StoreEntity is an abstract superclass that can represent either a Store or a StoreGroup.
  • ATCHTGT : This table holds information about the target, which is used to hold a collection of assets, for use as an attachment target.
  • ATCHTGTDSC : This table holds the description of an attachment target.
  • LANGUAGE : Each row of this table represents a language. WebSphere Commerce supports multiple languages and is translated into ten languages by default. Using the predefined ISO codes users can add other supported languages.
  • COLLATERAL : This table contains data describing the advertisements used by marketing campaigns.
  • COLLTYPE : This table defines all possible types of Collateral. Examples of Collateral types are image, flash and text.
  • ATCHREL : This table holds the attachment relation between a business object and an attachment target.
  • ATCHOBJTYP : This table holds the information about the types of business objects that are configured to have attachments. For example, attachments can be associated to CATALOG, CATGROUP, and CATENTRY business objects.
  • ATCHRLUS : This table holds usage information for attachment relations. For example, an attachment can be a warranty document related to a product. In this case the usage is ‘warranty’.
  • ATCHAST : This table holds the metadata for the attachment asset in the attachment target.
  • EMSPOT : This table contains registered e-Marketing Spots.
  • DMEMSPOTDEF : The default content to display in an e-Marketing Spot.

I have generalized the following SQL statements so you’ll need to replace the following items in the SQL match up with your specific needs:

  • {STORE_IDENTIFIER} : The STOREENT.IDENTIFIER column for the store you wish to associate the content/e-marketing spot with.
  • {LOCALE_NAME} : The LANGUAGE.LOCALENAME column for the language you wish to use. (e.g. ‘en_US’)
  • {COLLATERAL_NAME} : The desired name for the collateral to be created.
  • {ATTACHMENT_NAME} : The desired name for the attachment to be created.
  • {ATTACHMENT_DESCRIPTION} : The desired description for the attachment to be created.
  • {COLLATERAL_URL} : The desired URL to be used when the attachment is clicked.
  • {ESPOT_NAME} : The desired e-marketing spot name.
  • {ESPOT_DESC} : The desired e-marketing spot description.
  • {ATTACHMENT_ASSET_PATH} : The full path from the store directory to the attachment asset.
  • {ATTACHMENT_MIME_TYPE} : The mime type of the attachment asset.

A few notes regarding the SQL shown below. I always start database inserts, not handled through the WebSphere Commerce framework beginning at the number ‘-10001’. The SQL will automatically generate the appropriate primary key identifier for the following tables starting at -10001 and decrementing each subsequent insert by 1.

INSERT INTO ATCHTGT (ATCHTGT_ID, STOREENT_ID, MEMBER_ID, IDENTIFIER) VALUES (
    (SELECT CASE WHEN MIN(ATCHTGT_ID) > -10001 THEN -10001 ELSE MIN(ATCHTGT_ID) - 1 END FROM ATCHTGT),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    (SELECT MEMBER_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    '{COLLATERAL_NAME}'
);

INSERT INTO ATCHTGTDSC (ATCHTGTDSC_ID, ATCHTGT_ID, LANGUAGE_ID, NAME, SHORTDESCRIPTION) VALUES (
    (SELECT CASE WHEN MIN(ATCHTGTDSC_ID) > -10001 THEN -10001 ELSE MIN(ATCHTGTDSC_ID) - 1 END FROM ATCHTGTDSC),
    (SELECT ATCHTGT_ID FROM ATCHTGT WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND IDENTIFIER = '{COLLATERAL_NAME}'),
    (SELECT LANGUAGE_ID FROM LANGUAGE WHERE LOCALENAME = '{LOCALE_NAME}'),
    '{ATTACHMENT_NAME}',
    '{ATTACHMENT_DESCRIPTION}'
);

INSERT INTO COLLATERAL (COLLATERAL_ID, STOREENT_ID, COLLTYPE_ID, NAME, URL) VALUES (
    (SELECT CASE WHEN MIN(COLLATERAL_ID) > -10001 THEN -10001 ELSE MIN(COLLATERAL_ID) - 1 END FROM COLLATERAL),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    (SELECT COLLTYPE_ID FROM COLLTYPE WHERE NAME = 'File'),
    '{COLLATERAL_NAME}',
    '{COLLATERAL_URL}'
);

INSERT INTO ATCHREL (ATCHREL_ID, OBJECT_ID, ATCHOBJTYP_ID, ATCHTGT_ID, ATCHRLUS_ID) VALUES (
    (SELECT CASE WHEN MIN(ATCHREL_ID) > -10001 THEN -10001 ELSE MIN(ATCHREL_ID) - 1 END FROM ATCHREL),
    (SELECT COLLATERAL_ID FROM COLLATERAL WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{COLLATERAL_NAME}'),
    (SELECT ATCHOBJTYP_ID FROM ATCHOBJTYP WHERE IDENTIFIER = 'COLLATERAL'),
    (SELECT ATCHTGT_ID FROM ATCHTGT WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND IDENTIFIER = '{COLLATERAL_NAME}'),
    (SELECT ATCHRLUS_ID FROM ATCHRLUS WHERE IDENTIFIER = 'NOUSAGE')
);

INSERT INTO ATCHAST (ATCHAST_ID, STOREENT_ID, ATCHTGT_ID, ATCHASTPATH, DIRECTORYPATH, MIMETYPE) VALUES (
    (SELECT CASE WHEN MIN(ATCHAST_ID) > -10001 THEN -10001 ELSE MIN(ATCHAST_ID) - 1 END FROM ATCHAST),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    (SELECT ATCHTGT_ID FROM ATCHTGT WHERE IDENTIFIER = '{COLLATERAL_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    '{ATTACHMENT_ASSET_PATH}',
    '{ATTACHMENT_ASSET_PATH}',
    '{ATTACHMENT_MIME_TYPE}'
);

INSERT INTO EMSPOT (EMSPOT_ID, STOREENT_ID, NAME, DESCRIPTION, USAGETYPE) VALUES (
    (SELECT CASE WHEN MIN(EMSPOT_ID) > -10001 THEN -10001 ELSE MIN(EMSPOT_ID) - 1 END FROM EMSPOT),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    '{ESPOT_NAME}',
    '{ESPOT_DESC}',
    'MARKETING'
);

INSERT INTO DMEMSPOTDEF (DMEMSPOTDEF_ID, EMSPOT_ID, STOREENT_ID, CONTENTTYPE, CONTENT) VALUES (
    (SELECT CASE WHEN MIN(DMEMSPOTDEF_ID) > -10001 THEN -10001 ELSE MIN(DMEMSPOTDEF_ID) - 1 END FROM DMEMSPOTDEF),
    (SELECT EMSPOT_ID FROM EMSPOT WHERE NAME = '{ESPOT_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    'MarketingContent',
    (SELECT COLLATERAL_ID FROM COLLATERAL WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{COLLATERAL_NAME}')
);

Author: daharveyjr

I’m a solution architect responsible for the design, development, implementation, testing, and maintenance of e-commerce operations and applications using the Hybris and WebSphere Commerce product suites and other web technologies such as Java, J2EE/JEE, Spring, PHP, WordPress and more. Twitter | Facebook | LinkedIn

Leave a Reply

Your email address will not be published.