#=====================================================================
+========#
#=====================================================================
+========#
sub do_select_Handset
{
  my ( @rec, $bas_out, $cob_out, @prod_id, @prod_date, @disc, @acc );
  my ( $sth_main, $sth_date, $sth_prod, $sth_acc, $sth_disc, $sth_imei
+ );
  my ( $i, $prod_date, $prod_id, $item, $discount, @imei );
  my ( %attr_prod, %attr_imei, %attr_acc, %attr_disc );
  my $count = 0;
    
  my $allow_active = 1;

    # The main Select
  $sth_main = $dbh->prepare(<<__EOSQL__);
SELECT DISTINCT tele_period.account_no, 
                tele_period.telephony_id, 
                new_sim.sim, 
                new_sim.mobile_number, 
                subscription.user_name, 
                subscription.contract_status, 
                subscription.dealer, 
                to_char(tele_period.eff_start_date,'DD.MM.YYYY'),
                to_char(subscription.signed_date,'DD.MM.YYYY'),
                to_char(subscription.subscr_change_upd,'DD.MM.YYYY'), 
                lpad(subscription.subscription_id,7,'0'), 
                to_char(tele_period.eff_start_date,'HH24:MI:SS')
FROM new_sim, subscription, tele_period 
WHERE (tele_period.account_no   = new_sim.account_no) AND 
      (tele_period.telephony_id = new_sim.mobile_number) AND
      (tele_period.account_no   = subscription.account_no) AND
      (tele_period.telephony_id = subscription.mobile_number)
ORDER BY tele_period.account_no, tele_period.telephony_id
__EOSQL__

    # Date and time
    $sth_date = $dbh->prepare(<<__EOSQL__);
SELECT to_char(sysdate,'DD.MM.YYYY'),
         to_char(sysdate,'HH24:MI:SS') 
FROM org_index
__EOSQL__


     subscription products
    $sth_prod = $dbh->prepare_cached(<<__EOSQL__); 
SELECT lpad(product_id,5,'0'),
       to_char(last_bill_date,'DD.MM.YYYY') 
       FROM subscr_prod 
WHERE (mobile_number = ?) AND 
      (account_no = ?) AND 
      (sub_end_date > sysdate) 
ORDER BY last_bill_date DESC
__EOSQL__

      
    # discount
      $sth_disc = $dbh->prepare_cached(<<__EOSQL__); 
SELECT item_type, 
       disc_percent 
FROM disc_item_index 
WHERE (lpad(real_prod_id,5,'0') = ?)
__EOSQL__


    # IMEI number
    $sql_imei = "SELECT imei "
              . "FROM imei_period "
              . "WHERE (mobile_number = ?) AND "
              . "      (account_no = ?)";
              
    $sth_imei = $dbh->prepare($sql_imei) || &error('prep_sql', $0);
    
    $sth_imei = $dbh->prepare_cached(<<__EOSQL__); 
SELECT imei 
FROM imei_period 
WHERE (mobile_number = ?) AND 
      (account_no = ?)
__EOSQL__



    # Last Bill
    $sth_acc = $dbh->prepare_cached(<<__EOSQL__);
SELECT customer.password, 
       to_char(max(ar_open.bill_date),'DD.MM.YYYY') 
FROM customer, ar_open 
WHERE (customer.account_no = ar_open.account_no) AND 
      (customer.account_no = ?) 
GROUP BY customer.password,
         to_char(sysdate,'DD.MM.YYYY'), 
         to_char(sysdate,'HH24:MI:SS')
__EOSQL__


  # End of SQL pregeneration

  loggit(" ");
  loggit("starting do_select_Handset ...");
  $tabspaces++;

  $sth_date->execute;
  ($sysdate,$systime) = $sth_date->fetchrow_array;
  $sth_date->finish;

  print "\n\nCreating Handset File: $sysdate $systime\n\n";

  $sth_main->execute;

  open(BAS, "> basil_out/new_Handset.bas");
  open(COB, "> cobol_out/new_bthsetin");
  loggit("Open BAS file.");
  loggit("Open COB file.");

  while ((@rec = $sth_main->fetchrow_array)  && ($count < 10))
  {
    if ($rec[4] eq undef) { $rec[4] = '                              '
+; }
    if ($rec[5] eq undef) { $rec[5] = ' '; }
    if ($rec[6] eq undef) { $rec[6] = '        '; }
    if ($rec[7] eq undef) { $rec[7] = '          '; }
    if ($rec[8] eq undef) { $rec[8] = '          '; }
    if ($rec[9] eq undef) { $rec[9] = '          '; }
    if ($rec[11] eq undef) { $rec[11] = '00:00:00'; }

    if ($rec[12] eq undef) { $rec[12] = '                '; }

    $sth_imei->execute($rec[1],$rec[0]) || &error('sql', $0);
    print "SQL: $sql_imei\n";

    @imei = $sth_imei->fetchrow_array;
    print "IMEI : @imei\n";

    if ($imei[0] eq undef) {$imei[0] = '                ';}
    $imei_prefix = substr($imei[0],0,6);

    $bas_out = "$rec[0]|$rec[1]| |$rec[2]|       |0000000|$imei[0]|$re
+c[4]  |13|Y|N|G|";
    $cob_out = "$rec[0]$rec[1] $rec[2]       0000000$imei$rec[4]  13YN
+G";

    $sth_prod->execute($rec[1],$rec[0]);

    for ($i = 0; $i < 15 ; $i++) {
      $prod_id[$i] = '     ';
      $prod_date[$i] = '          ';
    }
    $i = 0;

    while ((@products = $sth_prod->fetchrow_array) && ($i < 15))
    {
      $prod_id[$i] = $products[0];
      $prod_date[$i] = $products[1];

      $sth_disc->execute($products[0]);
      $item = ' ';
      $discount = 0;

      while (@disc = $sth_disc->fetchrow_array) {
        if ($disc[0] eq 'P') {
          if ($item eq 'A') {
            $item = 'L';
            if ($discount > $disc[1]) { $discount = $disc[1]; }
          }
          else {
            $item = $disc[0]; $discount = $disc[1];
          }
        }
        elsif ($disc[0] eq 'A') {
          if ($item eq 'P') {
            $item = 'L';
            if ($discount > $disc[1]) { $discount = $disc[1]; }
          }
          else {
             $item = $disc[0]; $discount = $disc[1];
          }    
        }
      }
      $i++;
    }

    if ($item eq 'P') {$item = 'R';}
    $discount = sprintf("%08.2f",$discount);
    
    $bas_out.= "$item|$discount|Y|N|N| |N|Y|$rec[6]|";
    $cob_out.= $item.$discount."YNN NY$rec[6]";

    for ($i = 0; $i < 15 ; $i++) {
      if ($prod_id[$i] eq undef) { $prod_id[$i] = '00000';}
      $bas_out.= $prod_id[$i]."|".$prod_date[$i]."|";
      $cob_out.= $prod_id[$i].$prod_date[$i];
    }

    $sth_acc->execute($rec[0]);
    @acc = $sth_acc->fetchrow_array;
    print "ACC : @acc\n";    

    if ($acc[0] eq undef) { $acc[0] = '               ';}
    if ($acc[1] eq undef) { $acc[1] = '          ';}
    if ($prod_date[0] eq undef) { $prod_date[0] = '          ';}

    $acc[0] = substr($acc[0],0,15);

    $bas_out.= "$sysdate|$systime|$rec[7]|$rec[11]|$rec[8]|$rec[9]|$ac
+c[1]|$prod_date[0]|".
               "00000|0000075.00|$imei_prefix|$acc[0]|$rec[10]|";
    $cob_out.= "$sysdate$systime$rec[7]$rec[11]$rec[8]$rec[9]$acc[1]$p
+rod_date[0]".
               "000000000075.00$imei_prefix$acc[0]$rec[10]";

    print BAS $bas_out."\n";
    print COB $cob_out."\n";
    $count++;
  }

  loggit("$count lines inserted.");
  $tabspaces--;
  loggit("ending do_select_Handset ...");
  close(BAS);
  close(COB);
}
[download]

Just forget the above code, it is rather useless
My head is certainly not working well today (o:

Ok, to be simplier. I just put the relevant info here. Both SQL statement are prepared_cached, but only the second one is working...
I also tried to add code to track down error message, but nothing is returned )o;
I bet the solution is silly as hell, but I can't find it... maybe need some rest (o;{{


    $sth_imei = $dbh->prepare_cached(<<__EOSQL__); 
SELECT imei 
FROM imei_period 
WHERE (mobile_number = ?) AND 
      (account_no = ?)
__EOSQL__

    $sth_acc = $dbh->prepare_cached(<<__EOSQL__);
SELECT customer.password, 
       to_char(max(ar_open.bill_date),'DD.MM.YYYY') 
FROM customer, ar_open 
WHERE (customer.account_no = ar_open.account_no) AND 
      (customer.account_no = ?) 
GROUP BY customer.password,
         to_char(sysdate,'DD.MM.YYYY'), 
         to_char(sysdate,'HH24:MI:SS')
__EOSQL__

# [..]

$sth_imei->execute($rec[1],$rec[0]);
@imei = $sth_imei->fetchrow_array;
print "IMEI : @imei\n";
# This does not return anything )o;

$sth_acc->execute($rec[0]);
@acc = $sth_acc->fetchrow_array;
print "ACC : @acc\n";    
# This one works fine
[download]

DBI->trace() is your friend and so is $sth->errstr.

First up is $sth->errstr who you really should be using after all your prepare and execute statements like this:

$sth->execute($foo,$bar)
    or die "Couldn't execute query:" . $sth->errstr;
[download]

DBI->trace() is more a development tool, it has multiple levels of tracing for very effecient debugging.

 LEVEL | EFFECT
--------------------------------------------
   0     Disable trace
   1     Execution trace (values and errors)
   2     L1 + method and params
   3     L2 + driver level (very detailed)
   4     An insane amount of information
[download]

The DBI->trace(2, 'tracefile') takes one or two parameters, the first is the trace level as illustrated above, the second is an optional file to dump the debugging log into.

I'm recalling this out of Programming the Perl DBI which is an excellent book, but it's on loan to a friend so I hope the table's correct. I was/am preparing a more detailed post given some of your other code (Oracle DBA here, so this stuff is fun for me) but I'm pretty busy so I thought I'd just share this until I get a few moments

Update: Added the $sth->errstr() stuff then noticed it had already been mentioned by andye

Update 2: Have a few more moments, I'd really like to go over your general query structure but that would take more time than I currently have so let's stick to some little things (BTW brovnick covered a really good point, personally I see $count as very perplexing given you can just iterate over the cursor):

    # The main Select
  $sth_main = $dbh->prepare(<<'__EOSQL__');
SELECT DISTINCT t.account_no, 
                t.telephony_id, 
                n.sim, 
                n.mobile_number, 
                s.user_name, 
                s.contract_status, 
                s.dealer, 
                to_char(t.eff_start_date,'DD.MM.YYYY'),
                to_char(s.signed_date,'DD.MM.YYYY'),
                to_char(s.subscr_change_upd,'DD.MM.YYYY'), 
                lpad(s.s_id,7,'0'), 
                to_char(t.eff_start_date,'HH24:MI:SS')
FROM new_sim n, subscription s, tele_period t
WHERE (t.account_no   = n.account_no) AND 
      (t.telephony_id = n.mobile_number) AND
      (t.account_no   = s.account_no) AND
      (t.telephony_id = s.mobile_number)
ORDER BY t.account_no, t.telephony_id
__EOSQL__
[download]

Notice the single quotes around __EOSQL__? By default here docs are interpolated, you don't want perl to spend the time checking and you certainly don't want it interpolating in this case. So explicitly state it as shown. Also, if you want, you can use table aliases. I handle a lot of queries a day and it's so much faster to type and easier to read when they're used (check the FROM clause to see how it's done).

# using an array ref instead
$rec = $sth_main->fetchrow_arrayref('NAME_lc')
$sth_imei->execute( $rec->[1], $rec->[0] ) 
    or die $sth_imei->errstr;

# using a hash ref
$rec = $sth_main->fetchrow_hashref('NAME_lc');
$sth_imei->execute( $rec->{telephony_id}, $rec->{account_no})
    or die $sth_imei->errstr;
[download]

Using a fetched reference instead of an array is much faster and uses less memory, b/c each time your cursor moves (ie. iterate to the next record) DBI uses the same memory space instead of creating a new array. This would necesitate some major changes to your structure, but if you want more speed you'll probably be doing them anyways.

The hash reference is a bit slower on lookup simply b/c it is a hash, however, the benefits of it should be obvious. Now $rec actually means something instead of being just a number.

P.S. Consider joining, it's painless: It's also much nicer talking to a name than Anonymous Monk

• I suggest you put in error conditions in case the prepare_cached or the execute fails. You can get the database error message using  $dbh->errstr - it's often very helpful.
• Are you sure that you're finishing both of the statement handles? In the larger chunk of code above, there's only one finish.
• You could try doing bind_param separately from execute, in case the problem's there
• In answer to someone's question below, 'clockwork'. ;)
Hope that's even a bit helpful,

andy.

OK, I see nothing obviously wrong in this snippet, but...
(Apologies if this is teaching you to suck eggs)

1. If your main concern is overall speed, I suggest some detailed profiling. Find where the code is spending most of its time. See Benchmark. Use the Benchmark->new and timediff() methods to look at checkpoints. Since you are doing lots of logging anyway, have your loggit() call put a timestamp in the LOG.

• Prepare_cached is only going to save you time if it takes a long time to do the actual prepare and you call it lots of times. It could be that if the datasets are large the time is taken in the execute(), and not the prepare().
• Depending on your DB config, load on the server, and systems could be a factor.
• You seem to be collecting all the values and then only using the first 15 or 10 lines. How about using a LIMIT command to reduce the lines computed/returned by the SQL.
• There may be other SQL/DB efficiency ideas, but I am not the person to ask about that. I expect that a good time profile will help you narrow down the issues better.

2. Have you turned on the error checking in the DB ?

{RaiseError=>1}
[download]

in the connect(). It could be that there is a problem in the prepare() line.

3. Is it only failing if you use prepare_cached ? I.E. does it work without the _cached ?

4. Are you using the $sth->finish() when no longer needed ? This can generate errors if there is an open execute and _cached is called again.

That's all I can think of right now, good luck.
--
Brovnik

The same for me... does that mean noboby uses prepare_cached or simply that it works like a swiss clock for all who use it ? (o;

There is the threat :
Code useless, forget it... this is more usefull (o;
by Anonymous Monk on Jun 22, 2001 at 11:57

which give the piece of code which troubles me... Eric.