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}')
);
