Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

prepare_cached for DBI::Oracle

by Anonymous Monk
on Jun 22, 2001 at 14:25 UTC ( [id://90672]=perlquestion: print w/replies, xml ) Need Help??

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

Hello, I try to use prepare_cached in order to speed up a subroutine with many sub-SQL statement. But it look rather weird as some of the prepare_cached wor fin, some other return nothing... I cannot include the code... Any good example somewhere ?? Thanks Eric.

Replies are listed 'Best First'.
Re: prepare_cached for DBI::Oracle
by Brovnik (Hermit) on Jun 22, 2001 at 14:45 UTC
    If you can't include the code, it is difficult to help.

    Start with Super Search and look for prepare_cached.
    --
    Brovnik

[reply]
      Ok, here is the code (had to be sure I was able to put it there...) (o: Without prepare_cached, and because the DB I am running it on is HUGE, it takes 6h to produce the output files... I hope that prepare_cached could cut the time by plenty (o; 
      #=====================================================================
+========#
#=====================================================================
+========#
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]
[reply]
[d/l]
        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]
[reply]
[d/l]
      Brother Brovnik,
      I followed your suggestion and... Super Search took me back to this thread! :-D
[reply]
        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.
[reply]
Re: prepare_cached for DBI::Oracle
by andye (Curate) on Jun 22, 2001 at 14:47 UTC 
    
my $sql = 'select foo from bar';

my $sth;
unless ($sth = $dbh->prepare_cached($sql)) {
     #raise an error, finish with this statement
}

    [download]
    Sorry if this is too simplistic - can you provide any more detail of the problem?

    andy.

[reply]
[d/l]

Back to Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: perlquestion [id://90672]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2024-04-24 02:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found