in reply to Error in $sth->execute()

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.

Replies are listed 'Best First'.
Re^2: Error in $sth->execute()
by ravi45722 (Pilgrim) on Feb 20, 2016 at 04:24 UTC

    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]
Re^2: Error in $sth->execute()
by ravi45722 (Pilgrim) on Feb 20, 2016 at 04:31 UTC

    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]

In Section Seekers of Perl Wisdom