[ic] Importing Products Rounds Down Prices

Cameron B. Prince cplists at princeservices.com
Sat Feb 17 16:03:31 EST 2007


Hi Carol,

I identified this problem last year along with a temporary fix described in
my original post here:




Hey guys,

I recently moved a customer over and shortly after was informed of a problem
where all the product prices had the cents stripped off. A price in the
products.txt of 17.99 was showing up in MySQL as 17.00.

This is IC v4.8.5, MySQL v4.1.16, perl v5.6.2 and the latest DBI and
DBD::mysql.

I reimported the table several times and verified this was happening each
time. I did a describe on the table and found that the price column had not
been modified from the default foundation catalog:

| price         | decimal(12,2) |      | MUL | 0.00    |       |

To further test, I setup a standard catalog using IC v5.4.0 and copied the
products.txt file over and imported it. I had the same problem.

So I begin troubleshooting by dropping the product rows from 29,000+ to
1,000. I still had the same problem. I edited the
dbconf/mysql/products.mysql file and removed:

Database  products  NUMERIC      price

Once I imported again, the prices were correct. Just as a sanity check, I
put the line back in the dbconf file and imported again. The problem
reoccurred. There appears to be a bug in the import function.

I recall seeing code in the admin pages or source in the past that used the
NUMERIC directive to control sorting I believe. Are there any other
ramifications I should be aware of by removing it from the dbconf file?

Thanks,
Cameron





> -----Original Message-----
> From: interchange-users-bounces at icdevgroup.org [mailto:interchange-users-
> bounces at icdevgroup.org] On Behalf Of cblevins at macronet.com
> Sent: Saturday, February 17, 2007 2:37 PM
> To: interchange-users at icdevgroup.org
> Subject: [ic] Importing Products Rounds Down Prices
> 
> When importing products.txt to my MySQL IC database prices are being
> rounded
> down from 11.95 to 11.00.  If I change the price via the Admin interface
> it
> imports correctly.  I am uploading the initial products file with a bit
> over
> 2000 products so it really is not a viable option to update all the prices
> via the admin interface.  Should I be changing COLUMN_DEF "price=varchar"
> in
> products? I have searched the list and found a few posts but no real
> solution.  Any help with this would be greatly appreciated.
> 
> I have verified this on two different servers:
> 
> Server 1
> MySQL version 5.027
> IC version 5.5.0-200612100658
> 
> Server 2
> MySQL version 4.1.12
> IC Version 5.4.0
> 
> This is my products.sql on server 1:
> Database  products  products.txt __SQLDSN__
> Database  products  KEY          sku
> Database  products  HIDE_FIELD   inactive
> Database  products  COLUMN_DEF   "sku=char(64) NOT NULL PRIMARY KEY"
> Database  products  COLUMN_DEF   "description=varchar(128) NOT NULL"
> Database  products  COLUMN_DEF   "title=varchar(128) DEFAULT '' NOT NULL"
> Database  products  INDEX         title
> Database  products  COLUMN_DEF   "template_page=varchar(64)"
> Database  products  COLUMN_DEF   "comment=TEXT"
> Database  products  COLUMN_DEF   "thumb=varchar(128)"
> Database  products  COLUMN_DEF   "image=varchar(64)"
> Database  products  COLUMN_DEF   "price=DECIMAL(12,2) NOT NULL"
> Database  products  INDEX         price
> Database  products  COLUMN_DEF   "category=varchar(64) NOT NULL DEFAULT
> ''"
> Database  products  INDEX         category
> Database  products  COLUMN_DEF   "prod_group=varchar(64) NOT NULL DEFAULT
> ''"
> Database  products  INDEX         prod_group
> Database  products  COLUMN_DEF   "nontaxable=CHAR(3)"
> Database  products  COLUMN_DEF   "weight=varchar(12) DEFAULT '0' NOT NULL"
> Database  products  COLUMN_DEF   "size=varchar(96)"
> Database  products  COLUMN_DEF   "color=varchar(96)"
> Database  products  COLUMN_DEF   "author=varchar(255)"
> Database  products  COLUMN_DEF   "related=text"
> Database  products  COLUMN_DEF   "featured=varchar(32)"
> Database  products  COLUMN_DEF   "inactive=varchar(3) DEFAULT ''"
> Database  products  COLUMN_DEF   "gift_cert=varchar(3) DEFAULT ''"
> Database  products  COLUMN_DEF   "sub1=varchar(128)"
> Database  products  COLUMN_DEF   "sub2=varchar(128)"
> Database  products  COLUMN_DEF   "sub3=varchar(128)"
> Database  products  COLUMN_DEF   "sub4=varchar(128)"
> Database  products  NUMERIC      price
> 
> Thanx,
> Carol Blevins
> 
> _______________________________________________
> interchange-users mailing list
> interchange-users at icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users



More information about the interchange-users mailing list