DESCRIPTION

virtualCATALOG^TM SQL is a stand-alone database solution for web sites. It was written with Virtual Focus Inc's virtualCART product in mind but has been used in many ways without virtualCART. It is used to generate catalog pages from a database of catalog items and templates, and to manage that database.

Back to Table of Contents

REQUIREMENTS

This program requires the perl interpreter and some non-standard perl modules to be installed. Contact your web site host if you have questions regarding this installation.

The specific requirements of virtualCATALOG^TM SQL are:

• Perl 5.004_04

  Many UNIX web servers already have perl installed, but it might be out of date, so ask your system administrator if they have a version more recent than 5.004_04 (which was released in October 1997).

  If you need to install perl on a web server running Windows NT, you can download it from http://www.activestate.com/.

  Perl 5.6 is the newest version.
  
  

• Perl Modules
  To learn how to install PERL modules, please visit http://cpan.org/modules/INSTALL.html or http://search.cpan.org/author/JHI/perl-5.8.0/lib/CPAN.pm or contact your web administrator.
  
  
  • CGI perl module (usually included in standard install of perl)
    
    
  • Tie::IxHash

    This module isn't included with the standard perl distribution. If it isn't already installed, ask your system administrator to install it for you.
    
    dropdownStart.pm plugin requires this module. This plugin allows you to create drop down menus from your option fields. If you are unable to install this module, be sure to remove the file "dropdownStart.pm" from the plugins directory.

  • DBI

    The DBI perl modules aren't included in the standard perl distribution. DBI stands for Database Interface and is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used. If they aren't already installed, ask your system administrator to install them for you. You can find documentation and download DBI from it's homepage at: http://www.symbolstone.org/technology/perl/DBI/index.html

    They can also be quickly installed by installing Bundle::DBI from CPAN. Installing DBI requires a compiler to compile parts of the package.

  • DBD::CSV
    
    This module isn't included with the standard perl distribution. If it isn't already installed, ask your system administrator to install it for you. The DBD::CSV is a driver for the DBI. This driver allows Virtual Catalog to import your text files and run flat file databases. This module is required if you want to use the Import function of Virtual Catalog.

    DBD::CSV can be quickly installed by installing Bundle::DBD::CSV.
    
    

  • DBD::mysql
    This module is required if you wish to utilize the MySQL feature of Virtual Catalog. DBD::mysql is a good choice for many varieties of UNIX. It is cheap and many UNIX servers already have it installed.
    
    If you want to use the advance database features, a DBD module for the type of database you want to use will need to be installed.

    Some examples are DBD::ODBC, DBD::mysql, DBD::Oracle, DBD::Postgres, and DBD::CSV.

    DBD::ODBC is a good choice if your web server runs NT. This module will allow you to use any ODBC compliant database for your data. Microsoft Access and Microsoft SQL Server are typical examples of ODBC compliant database servers.

    DBD modules are available at http://www.symbolstone.org/technology/perl/DBI/index.html and there are DBD modules for just about every major database server.

    DBD modules can also be found in CPAN.

  • LWP
    This module allows Virtual Catalog to interface with the browser and display data.
    
    
  • URI
    Uniform Resource Identifier.
    
    
  • Storable
    
    
• For more assistance in determining if your web server has the right modules installed, download this file.
  Installing perldebug.cgi:
  • Change the first line(it begins with #!) to point to your PERL intepreter. If you do not know how to do this, please contact your web administrator.
  • Upload the file into your cgi-bin directory and rename it from perldebug.txt to perldebug.cgi
  • Change the permissions on the file to 755
  • Run the file from your web browser e.g. http://www.mywebsite.com/cgi-bin/perldebug.cgi
  The script will then analyze your PERL and provide you with the necessary information.
  
  

Do you have virtualCATALOG questions? Check out our virtualCATALOG FAQ

INSTALLATION

Installing virtualCATALOG^TM SQL isn't really difficult. The only difficult part would be installing the required perl modules if they aren't already installed or you're not installing a binary package of virtualCATALOG^TM SQL.

Before installing, talk your your web administrator and find out if your web site host has the proper version of perl installed or if any of the other perl modules need to be installed.

Once all of the requirements are met, then you're ready to install and use virtualCATALOG^TM SQL.

1. Unpack the virtualCATALOG^TM SQL distribution and carefully read the virtualCATALOG^TM SQL User guide in the docs directory.
   
   
2. Rename the ``b-xxxx.cfg'' file in the data directory to your virtualCART ID. For example, if your virtualCART ID was b-2400, then rename ``b-xxxx.cfg'' to ``b-2400.cfg''.
   
   
3. Upload the entire VirtualCatalog3 directory and all of its contents to your cgi-bin directory in your web server.
   
   
4. Change the permissions as follows:
   VirtualCatalog3 - 775
   
   • CatalogMgr.pl - 755
   • html - 755
   • Hdr - 755
   • Ftr - 755
   • Htx - 755
   • Sample - 755
   • data - 775
     • b-xxxx.cdb - 644
     • b-xxxx.cfg - 644
   • modules - 755
   • plugins - 755
   • Vcat - 755
     
     
5. Copy the vc_login.htm file to the root directory of your web site. This is the directory where you'll find our index/home page.
   If you have placed the virtualCATALOG^TM SQL script into any other place than the default (/cgi-bin/VirtualCatalog3/), then you will need to rename the action in the <form> tag to reflect this.

Do you have virtualCATALOG questions? Check out our virtualCATALOG FAQ

GETTING STARTED

To begin generating catalog pages with virtualCATALOG^TM SQL you will need to do the following steps:

1. Catalog Template Pages
   Create a catalog page template and upload it to your web server.
   
   

2. Create a database of items for your catalog
   You may do this by entering each record with the CatalogMgr Add Data option, or by uploading a delimited ASCII file to your web server, then using the CatalogMgr Import Data option to add the records in the file to the database.
   
   

3. Create an HTML page
   This page should contain a link to CatalogMgr to generate catalog pages. Upload this html file to your web site.

See the sample files for examples of catalog page templates, calling HTML pages and data.

Back to Table of Contents

CATALOG TEMPLATE PAGES

Catalog page templates define the format of the generated catalog pages. Use standard HTML, virtualCART coding and the specially defined virtualCATALOG^TM SQL substitution codes to create templates that define your catalog pages. Catalog templates should be placed in the HTX subdirectory of virtualCATALOG^TM SQL on your web server.

virtualCATALOG^TM SQL template page substitution codes:

For a few samples of virtualCATALOG^TM SQL template pages, please follow this link

Back to Table of Contents

PLUG-IN STUFF

The default plug-ins included with this version of virtualCATALOG^TM SQL provide the following substitution codes. More detailed information and configuration options on each plugin can be found by logging into the Catalog Manager.

virtualCATALOG^TM SQL plug-in substitution codes:

Please remember that these are default plug-ins that come with the software package. virtualCATALOG^TM SQL has the ability to expand and add plug-ins based on your needs.

Back to Table of Contents

USING THE CATALOG MANAGER

CatalogMgr.pl Administration area operates in two modes: configuration / data-management and catalog page generation.

Using CatalogMgr from vc_login.htm will access the configuration / data-management mode. Start a browser session and open vc_login.htm. After you login from the vc_login page, option pages are displayed with clear instructions to guide you through:

• changing configuration items (password, default values, plug-in options, etc.)

• adding records to the database

• deleting records from the database

• modifying records in the database

• importing data from a delimited ASCII file

• viewing existing database records

Linking to virtualCATALOG^TM SQL

Calling CatalogMgr from your web page will access the catalog page generation mode. You need to place code on your web page, either a form action or an href, to call CatalogMgr. Parameters for CatalogMgr are listed below. Your web page coding may specify an actual value or, if in a Catalog Page Template, it may use one of the %x% substitution codes. Parameters to CGI programs are given after the program name and are specified in the format parameter=value and are separated from each other with an ampersand (&). If the value contains spaces, each space should be replaced with a plus sign (+). The parameter list is separated from the program name by a question mark (?). For example:

http://www.mywebsite.com/cgi-bin/VirtualCatalog3/CatalogMgr.pl?cartID=b-xxxx&template=myPageTemplate.htx&hdr=My+Online+Shopping+Site

Please note that the URL will have to be changed to point to the CatalogMgr.pl file on your web server.

Parameters are case-sensitive. One of the most common problems is having parameters with improper case.

Here is a list of the parameters that CatalogMgr.pl accepts and whether the parameter is required or not:

Back to Table of Contents

Logging On

CatalogMgr is designed for a single-user. To begin working on the database or to change any configuration values, the user must log on. vc_login.htm must be opened in a browser.If the installation steps was done properly, you can access the vc_login.htm file at http://www.yourwebsite.com/vc_login.htm.This will bring up a login screen. The Cart ID number and the Password need to be entered before any work can be done. The default password for the first login is admin. Upon successful entry of the ID and Password, an option screen that gives a choice of making configuration changes or doing database administration will be displayed.

Only one user at a time can make changes to the database or the configuration file. If a second user successfully logs in, the first user will not be able to make any more changes to the configuration file or the database. The first user will receive a timeout error if he tries to make any additions or changes after the other user has logged in.

If a currently logged in user does not do any work for 30 minutes, they will receive a timeout error when they try to continue using CatalogMgr. If a timeout message is received, it will be necessary to go through the login process again before any work can be done.

Back to Table of Contents

Configuration

The Configuration screen offers forms for changing the current password and changing any defaults that are currently being used. Only one of the forms can be used at a time. To access the Configuration screen, you must log into CatalogMgr. The default password, needed for logging into the database management portion of CatalogMgr, is admin. This password should be changed before you begin to create a database. Change the password by logging in, using admin as the password, and then selecting the Configuration option.

To change the password, enter a new password, then enter it again as indicated on the form. Then select the Change Password option. A ``Password changed'' message will be shown on successful completion.

To change the default values for a plug-in, select the plugin from the drop-down list and click the Configure Plugin button. This will bring up a list of all user definable options for the plug-in. Once you have updated the plug-in options, select Update Plugin to record your changes.

The Configuration screen allows for adjustment of the number of records displayed per catalog page. You can enter a default number that will produce an effective display for your product on an average catalog page. The templates you design can over-ride this value by using the displayNumber parameter. This allows customizing a page for any special situations.

The boxes for row colors allow you to have alternating row colors for your search results. Both boxes need to be a color in hex. To use the alternating row colors, use the %bgcolor% token in your template files.

Most users of virtualCATALOG^TM SQL will use virtualCATALOG^TM SQL .cdb files to store product data, but one of the newest features of VirutalCatalog is the ability to use relational database servers to store product information.

In order to use the advanced database features, you will first need to know how to use and setup your database software. Unless you know how to use your database software, you will probably need your web site administrator to help you setup virtualCATALOG^TM SQL to use it.

Storing product information using a database server, instead of .cdb files, will give you several advantages:

• Store an almost unlimited number of products.

• Integrate virtualCATALOG^TM SQL into your other business databases.

• Improve performance of searching and retrieving data.

• Scale virtualCATALOG^TM SQL to handle more traffic.

• Use a database you already have.

Configuring virtualCATALOG^TM SQL to use a database will require some knowledge of Perl's modules installation and the appropriate DBD module for your database will need to be installed. Installing DBD modules might require the help of your web site hosting company.

First install the DBD module for your database. You can find documentation about DBI and download DBD modules from http://www.symbolstone.org/technology/perl/DBI/index.html

Back to Table of Contents

CREATING MySQL DATABASE

Once the appropriate DBD module is installed for your database, follow these steps.

1. Log into your MySQL server and create a new database. Syntax is CREATE DATABASE db_name
   If you do not know how to do this, please contact your web administrator.
2. Telnet into your server and go to the docs directory in your VirtualCatalog3
3. Type mysql -u <username> -p<password> <database_name> < CatalogMgr_mysql.sql
   <username> is the username to access the MySQL database. There is a space between -u and <username>
   <password> is the password to access the MySQL database. If there is none, omit this parameter. There is NO space between -p and <password>
   <database_name> is the name of the database that you've just created.
   This will create a table in your MySQL database called CatalogMgr.
4. Once the database has been prepared, go to the virtualCATALOG^TM SQL configuration screen and enter the data-source name (DSN) of your database, the username used to connect to the database, and the password.
   
   DSNs usually take the form of dbi:driver:<database_name>. For example, if you wanted to store the information in a MySQL database named "catalog", then use dbi:mysql:catalog for your DSN. If you wanted to use a Microsoft Access database whose system DSN is ``inventory'', the DSN would be dbi:ODBC:inventory.
   
   DSNs have many options and advanced features. Reading the DBI documentation and the documentation that comes with the DBD module for your database will tell you how to construct the DSN for your database. You can find documentation about DBI and DBD modules at: http://www.symbolstone.org/technology/perl/DBI/index.html

Do you have virtualCATALOG questions? Check out our virtualCATALOG FAQ

Data Management

Choosing the Data Management option allows you to create or change the database records of the products that will be displayed on your catalog pages. The main Data Management screen offers the following database options: Add Data, Modify Record, Delete Record, Import Data, and View Data. Some of these options, such as Modify Record, can use additional information. Additional data can be specified on this screen: a search field and/or a specific string to search for. The default field is the Product Number. This will always be selected unless another choice is made. The default field is ignored when not needed by the chosen option.

When the Product Number field on Data Management is used to find records for modifying, deleting, or viewing, either a complete or partial product number may be entered. Searching doesn't use perl regular expressions, but it does have some similarities.

Note: Entering MUS will find all products with numbers that contain MUS, such as MUS-192 or T23MUS.

To find product numbers that begin with a certain character or expression, begin the search expression with ^. For example, ^MUS would find MUS-345 and MUSIC23. It would not find 105-MUS or 23MUS11. ^9 would find all numbers that started with 9.

To find product numbers that end with a certain character or expression, end the search with $. For examples, MUS$ would find 123MUS and 3B-4MUS. It will not find 23MUS11 or MUS-105.

1. Adding Data

   The Add Data option does not require any additional information. Simply click the Add Data button to access the Add A Record screen.

   This is the form for adding new records. Values for these fields may not contain pipes (``|''). The product number field is required and must be unique. Some fields already contain default values. These defaults may be kept or changed. Fields that are not needed may be left blank. Any virtualCART coding that will be useful, including virtualCART op# keywords may be entered in the product definition fields. HTML syntax may also be entered in appropriate fields. Look at the examples in Appendix A for more information.

   If you want to have search results sorted, then you will probably want to put some thought into the numbering scheme for your product numbers. The sorting is performed alphabetically. Product numbers that are numeric will work, but are not the best choice. If numeric product numbers are all padded to be the same length, 0034 instead of 34, they will work better. Using product numbers that contain the same number of characters such as 000011, 000111, and CS-456, cause the records to sort in the expected order and make records easier to view.

   The sequence of product numbers 1, 2, 3, 4, 5, 6, 10, 11, 12, 13, 20, 21, 100, 101, 111 would sort as:

           1, 10, 100, 101, 11, 111, 12, 13, 2, 20, 21, 3, 4, 5, 6.

   The partnumber field is used to sort the records by default, but you can change which field is used by setting the ``sort_on'' parameter.
   
   Back to Table of Contents
   
   

2. Modify Record

   The Modify Record option can use additional information to help select the correct record(s) to modify. If a Search for value is entered, then a Search in fields selection should be made to determine what database field(s) will be searched. If nothing is entered in Search for, all records in the database will be shown on the Select record to modify screen.

   If a Search for value is given, CatalogMgr will try to find a match for the given value in the database field that is selected from the Search in fields list. To search for all pasta products, e.g, Product Description might be selected in Search in fields, with pasta as the Search for value. If category is chosen as the field to search, the value to search for might be ``Children's stockings'', ``hand tools'', ``tires'' or whatever category is appropriate for the current catalog. If there is exactly one record that meets the selection criteria, the Modify This Record screen will show that record. Otherwise the Select record to modify screen will show all matches that were found.

   Click the Modify Record button to reach one of the modify screens. Enter or change values on the Modify This Record screen just as you would on the Add A Record screen.
   
   Back to Table of Contents
   
   

3. Delete Record

   Delete Record works much like Modify Record. Entering a value in Search for, and selecting a field, will limit the search; otherwise all records will be presented for selection. Whether a single record or a list of records matches the search criteria, the Select record(s) to delete screen will be shown. The actual record(s) to delete must be selected from the item(s) given on this screen. No records will be deleted without being selected from this screen.
   
   Back to Table of Contents
   
   

4. Import Data

   Using Import Data, new records can be added, fields can be changed, or records can be replaced. With the form on this screen, data can be brought into the database from a delimited ASCII/Text file.

   To import data, follow these steps:

   • Browse for the text file that contains your database records
     
     
   • Type in your delimiter character
     The delimiter character used in the import file defaults to ``|''. If a different delimiter is used, this default must be changed to the correct character before trying to import the file. Use ``\t'' if tabs are used to delimit the file.

   Fields in the delimited file must not contain the character used as a delimiter for the fields. Common delimiters would be a pipe symbol (``|'') or a tab. Data from an Excel spreadsheet saved as a .csv file uses a comma (``,'') as the delimiter.

   • Select either Update current records or Delete records then add.
     
     Update current records will cause any records with matching product numbers to be edited. Any fields selected in the import file will replace those fields in the matching record in the database. Any new records in the import file will be added to the database.
     
     

   Delete current records then add will remove all old records and replace them with the new records from the import file. Before beginning to import a file, it is a good idea to make a backup copy of the original database. If the wrong option was chosen, if the wrong file used, or some other problem were encountered the backup database file could be copied to restore the database, and the Import Data action could be repeated.

   • Select Fields are not quoted or Fields are quoted.
     
     This option allows you to tell virtualCATALOG^TM SQL if the fields in the text file have quotes around them or not. MS Excel has the tendency to put quotes around fields when exporting its database to a text file as well as put two qoutes where a qoute is found in your database.
     
     
   • Select whether you want virtualCATALOG^TM SQL to interpret line feeds and carriage returns as record separators or not.
     
     Interpret Line Feeds and Carriage Returns as Record Separators tells virtualCATALOG^TM SQL that there are 2 characters at the end of each line that signifies the end of the line. Most database text files use line feeds to separate records. However, on some Operating Systems, they use a line feed and a carriage return to signify a separate record. This will cause your database to either strange characters in op16 or have empty lines in your products list. By default, this is left unchecked.
     
     
   • Select the fields you want imported and enter the column number the field corresponds to in your text file.

   The fields that are used in the import file must be checked on the form. If the fields in the file are in the same order as the items checked, no column numbers need to be entered. If the import file's fields are not in the same order as the fields on the form, the number of the column containing the field needs to be entered.

   An import file might be used to update prices for some products in the database. In this case, the file might contain just two fields: product number and cost. If product number is in the first column and cost is in the second, those two fields would be checked and no numbers are needed. If the import file had 4 columns with the product number in column 1, details in column 4, and cost in column 2, then column numbers would be required. If any column numbers are required, then all of the selected import fields must have column numbers.

   • Import now
     When all of the information is entered on the form, click the Import File Now button. If the file is successfully imported, a screen with that message will be shown, and it will contain a button to return to the Data Management screen.
     
     
   Back to Table of Contents
   
   
5. View Data

   View data works much like Modify Record. Entering a value in Search for, and selecting a field to search, will limit the search; otherwise all records will be presented. If a list of products is returned, all of the details of any product can be viewed by selecting the product, then clicking the View This Record button. A single matching record will immediately be displayed in complete form.

Back to Table of Contents

Do you have virtualCATALOG questions? Check out our virtualCATALOG FAQ

HISTORY

3.32

• Fixed issue where import function may not work with certain versions of SQL-Statement

3.31

• Fixed minor issue with Page Numbers and Previous/Next links in Administration Area that affected some customers.

3.30

• Miscellaneous modifications to optimize database call and page generation

3.21

• Searching "AND" by default causes problems when SearchField is set to "all". If SearchField is set to "all", Search is defaulted to "OR"

• Adding quotes around search terms causes Previous/Next links to break. Escape quote marks in search terms with &quot;

• When modifying a record, spaces in partnumber causes script not to update. Fixed now.

3.20

• Previous/Next page links now show up in administration View/Modify/Delete screens

• Search now does AND by default (previously uses OR)

• Search terms can now be grouped with quotes. e.g. Searching with quotes around "Audio Tape" now searches Audio Tape as one keyword not two.

• This version no longer supports CDB databases (virtualCATALOG BASIC databases). You can still import them but you are now required to use MySQL databases. CDB support was dropped in favor of MySQL optimized code

3.11

• Added toggle to enable/disable Login time outs.

• Added Export to CSV function in Administration area. Removed export via htx template.

3.10

• Fixed error where not selecting a record when deleting doesn't give the right error message. Suppose to say no records select, but instead says Record Deleted, but no records were deleted.

• Updated documentation for dropdown menu processor

• Added export.htx to Htx directory to provide ability to download the catalog database

3.09

• Fixed issue where no error message will appear or Fatal error occurs when login failed.

• Fixed issue where apostrophe caused MySQL search to fail.

• Removed administration buttons from Error Message template. Although a person clicking on the button will get a timeout error, it's better safe than sorry

• Changes to perldebug to make sure it detects the MySQL perl drivers properly

3.00

• Code restructured to make it more managable.

• Updated search to allow "and" and "or" keword lists as well as allowing developer to include complete search string.

• Added support for plugins to modify page generation capabilities.

• Created plug-in to allow user to display products in columns.

• Created plug-in to simplify creation of drop-down menus

• Created plug-in to include an image based on quantity in stock.

2.40

• Misc bugfixes.

• Patched security issue with header and footer files.

2.00

• Almost total rewrite.

• Added support for Perl's DBI modules. Now virtualCATALOG^TM SQL can use relational databases.

1.33

• Fixed issue with config file where blank $imagesurl and $imagesdirectory variablescaused login problems.

1.32

• Added function to browse and upload image to web server when adding or modifying data.

1.29

• Fixed security issue.

1.23

• Changed document over to Pod format and updated it.

• Removed the t-shirt examples from the sample.cdb.

1.22

• Changed around the $installLocation autodetect. I think it's more straight-forward to users now.

• %image% token removes any leading or trailing spaces from the image URL, in case someone accidentally left them in.

1.21

• Added alternating row colors feature.

• Updated the configuration form so users can configure the row colors for the alternating rows.

• Put default row colors in b-xxxx.cfg.

• Added URI escaping inside %previousStart% and %nextStart%. That fixed the ``spaces inside previous/next URL'' problem.

1.01

• Added a line so a user can specify the path to the base directory, overriding the autodetected path.

• Fixed some bugs involved in keeping the database sorted.

• Fixed the ``space before image url'' bug.

• Added %category% token.

1.00

• Initial Version