Preparing the database for the new engraving attributes

In this step, you will create engraving attributes.

About this task

These attributes are defined in the PATTRIBUTE table. This table allows you to create custom personalization attributes for products. Each personalization attribute has an attribute type.

Procedure

  1. Before creating the new engraving attributes columns, determine the member identifier for your store.
    1. Start the test environment.
    2. Open a browser and type the following URL: http://localhost/webapp/wcs/admin/servlet/db.jsp.
    3. In the input box, enter the following SQL statement: select MEMBER_ID from STOREENT where IDENTIFIER = ' Madisons'; where Madisons is the name of your store. Make note of the value .
  2. Creating the engraving rows in the PATTRIBUTE table. To save engraving attributes for each orderItem to the PATTRVALUE table, you must first create the attribute types inside of the PATTRIBUTE table.To create the Text, Size and Font attributes inside the PATTRIBUTE table:
    1. In the input box, enter the following SQL statement: 
      
INSERT INTO PATTRIBUTE(PATTRIBUTE_ID, ATTRTYPE_ID, NAME,
ENCRYPTFLAG, ACCESSBEANNAME)
        SELECT counter+1,'STRING','engravingText',
        0,'com.ibm.commerce.utf.objects.PAttrStringValueAccessBean'
from keys where tablename='pattribute'; 
        update keys set counter=counter+1 where tablename=
'pattribute'; 

      
INSERT INTO PATTRIBUTE(PATTRIBUTE_ID, ATTRTYPE_ID, NAME,
ENCRYPTFLAG, ACCESSBEANNAME)
        SELECT counter+1,'STRING','engravingFont',
        0,'com.ibm.commerce.utf.objects.PAttrStringValueAccessBean'
from keys where tablename='pattribute'; 
        update keys set counter=counter+1 where tablename=
'pattribute'; 

      
INSERT INTO PATTRIBUTE(PATTRIBUTE_ID, ATTRTYPE_ID, NAME,
ENCRYPTFLAG, ACCESSBEANNAME)
        SELECT counter+1,'STRING','engravingSize',
        0,'com.ibm.commerce.utf.objects.PAttrStringValueAccessBean'
from keys where tablename='pattribute'; 
        update keys set counter=counter+1 where tablename=
'pattribute'; 

      
SELECT NAME,PATTRIBUTE_ID FROM PATTRIBUTE WHERE NAME='engravingText' OR 
NAME='engravingFont' OR NAME='engravingSize';
      Make note of your three new Pattribute_id's, which are:
      • Pattribute_id_text
      • Pattribute_id_font
      • Pattribute_id_size
  3. Determine which catalog items will be engraveable. You must determine which items in your catalog will have engraving options, In this example, the wine glasses for the Web 2.0, Madison2 store are the engraveable items we will be using. To determine catentry_id of the items we are going to use in our example:
    1. In the input box, enter the following SQL statement: 
      
select catentry.catentry_id, catentdesc.name, catentdesc.shortdescription from catentry, 
catentdesc where catentry.member_id=Member_id and catentry.catenttype_id='ItemBean' 
and catentry.catentry_id = catentdesc.catentry_id and catentdesc.language_id=-1 and 
(name like '%Villagois%Wineglasses%' or name like '%Hawthorne%Wineglasses%');
      Where Member_id is the member identifier that you recorded above.
    2. Make note of both of their catentry_id's for "Villagois" Wineglasses and "Hawthorne" Wineglasses
  4. Associating the catalog entries with the custom attributes. You must associate all of the catentry_id with each pattribute_id inside of the PATTRPROD table Each catalog item now must be associated the pattribute, for example you want to keep track that your wine glass with catentry_id 10022 can have all three pattribute values, text, size and font:
    1. In the input box, enter the following SQL statement: 
      
INSERT INTO PATTRPROD(PATTRIBUTE_ID, CATENTRY_ID) VALUES (
Pattribute_id_text,
Catentry_id_Villagois);

      
INSERT INTO PATTRPROD(PATTRIBUTE_ID, CATENTRY_ID) VALUES (
Pattribute_id_size,
Catentry_id_Villagois);

      
INSERT INTO PATTRPROD(PATTRIBUTE_ID, CATENTRY_ID) VALUES (
Pattribute_id_font,
Catentry_id_Villagois);

      
INSERT INTO PATTRPROD(PATTRIBUTE_ID, CATENTRY_ID) VALUES (
Pattribute_id_text,
Catentry_id_Hawthorne);

      
INSERT INTO PATTRPROD(PATTRIBUTE_ID, CATENTRY_ID) VALUES (
Pattribute_id_size,
Catentry_id_Hawthorne);

      
INSERT INTO PATTRPROD(PATTRIBUTE_ID, CATENTRY_ID) VALUES (
Pattribute_id_font,
Catentry_id_Hawthorne);

      Where Pattribute_id_text, Pattribute_id_size and Pattribute_id_font are the three Pattribute_id values and Catentry_id is one of the values that you recorded in the above steps.