OraFAQ Forum: Programming Interfaces » passing values manually

Home » Open Source » Programming Interfaces » passing values manually

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

passing values manually [message #685649]

Mon, 28 February 2022 21:18

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

Currently, i am passing two keymodule entries as input and would like to change process by passing all the keycatmodule_id whatever present in the database.. please let me know how to change the script. I am expecting the values to be passed through this query Quote:

select distinct(keycatmodule_id) from us_item_tbl;

instead Quote:

our @keymodule_id = (8401, 8404);

please help me out.

our @keymodule_id = (8401, 8404);

sub sane {
    my $self = shift;
    my %lookup;
    my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
    my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item,
                                         week_first_mv, week_last_mv
                                     from us_item_tbl i
                                     where keymodule_id = ? });

    for my $key ( @keymodule_id) {
        $sth->execute ($key);
        $sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
        while ($sth->fetch) {
            my $nae = sprintf "%02s%05s%05s", $sys, $vend, $item;
            $upc = sprintf "%014s", $cpu;
            $lookup{$nae}{$cpu} = [$wad, $wlm];
        }
    }
    $dbh->disconnect;
    $$self{'SANE'} = \%lookup;
}

[Updated on: Mon, 28 February 2022 21:22]

Report message to a moderator

Re: passing values manually [message #685651 is a reply to message #685649]

Tue, 01 March 2022 01:47

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Something like:

@keymodule_id = @{$dbh->selectcol_arrayref('select distinct(keycatmodule_id) from us_item_tbl')};

Report message to a moderator

Re: passing values manually [message #685652 is a reply to message #685651]

Tue, 01 March 2022 02:20

arun888
Messages: 100
Registered: June 2015
Location: INDIA

Senior Member

I have tried the below queries and got the keycatmodule output in the array.
Currently, not sure how to pass this array in the sub sane function

sub keymodule {
    my $self = shift;
    my %lookup;
    my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
    my $sth = $dbh->prepare (qq{ select distinct keymodule_id
                                     from us_item_tbl });

        $sth->execute;
 my $arrayref = $sth->fetchall_arrayref();
   foreach (@$arrayref) {
   print "$_\n";
         }

$dbh->disconnect;
}
Output
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff98

Need pass the above array in into the below sub function.i am not sure about it.
Instead of the keymodule_id variable need to pass the @$arrayref value.

 &sane($self);

sub sane {
    my $self = shift;
    my %lookup;
    my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
    my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item,
                                         week_first_mv, week_last_mv
                                     from us_item_tbl i
                                     where keymodule_id = ? });

    for my $key ( @keymodule_id) {
        $sth->execute ($key);
        $sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
        while ($sth->fetch) {
            my $nae = sprintf "%02s%05s%05s", $sys, $vend, $item;
            $upc = sprintf "%014s", $cpu;
            $lookup{$nae}{$cpu} = [$wad, $wlm];
        }
    }
    $dbh->disconnect;
    $$self{'SANE'} = \%lookup;
}

[Updated on: Tue, 01 March 2022 02:23]

Report message to a moderator