ravi45722 has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to upload some rows to DB 

 INPUTS
CID,MSISDN,Preference,TYPE,SEVERITY 
"9","9596354636","1","A","2"
"9","9596560722","2","A","2"
"9","9596560722","2#3#4","A","2"
"9","9622526453","3","D","2"

[download]

if it is "A" I need to add that number in the particular category as '1'. If the number is already exists in DB then update it on duplicate key of msisdn 


my $user_sth  = $dbh->prepare("insert into user_master (msisdn,cat0,ca
+t1,cat2,cat3,cat4,cat5,cat6,cat7,vip,SAcode,phone_type) values (?,?,?
+,?,?,?,?,?,?,?,?,?) on duplicate key update ?");

if ( $opty eq "A" )
{
                                if ( $preference =~ tr/0//)   #if pref
+erence is 0 make all category as 0
                                {
                                        $cat{'0'} =1;
                                        $cat{'1'} =0;
                                        $cat{'2'} =0;
                                        $cat{'3'} =0;
                                        $cat{'4'} =0;
                                        $cat{'5'} =0;
                                        $cat{'6'} =0;
                                        $cat{'7'} =0;
                                        $db_update_cmd = "cat0=1"; #,c
+at1=0,cat2=0,cat3=0,cat4=0,cat5=0,cat6=0,cat7=0";
                                }
                                else         #or made that category as
+ one in the DB
                                {
                                        my @all_cat = split(/\#/,$pref
+erence);
                                        foreach my $cat (@all_cat)
                                        {
                                                $cat{$cat} = 1;
                                                $db_update_cmd = $db_u
+pdate_cmd."cat$cat=1,";
                                        }
                                        chop $db_update_cmd;
                                }
my @db_values = (sprintf('%0d', $cat{'0'}), sprintf('%0d',$cat{'1'}),s
+printf('%0d', $cat{'2'}), sprintf('%0d',$cat{'3'}),sprintf('%0d', $ca
+t{'4'}),sprintf('%0d',$cat{'5'}),sprintf('%0d', $cat{'6'}), sprintf('
+%0d',$cat{'7'}));
$user_sth->execute($msisdn,@db_values,0,$SAC,$phone_type,$db_update_cm
+d) or die $DBI::errstr;

[download]
DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near ''cat1=1'' at line 1 at ./dnd_category_mana
+gement_incremental_provisioning.pl line 208, <FD> line 1.

[download]

But it returning this error because it taking $db_update_cmd with ''. For example if the preference is 1 the $db_update_cmd = "cat1=1". If I pass that in execute function it taking as  ...... upadte 'cat1=1'; Due to that single quotes its showing error. How can I simplify this

Replies are listed 'Best First'.
Re: Error in $sth->execute()
by 1nickt (Canon) on Feb 20, 2016 at 03:55 UTC

    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.
[reply]
[d/l]
[select]

      I know the error where it is. And I mentioned that too.

      Its from Module while executing in programm 

       
DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near ''cat0=1,cat1=0,cat2=0,cat3=0,cat4=0,cat5=0
+,cat6=0,cat7=0'' at line 1 at ./dnd_category_management_incremental_p
+rovisioning.pl line 208, <FD> line 1.

      [download]

      Its from DB terminal while I executing manually 

       
mysql> insert into user_master (msisdn,cat0,cat1,cat2,cat3,cat4,cat5,c
+at6,cat7,vip,SAcode,phone_type) values (919596354636,0,1,0,0,0,0,0,0,
+0,0,0)on duplicate key update 'cat0=1,cat1=0,cat2=0,cat3=0,cat4=0,cat
+5=0,cat6=0,cat7=0';
ERROR 1064 (42000): You have an error in your SQL syntax; check the ma
+nual that corresponds to your MySQL server version for the right synt
+ax to use near ''cat0=1,cat1=0,cat2=0,cat3=0,cat4=0,cat5=0,cat6=0,cat
+7=0'' at line 1

      [download]

      If I remove the single quotes the query is executing properly.

      Its very clear that when I passing argument to $sth->execute() its taking the argument and adding '' (single quotes) to the statement. If the single quotes is not there it will execute properly. Why the module adding single quotes to my query. How to make query without adding quotes

      This code is working very fine 

      my $IP              = "172.16.2.163";
my $user            = "root";
my $table           = "user_master";
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$IP;mysql_socket=/
+opt/lampstack-5.5.27-0/mysql/tmp/mysql.sock","$user","",{'RaiseError'
+ => 1});
my $user_sth  = $dbh->prepare("insert into user_master (msisdn,cat0,ca
+t1,cat2,cat3,cat4,cat5,cat6,cat7,vip,SAcode,phone_type) values (?,?,?
+,?,?,?,?,?,?,?,?,?) on duplicate key update cat0=0");
$user_sth->execute($msisdn,@db_values,0,$SAC,$phone_type) or die $DBI:
+:errstr;

      [download]
      
CREATE TABLE `user_master` (
  `msisdn` bigint(20) NOT NULL,
  `cat0` tinyint(1) DEFAULT '0',
  `cat1` tinyint(1) DEFAULT '0',
  `cat2` tinyint(1) DEFAULT '0',
  `cat3` tinyint(1) DEFAULT '0',
  `cat4` tinyint(1) DEFAULT '0',
  `cat5` tinyint(1) DEFAULT '0',
  `cat6` tinyint(1) DEFAULT '0',
  `cat7` tinyint(1) DEFAULT '0',
  `vip` tinyint(1) DEFAULT '0',
  `SAcode` bigint(10) NOT NULL DEFAULT '0',
  `phone_type` bigint(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`msisdn`),
  KEY `num` (`msisdn`)
)

      [download]

      But when I use "?" after duplicate key its not working 

      my $IP              = "172.16.2.163";
my $user            = "root";
my $table           = "user_master";
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$IP;mysql_socket=/
+opt/lampstack-5.5.27-0/mysql/tmp/mysql.sock","$user","",{'RaiseError'
+ => 1});
my $user_sth  = $dbh->prepare("insert into user_master (msisdn,cat0,ca
+t1,cat2,cat3,cat4,cat5,cat6,cat7,vip,SAcode,phone_type) values (?,?,?
+,?,?,?,?,?,?,?,?,?) on duplicate key update ?");
$user_sth->execute($msisdn,@db_values,0,$SAC,$phone_type,$db_update_cm
+d) or die $DBI::errstr;

      [download]
[reply]
[d/l]
[select]

      Ya thank you. I understand what to do now. But as per my requirement

      Example

      "9","9018188855","0","A","2"

      If the preference is zero we have to make all category zero

      "10","7259756747","1#5#7","A","3"

      Here we have three preferences 1,5,7 Now I have to make these categories as '1' in DB and don't change the remaining categories. If I write "ON DUPLICATE KEY UPDATE cat1=? and cat2=? etc...." how can we know the values presented in DB. If I do select and fetch and update it make my program bulky. Any suggestions????

[reply]

        Yes, I thought you would find that to be your next problem.

        Sorry, too tired now to think of a solution that does not involve two queries. It would all depend on the data. How much there is, how dynamic it is. But I think with the right indexes, given the small number of columns in your schema, you could fetch any existing data very quickly if you know a unique user ID. If you can do an efficient check/fetch, then your task of deciding what to pass in the insert/update statement becomes much simpler.

        The way forward always starts with a minimal test.
[reply]

        You could try an update first and if no records are changed do an insert.

        Read more... (3 kB)
        poj
[reply]
[d/l]
Re: Error in $sth->execute()
by Athanasius (Archbishop) on Feb 20, 2016 at 04:14 UTC

    Hello ravi45722,

    You don’t show what values $preference might take, but this line looks strange to me:

    if ( $preference =~ tr/0//)   #if preference is 0 make all category as
+ 0

    [download]

    Say the value of $preference is the string "10405". The transliteration will find the two zero characters, leave them alone (since no replacement is specified), and return a value of 2 — and therefore the if statement will evaluate to true. See the discussion in perlop#Quote-Like-Operators.

    Maybe this is what you intended, but, as I said, it looks strange.

    Hope that helps,

    Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]
      The input is very strict & the preferences data is like 
       0      #if zero comes no other value is present
 1      #single category (it very vary from 1..7)
 1#2#3  #multiple category

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom