PERL DBI question

[YankeePride13]

Hello, currently I'm trying to use PERL to update my web sites database with new info. I am trying to update large numbers of rows, reading information from a text file, and it fails every time stating that I cannot update or insert a child row as foreign key constraints fail (which is something I understand, however I am not messing up any foreign key constraints). However, if I feed it 1 row to update at a time, there is no problem. Does anyone know if this is a short coming of perl or is there a problem on my end?

 

[max1x]

If it's an FK constraint, that is data specific. I don't think perl is manipulating ur data, unless you're updates are adding a dataPoint which is has some hidden character attached to it i.e "a "..etc and u have not properly parsed all the data...just a thought.

 

[travs69]

Probably a coding problem on your end. Post your code!!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[YankeePride13]

for reasons out of my control i can't post the whole code, but i'll post the queries that the perl script makes and ill find you in one how it works

my $update_pcm = $dbh->prepare(qq{
	UPDATE PRODUCT_CATEGORY_MEMBER SET product_Category_Id = ?, FROM_DATE IN NOW() WHERE PRODUCT_ID = ? AND PRODUCT_CATEGORY_ID = ?
});

my $update_product = $dbh->prepare(qq{
	UPDATE PRODUCT SET primary_product_Category_Id = ? WHERE PRODUCT_ID IN ? AND PRIMARY_PRODUCT_CATEGORY_ID = ?
});

i have a loop that reads in info from a .txt file. It's made to transfer a product from one product category to another. It reads in the product id, the old cat, and the new cat from a txt file using a comma as a delimeter. While it passes through the loop it updates 2 tables with the information. It's not complicated at all. Now if i delete all the products in the .txt file except for 1 (i.e. 1 product 1 old cat 1 new cat) it works fine. If i have more than 1 line it complains and tells me that i'm violating a foreign key constraint. Initially i thought it was an error in the data (like a typo) and that it was trying to update the category to one that doesn't exist (hense failing the FK contraint) but its a flawless data file. So I was wondering is something like this possible to do in Perl? Or am i gonna have to do it 1 at a time...

 

[YankeePride13]

my $update_pcm = $dbh->prepare(qq{
    UPDATE PRODUCT_CATEGORY_MEMBER SET product_Category_Id = ?, FROM_DATE = NOW() WHERE PRODUCT_ID = ? AND PRODUCT_CATEGORY_ID = ?
});

my $update_product = $dbh->prepare(qq{
    UPDATE PRODUCT SET primary_product_Category_Id = ? WHERE PRODUCT_ID IN ? AND PRIMARY_PRODUCT_CATEGORY_ID = ?
});

find/replace typo lol

 

[YankeePride13]

my $update_pcm = $dbh->prepare(qq{
    UPDATE PRODUCT_CATEGORY_MEMBER SET product_Category_Id = ?, FROM_DATE = NOW() WHERE PRODUCT_ID = ? AND PRODUCT_CATEGORY_ID = ?
});

my $update_product = $dbh->prepare(qq{
    UPDATE PRODUCT SET primary_product_Category_Id = ? WHERE PRODUCT_ID = ? AND PRIMARY_PRODUCT_CATEGORY_ID = ?
});

found another =/ the code wasn't like this when i ran it

 

[travs69]

hmm.. can you post the surrounding code and a little bit of the input?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;