in reply to Perl DBI execute statement

You have been told repeatedly not to use IGNORE in your prepare statement. Why do you keep doing that? I am curious about that point.

"My insert statement for staging would be below:"

my $ext_insert = $dbh3->prepare("INSERT IGNORE INTO $feed_table_ext (id_code, item_name, item_value, item_data) VALUES (?, ?, ?, ?)") or die $dbh3->errstr;

Update:
It is difficult for me to understand your objective, but perhaps something close to this is what you mean? 
====== following your code =====
use Data::Dumper;

#get Id_Codes based on entry time
#
my $feed_table_idcode = 
        $dbh2->prepare("SELECT id_code 
                        FROM $feed_table 
                        WHERE entry_time >= ?");

#get all columns for each id_code in another table                    
+   
my $feed_table_ext_results = 
         $dbh2->prepare("SELECT * 
                         FROM $feed_table_ext 
                         WHERE id_code = ?"); 
                        
$feed_table_idcode->execute($time);

while (my @row = $feed_table_idcode->fetchrow_array() )
{
    my $id_code = $row[0];
    
    $feed_table_ext_results->execute($id_code);
    print Dumper $feed_table_ext_results,"\n";
}

[download]
Since these tables are both in the same DB, I think that some type of SQL JOIN will get the job done. Without a test DB, I won't attempt it, but that appears plausible to me.

Replies are listed 'Best First'.
Re^2: Perl DBI execute statement
by cbtshare (Monk) on Dec 12, 2016 at 19:16 UTC

    Thank you, the code is what I wanted, but I have figured out my issue, I get the following error: 

    DBD::mysql::st fetchrow_array failed: fetch() without execute() at /ho
+me/rdscopy.pl line 204.
DBD::mysql::st fetchrow_array failed: fetch() without execute() at /ho
+me/rdscopy.pl line 204.

    [download]

    when there is no data in the table facebook_321_ext table in production, so the query reutrns nothing and then the insert statement below " while (my @insert_ext = $feed_table_ext_results->fetchrow_array())" which is line 204 gives that error, but it works when data is there upon other test. 

     while (my @insert_ext = $feed_table_ext_results>fetchrow_array())
                                        {
         
                  $ext_insert->execute(@insert_ext) ;
                                        }

    [download]
    Is there a way I can error check before executing?
[reply]
[d/l]
[select]

      as poj has already pointed out "I suspect there are other problems" but you have at least successfully identified that you get that error when $feed_table_idcode->fetchrow_array does not return any rows, because then $feed_table_ext_results->execute never gets executed. One way to test if it has returned any rows is to set some sort of flag to false as a default state, and then set it to true if $feed_table_idcode->fetchrow_array did return a row, (and $feed_table_ext_results->execute gets executed). Then you can skip the $feed_table_ext_results>fetchrow_array loop if your flag is still false

      for instance $did_ex_flag=0; sets a flag to false, and $did_ex_flag=1; sets it to true, and if ($did_ex_flag) { ...} can be used to test it.

      another way, that might be more useful, would be to move the $feed_table_ext_results->fetchrow_array loop to right after the $feed_table_ext_results->execute statement. Of course you now realize the $feed_table_ext_idcode = statement needs to be before that point.

[reply]
[d/l]
[select]
        Ok, thank you , will try using the flags method .
[reply]
      Is there a way I can error check before executing?

      Yes, but I suspect there are other problems. How many lines are there in the whole script ? You need to show how the 3 execute statement are nested together

      poj
[reply]

        currently there are 312 lines.

        My insert statements are 
            #Prepare insert statement for feed table 
    $sth_insert = $dbh3->prepare("INSERT IGNORE INTO $feed_table (id_c
+ode, entry_time, parent_id_code, author_name, author_code, author_url
+, author_image_url, entry_url, entry_types, status_code, entry_text, 
+entry_data, last_update, pull_time, queue_code) VALUES (?, ?, ?, ?, ?
+, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)") or die $dbh3->errstr;

    ##prepare insert statement for feed table ext
    my $ext_insert = $dbh3->prepare("INSERT IGNORE INTO $feed_table_ex
+t (id_code, item_name, item_value, item_data) VALUES (?, ?, ?, ?)") o
+r die $dbh3->errstr;

    ##prepare insert table for feed table category
    my $cat_insert = $dbh3->prepare("INSERT IGNORE INTO $feed_table_ca
+tegory (id_code, category_class_code, category_code, user_name, cat_t
+ime) VALUES (?, ?, ?, ?, ?)") or die $dbh3->errstr;

    ##prepare insert table for feed table log    
    my $log_insert = $dbh3->prepare("INSERT IGNORE INTO $feed_table_lo
+g (id_code, action_time, action_usec, action_code, action_type, user_
+id, processor_id, action_text) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") or d
+ie $dbh3->errstr;


#get table results from production stream table
my $activate_info= $dbh->prepare("SELECT source_code, stream_code, cli
+ent_id, stream_oauth_id, name, url, status_code, stream_data, feed_db
+, feed_table, last_update, report_db, sla, exclude_request, owned, pu
+sh FROM stream WHERE id = ?");

$activate_info->execute($client_ID);

##prepare the insert statement 
 $activate_stream = $dbh4->prepare("INSERT INTO stream (source_code, s
+tream_code, client_id, stream_oauth_id, name, url, status_code, strea
+m_data, feed_db, feed_table, last_update, report_db, sla, exclude_req
+uest, owned, push) VALUES(?, ?, 10, 1, ?, ?, \'DISABLED\', ?, \'socia
+l_patrol_feed002\', ?, ?, ?, ?, ?, ?, ?)");

                            

##Insert statement execution
print "Dropping $feed_table table..\n";
#sleep(3);
$dbh3->do("DROP TABLE IF EXISTS $feed_table");            
print "Recreating $feed_table table..\n";
#sleep(3);
my $qy = $dbh2->prepare("SHOW CREATE TABLE $feed_table");
$qy->execute();

my $feed_table_create = $qy->fetchrow_array();

$dbh3->do("$feed_table_create");        
print "Inserting data into $feed_table table..\n";
#sleep(3);
while (my @insert = $table_results->fetchrow_array())
                {
$sth_insert->execute(@insert) or die $sth_insert->errstr;
                }
#$sth_insert->finish();

##Insert ext execution
print "Dropping $feed_table_ext table..\n";
#sleep(3);
#$dbh3->do("DROP TABLE IF EXISTS $feed_table_ext");
                    
print "Recreating $feed_table_ext table..\n";
#sleep(3);

#my $qy_ext = $dbh2->prepare("SHOW CREATE TABLE $feed_table_ext");

#$qy_ext->execute();

#my $feed_table_ext_create = $qy_ext->fetchrow_array();
#  $dbh3->do("$feed_table_ext_create");

print "Inserting data into $feed_table_ext table..\n";
#sleep(3);
while (my @insert_ext = $feed_table_ext_results->fetchrow_array())
{
#print "@insert_ext\n";
$ext_insert->execute(@insert_ext) ;
                            }
#    $ext_insert->finish();

##Insert category execution
print "Dropping $feed_table_category table..\n";
#sleep(3);
$dbh3->do("DROP TABLE IF EXISTS $feed_table_category");

print "Recreating $feed_table_category table..\n";
#sleep(3);
my $qy_cat = $dbh2->prepare("SHOW CREATE TABLE $feed_table_category");
$qy_cat->execute();
my $feed_table_cat_create = $qy_cat->fetchrow_array();
        
$dbh3->do("$feed_table_cat_create");
        
print "Inserting data into $feed_table_category table..\n";
  #sleep(3);
while (my @insert_cat = $feed_table_category_results->fetchrow_array()
+)
    {
$cat_insert->execute(@insert_cat) or die $sth_insert->errstr;
          }
#        $cat_insert->finish();

##Insert log table execution
print "Dropping $feed_table_log table..\n";
  #sleep(3);
$dbh3->do("DROP TABLE IF EXISTS $feed_table_log");
                                                
print "Recreating $feed_table_log table..\n";
     #sleep(3);
my $qy_log = $dbh2->prepare("SHOW CREATE TABLE $feed_table_log");
 $qy_log->execute();
 my $feed_table_log_create = $qy_log->fetchrow_array();
                                    
       $dbh3->do("$feed_table_log_create");
                
print "Inserting data into $feed_table_log table..\n";
 #sleep(3);
            
while (my @insert_log = $feed_table_log_results->fetchrow_array())
 {
 $log_insert->execute(@insert_log) or die $sth_insert->errstr;
                                          }
#            $log_insert->finish();




##Prepare insert statement to activate stream
if($sth_insert->errstr)
{
print "The insert statement failed for data $sth_insert->errstr";
        }
     else
                {
            ##Insert statement execution to activate stream
        #print "Will now delete data from stream table with client ID 
+$client_ID\n";
                                
#my $stream_delete = $dbh4->prepare("DELETE FROM stream WHERE id = ?")
+;
#$stream_delete->execute($client_ID);

    
print "Inserting data into STREAM table..\n";
                                           sleep(3);    
    while (my  @activate_results =  $activate_info->fetchrow_array() )
                            {
                                                 $activate_stream->exe
+cute(@activate_results[0, 1, 4, 5, 7, 9 .. 15]) or die $activate_stre
+am->errstr;
                                                                   }  
+  
                
    
                                }
                                #$activate_stream->finish();

        [download]
[reply]
[d/l]
      This error message is saying that: 
      my $nRows_affected = $feed_table_ext_results->execute(..)

      [download]
      was never executed. Why that is, I don't know at the moment, but it appears that there a number of posts that address this issue.

      Please adjust the formatting of your code to something similar to this: 

      while (my @insert_ext = $feed_table_ext_results->fetchrow_array())
{
   $ext_insert->execute(@insert_ext) ;
}

      [download]
      Instead of what you posted: 
      while (my @insert_ext = $feed_table_ext_results>fetchrow_array())
                                        {
         
                  $ext_insert->execute(@insert_ext) ;
                                        }

      [download]
      The Perl compiler doesn't care about huge indentations of the braces, but the humans do care.
[reply]
[d/l]
[select]
Re^2: Perl DBI execute statement
by cbtshare (Monk) on Dec 12, 2016 at 17:41 UTC
    thank you for your reply, I use IGNORE, I am copying data from production to staging, and during this process, sometimes data will already be in staging and INSERT IGNORE will then not give me a duplicate key error. I will review and try to implement your code, it makes, sense.
[reply]
      I am not a DB guru, but a failed INSERT sounds like a pretty big deal to me. Example: (primary_key,b,c,d). If an insert of that record fails because the primary_key exists, what about the values of b,c,d? Maybe this "INSERT" had new values for b,c,d?

      I would think that instead of an INSERT, an UPDATE might be more appropriate in your situation? If the UPDATE fails because no record with that primary key exists, then you INSERT a brand new record with that primary key and data fields, otherwise if the UPDATE succeeds, the data fields other than primary are updated.

[reply]
        correct it has the same id_code which is the primary key, but new data, I am not sure about the clause to update if primary exist and insert if it doesn't.
[reply]

In Section Seekers of Perl Wisdom