Hi ravi45722,

You should put DBI->trace; or $sth->trace; in your code. It will show you all the debug output from the DBI, including the actual SQL statement that your code is passing, so you can see if it's what you thought it was.

You can't assemble your update SQL and pass it in as the value of the single placeholder you've declared in your statement for ON DUPLICATE KEY UPDATE . If you want multiple columns to be affected by the UPDATE, you'll have to include them and assign them placeholders. 

$dbh->prepare("
    insert into user_master (msisdn,cat0,cat1,cat2,cat3,cat4,cat5,cat6
+,cat7,vip,SAcode,phone_type)
                     values (     ?,   ?,   ?,   ?,   ?,   ?,   ?,   ?
+,   ?,  ?,     ?,         ?)
      on duplicate key update cat0=?, cat1=?, cat2=?
");                                                # ^^^ etc.

[download]
(I can see that you will have a problem with this technique when you want to do ON DUPLICATE KEY UPDATE but you don't want to overwrite values that exist for categories that are null in the current insert. It may that you need to step back and reexamine your task, maybe redesign your DB schema, maybe get your input data in a different format. For one example, you might want your database to have a separate table that just stores preferences, one row for each user preference, with cols for user, category, and preference.)

Also I noticed a couple of minor things: 

$db_update_cmd = $db_update_cmd."cat$cat=1,";

[download]
is better written as 
$db_update_cmd .= "cat$cat=1,";

[download]
And 
if ( $preference =~ tr/0//)

[download]
looks like it should be 
if ( $preference =~ /0/ )
# should be a more robust regexp to only capture '0', this is just to 
+show the right operator

[download]

Hope this helps!


The way forward always starts with a minimal test.

In reply to Re: Error in $sth->execute() by 1nickt
in thread Error in $sth->execute() by ravi45722

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.