Troubles with Canada Post module - help please?

[SusanF]

This is my first post to the osCommerce forum, but I've visited many times and have always found the answers I needed before having to post a plea of my own.. until now...

 

I just installed the Canada Post Shipping Module With Dimensions (latest version) and carefully went through all of the Shopping Cart Edits that were outlined in the install.txt file but have had miserable luck in getting the module to work.

 

When I go to create a new product, and click "insert" to publish it, I'm getting the following error message:

 

1054 - Unknown column 'p.products_dim_type' in 'field list'

 

select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_dim_type, p.products_weight_type, p.products_length, p.products_width, p.products_height, p.products_ready_to_ship, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id from products p, products_description pd where p.products_id = '32' and p.products_id = pd.products_id and pd.language_id = '1'

 

[TEP STOP]

 

This is just be beginning, the products that were already in my store are now all riddled with error messages too:

 

http://wendydewarhughes.com/catalog/shoppi...art.php?sort=2a

 

Any advice on what to do? Should I uninstall and start over again? I've spent hours already and am getting very frustrated....

 

Sue

[NorthernPenguin]

This is my first post to the osCommerce forum, but I've visited many times and have always found the answers I needed before having to post a plea of my own.. until now...

 

I just installed the Canada Post Shipping Module With Dimensions (latest version) and carefully went through all of the Shopping Cart Edits that were outlined in the install.txt file but have had miserable luck in getting the module to work.

 

When I go to create a new product, and click "insert" to publish it, I'm getting the following error message:

 

1054 - Unknown column 'p.products_dim_type' in 'field list'

 

select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_dim_type, p.products_weight_type, p.products_length, p.products_width, p.products_height, p.products_ready_to_ship, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id from products p, products_description pd where p.products_id = '32' and p.products_id = pd.products_id and pd.language_id = '1'

 

[TEP STOP]

 

This is just be beginning, the products that were already in my store are now all riddled with error messages too:

 

http://wendydewarhughes.com/catalog/shoppi...art.php?sort=2a

 

Any advice on what to do? Should I uninstall and start over again? I've spent hours already and am getting very frustrated....

 

Sue

You too! I had the same problem on my sandbox (test site) but very quickly found out what the problem was.... my sandbox was using the latest version of MySQL, while my production site is using v4.1.18. Looks like there were some major changes in how MySQL handles ' enum'. You will probably have to delve into the MySQL documentation to find the answer...start with "unum" and work from there!

[SusanF]

You too! I had the same problem on my sandbox (test site) but very quickly found out what the problem was.... my sandbox was using the latest version of MySQL, while my production site is using v4.1.18. Looks like there were some major changes in how MySQL handles ' enum'. You will probably have to delve into the MySQL documentation to find the answer...start with "unum" and work from there!

 

Yikes, that doesn't sound good - what do I do if I don't know MySQL? Is this a web host provider problem? Or am I SOL in using this module?

[NorthernPenguin]

Yikes, that doesn't sound good - what do I do if I don't know MySQL? Is this a web host provider problem? Or am I SOL in using this module?

 

Lets see if I can help... Can you tell me what version of MySQL and PHP you are using? From there I'll see what I can do.

[SusanF]

Lets see if I can help... Can you tell me what version of MySQL and PHP you are using? From there I'll see what I can do.

 

Thank you so much! I am so frustrated right now - I think my troubles began when I installed the shipping module WITH DIMENSIONS as opposed to just the normal shipping module.

 

Update: I went to the shipping module contribution area http://www.oscommerce.com/community/contributions,391/page,2 and installed these files, overriding the previous (with dimentions) install.

 

I then went back and restored all of the files that I manually edited (as per the instructions in the "with dimensions" install.txt file back to their originals (which should have removed all references to the dimensions)

 

I then did another test upload of a new item and it all worked great! Whew! UNTIL I went to the live shopping cart, and tried to purchase that item - now I'm getting the following error when I click on "buy now":

 

 

 

1054 - Unknown column 'p.products_dim_type' in 'field list'

 

select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_dim_type, p.products_weight_type, p.products_length, p.products_width, p.products_height, p.products_ready_to_ship, p.products_tax_class_id from products p, products_description pd where p.products_id = '35' and pd.products_id = p.products_id and pd.language_id = '1'

 

[TEP STOP]

 

 

 

I suspect the mySQL is still remembering the dim_type from the previous install? If so... I'm lost...

 

 

MySQL: Client API version 3.23.49

PHP Version 4.4.3

 

Thanks.....

[NorthernPenguin]

Thank you so much! I am so frustrated right now - I think my troubles began when I installed the shipping module WITH DIMENSIONS as opposed to just the normal shipping module.

 

Update: I went to the shipping module contribution area http://www.oscommerce.com/community/contributions,391/page,2 and installed these files, overriding the previous (with dimentions) install.

 

I then went back and restored all of the files that I manually edited (as per the instructions in the "with dimensions" install.txt file back to their originals (which should have removed all references to the dimensions)

 

I then did another test upload of a new item and it all worked great! Whew! UNTIL I went to the live shopping cart, and tried to purchase that item - now I'm getting the following error when I click on "buy now":

 

 

 

1054 - Unknown column 'p.products_dim_type' in 'field list'

 

select p.products_id, pd.products_name, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_dim_type, p.products_weight_type, p.products_length, p.products_width, p.products_height, p.products_ready_to_ship, p.products_tax_class_id from products p, products_description pd where p.products_id = '35' and pd.products_id = p.products_id and pd.language_id = '1'

 

[TEP STOP]

 

 

 

I suspect the mySQL is still remembering the dim_type from the previous install? If so... I'm lost...

 

 

MySQL: Client API version 3.23.49

PHP Version 4.4.3

 

Thanks.....

 

Hmmm... I'm running MySQL 4.1.20. Do you have access to phpMyAdmin or some other way of viewing you MySQL database? If so, find the "products" table and check if you have the following fields:

• products_id int(11)
  
• products_quantity int(4)
  
• products_model varchar(12)
  
• products_image varchar(64)
  
• products_price decimal(15,4)
  
• products_date_added datetime
  
• products_last_modified datetime
  
• products_date_available datetime
  
• products_weight decimal(5,2)
  
• products_weight_type enum('lbs', 'kgs')
  
• products_dim_type enum('in', 'cm')
  
• products_length decimal(6,2)
  
• products_width decimal(6,2)
  
• products_height decimal(6,2)
  
• products_ready_to_ship enum('0','1')
  
• products_status tinyint(1)
  
• products_tax_class_id int(11)
  
• manufacturers_id int(11)
  
• products_ordered int(11)
  

Get back to me after you check the table. BTW, if you are interested, you can view my sandbox at http://penguinhome.ca/cyclechic

This will give you an idea how the CP with dimensions actually works.

[SusanF]

Hmmm... I'm running MySQL 4.1.20. Do you have access to phpMyAdmin or some other way of viewing you MySQL database? If so, find the "products" table and check if you have the following fields:

• products_id int(11)
  
• products_quantity int(4)
  
• products_model varchar(12)
  
• products_image varchar(64)
  
• products_price decimal(15,4)
  
• products_date_added datetime
  
• products_last_modified datetime
  
• products_date_available datetime
  
• products_weight decimal(5,2)
  
• products_weight_type enum('lbs', 'kgs')
  
• products_dim_type enum('in', 'cm')
  
• products_length decimal(6,2)
  
• products_width decimal(6,2)
  
• products_height decimal(6,2)
  
• products_ready_to_ship enum('0','1')
  
• products_status tinyint(1)
  
• products_tax_class_id int(11)
  
• manufacturers_id int(11)
  
• products_ordered int(11)
  

Get back to me after you check the table. BTW, if you are interested, you can view my sandbox at http://penguinhome.ca/cyclechic

This will give you an idea how the CP with dimensions actually works.

 

This is what is in my PRODUCTS table:

 

products_id int(11)

products_quantity int(4)

products_model varchar(12)

products_image varchar(64)

products_price decimal(15,4)

products_date_added datetime

products_last_modified datetime

products_date_available datetime

products_weight decimal(5,2)

products_status tinyint(1)

products_tax_class_id int(11)

manufacturers_id int(11)

products_ordered

 

I see I don't have the dim_type but you do ... ?

[NorthernPenguin]

This is what is in my PRODUCTS table:

 

products_id int(11)

products_quantity int(4)

products_model varchar(12)

products_image varchar(64)

products_price decimal(15,4)

products_date_added datetime

products_last_modified datetime

products_date_available datetime

products_weight decimal(5,2)

products_status tinyint(1)

products_tax_class_id int(11)

manufacturers_id int(11)

products_ordered

 

I see I don't have the dim_type but you do ... ?

Ok..... in the Canada Post with Dimensions contrib there should be a file called cpMS2.sql. From phpMyAdmin --> Import you can run the sql file. It will add the missing fields for you... tell me if this works! :thumbsup:

Edited February 25, 2007 by NorthernPenguin

[SusanF]

Ok..... in the Canada Post with Dimensions contrib there should be a file called cpMS2.sql. From phpMyAdmin --> Import you can run the sql file. It will add the missing fields for you... tell me if this works! :thumbsup:

 

 

Import has been successfully finished.

SQL query: # This install provided by [email protected]

# any questions just email us and we will try to help

# Copyright October 23 2004

ALTER TABLE products ADD products_weight_type ENUM( 'lbs', 'kgs' ) NOT NULL default 'lbs' AFTER products_weight,

ADD products_dim_type ENUM( 'in', 'cm' ) NOT NULL default 'in' AFTER products_weight_type,

ADD products_length DECIMAL( 6, 2 ) DEFAULT '12' NOT NULL AFTER products_dim_type,

ADD products_width DECIMAL( 6, 2 ) DEFAULT '12' NOT NULL AFTER products_length,

ADD products_height DECIMAL( 6, 2 ) DEFAULT '12' NOT NULL AFTER products_width,

ADD products_ready_to_ship ENUM( '0', '1' ) NOT NULL default '0' AFTER products_height;# Affected rows: 9

# Done

# this will add the setting in your DB for Canada Post Shipping Module with dimensions 3.4-3.7

 

 

 

IT WORKED!!! THANK YOU THANK YOU THANK YOU THANK YOU!!!!

Now I just need to go back and put in the dimensions for the Admin panel and we're off to the races! Did I mention how thankful I am? I was really tearing my hear out and here it was that silly .sql file - I saw it but had no idea what to do with it - thank to you I now do!

[NorthernPenguin]

Import has been successfully finished.

SQL query: # This install provided by [email protected]

# any questions just email us and we will try to help

# Copyright October 23 2004

ALTER TABLE products ADD products_weight_type ENUM( 'lbs', 'kgs' ) NOT NULL default 'lbs' AFTER products_weight,

ADD products_dim_type ENUM( 'in', 'cm' ) NOT NULL default 'in' AFTER products_weight_type,

ADD products_length DECIMAL( 6, 2 ) DEFAULT '12' NOT NULL AFTER products_dim_type,

ADD products_width DECIMAL( 6, 2 ) DEFAULT '12' NOT NULL AFTER products_length,

ADD products_height DECIMAL( 6, 2 ) DEFAULT '12' NOT NULL AFTER products_width,

ADD products_ready_to_ship ENUM( '0', '1' ) NOT NULL default '0' AFTER products_height;# Affected rows: 9

# Done

# this will add the setting in your DB for Canada Post Shipping Module with dimensions 3.4-3.7

 

 

 

IT WORKED!!! THANK YOU THANK YOU THANK YOU THANK YOU!!!!

Now I just need to go back and put in the dimensions for the Admin panel and we're off to the races! Did I mention how thankful I am? I was really tearing my hear out and here it was that silly .sql file - I saw it but had no idea what to do with it - thank to you I now do!

Glad it worked! Don't forget to test against the Canada Post test server http://sellonline.canadapost.ca. That will confirm that everything is working properly.