osCommerce integration with UPS WorldShip

UPS Worldship allows for integration with your osCommerce store to pull order data onto your desktop for easy payment and generation of shipping labels.
To get UPS Worldship to work with an osCommerce store there are several steps required:
1. Install UPS Worldship on your desktop
2. Create an ODBC connection
3. Modify your Orders table to include shipping service and weight, optional
4. Create a UPS Worldship data map
5.  Start importing!

Creating an ODBC Connection

Open Database Connection is a service that allows your desktop computer to access your osCommerce database. To create a connection, you have to:

  • Open the server firewall to allow TCP_IN and TCP6_IN connections for port  3306. You can ask you hosting company to do this for you.
  • Give permission for your desktop IP address to access the database. Simply go to WhatIsMyIPAddress.com to work out what it is.  Then usually you can go to your hosting control panel to enter it.  In Cpanel it is the Databases > Remote MySQL function.   If you do not have a dedicated IP address you will need to keep changing this or provide a dynamic DNS address by using a service such as dyndns.org.
  • Find out your domain IP address as well as the database name, user and password which is in your osCommerce /includes/configure.php file
  • Test the ODBC connection using your PC Control Panel. This is an optional step but sometimes good to try if you are having connection problems.Go to your PC Windows Control Panel and enter ODBC in the search box.  You will find the “Set up data source (ODBC)” function. On the User DNS tab which in is usually the first tab, choose Add a connection as follows:
    1. Choose the driver. If you do not see a driver for MySQL then you will need to download one.
    2. Data Source Name: This can be anything but most people use their domain name
    3. Description: This can be anything but you can use “UPS Worldship”
    4. TCP/IP Server: Select this radio button.  Enter the IP address of your domain (not PC)
    5. User: database user
    6. Password: database user password
    7. Database: database name

Create UPS Worldship Map

UPS provide instructions here.
osCommerce orders table mapping is as follows:

  • Shipping Customer Name  = delivery_name
  • Shipping Line #1 = delivery_street_address
  • Shipping Line #2 = delivery_suburb
  • City = delivery_city
  • State  = delivery_state
  • ZIP = delivery_postcode
  • Country = delivery_country
  • Email address = customers_email_address
  • Phone Number  = customers_phone
  • Order # = orders_id
  • Order Status = orders_status

The Shipping Service and Weight are not available in the orders table.  However you can modify the table to add these two fields and change the order creation process in the osCommerce checkout to populate them. (If you use PayPal IPN then you may need to modify it in more than one place.)  osCommerce uses the format Carrier (Service) to describe shipping methods, for example, United Postal Service (Ground).  It might make life easier for you if, when you populate the Service name, you only use the Service e.g. Ground.

Importing

You can enter an Order ID in Worldship and it will pull the details from the database for you.
If you want to pull more than one order then your only option is to pull them all and then simply process the ones you need.
If you want to only pull the ones you need then you would need to take a further step at osCommerce level and create a process that extracts the orders that are ready to ship to a new table and use that table instead of the orders table for your mapping.

Port 3306 Security

UPS Worldship is a learning curve at the desktop.  Sometimes creating an ODBC connection seems difficult when in fact it is not.  Provided your server allows TCP connection for Port 3306 for both TCP and TCP6 then you should be OK. However not all shared hosting environments will allow it.
Opening Port 3306 can be seen as security risk and some PCI scanners will report it as such. If that is the case and you are on a dedicated serve you can simply close it before each scan.