http://www.perlmonks.org?node_id=1177486

sachin raj aryan has asked for the wisdom of the Perl Monks concerning the following question:

my $dir = "tt"; my $date = "dd"; my $brnchid = "kk"; my $path = join "/",$dir,$date; my $dbfile = "xxx.xx.xx.xx"; my $dsn = "dbi:Oracle:host=$dbfile"; my $user = "ma"; my $password = "mama"; my $dbh = DBI->connect('dbi:Oracle:host=xx.xx.xx.xx;sid=orcl;port=1521 +;SERVER=POOLED',$user,$password, { PrintError => 1, RaiseError => 1, AutoCommit => 1, FetchHashKeyName => 'NAME_lc', TraceLevel => 4, }) or die "Canot create Databse Handle: $DBI::errstr() \n"; chdir ($path); open(my $fh,'< nwsa.txt') or die "Could not open file $!"; while (my $row1 = <$fh>) { if ($row1=~/^[AL]/) { my @sachin = split//,$row1; my $f_column = join ("",$sachin[0],$sachin[1],$sachin[2],$ +sachin[3],$sachin[4],$sachin[5],$sachin[6]); my $s_column = join ("",$sachin[7],$sachin[8],$sachin[9],$ +sachin[10],$sachin[11],$sachin[12],$sachin[13],$sachin[14],$sachin[15 +],$sachin[16],$sachin[17],$sachin[18],$sachin[19],$sachin[20],$sachin +[21],$sachin[22],$sachin[23],$sachin[24],$sachin[25],$sachin[26],$sac +hin[27],$sachin[28],$sachin[29],$sachin[30],$sachin[31],$sachin[32],$ +sachin[33],$sachin[34],$sachin[35],$sachin[36],$sachin[37],$sachin[38 +],$sachin[39],$sachin[40],$sachin[41],$sachin[42],$sachin[43],$sachin +[44],$sachin[45],$sachin[46],$sachin[47],$sachin[48],$sachin[49],$sac +hin[50],$sachin[51],$sachin[52],$sachin[53],$sachin[54],$sachin[55],$ +sachin[56],$sachin[57],$sachin[58],$sachin[59],$sachin[60],$sachin[61 +],$sachin[62],$sachin[63],$sachin[64],$sachin[65],$sachin[66],$sachin +[67],$sachin[68],$sachin[69],$sachin[70],$sachin[71],$sachin[72],$sac +hin[73],$sachin[74],$sachin[75],$sachin[76],$sachin[77]); my $trd_column = join("",$sachin[85],$sachin[86],$sachin[8 +7],$sachin[88],$sachin[89],$sachin[90],$sachin[91],$sachin[92],$sachi +n[93],$sachin[94],$sachin[95],$sachin[96],$sachin[97],$sachin[98],$sa +chin[99],$sachin[100],$sachin[101]); my @newdigit=split/[,]+/,$trd_column; my $string = join('', map { "$_" } @n +ewdigit); my $Receipt_Amount=($string*100)/100; print "$Receipt_Amount\n"; my $fourth_column = join("",$sachin[106],$sachin[107],$sac +hin[108],$sachin[109],$sachin[110],$sachin[111],$sachin[112],$sachin[ +113],$sachin[114],$sachin[115],$sachin[116],$sachin[117],$sachin[118] +,$sachin[119],$sachin[120],$sachin[121],$sachin[122],$sachin[123],$sa +chin[124],$sachin[125],$sachin[126]); my @newdigit1=split/[,]+/,$fourth_column; my $string = join('', map { "$_" } @n +ewdigit1); my $Receipt_Amount1=($string*100)/100 +; print "$Receipt_Amount1\n"; print "$f_column,","$s_column,","$Receipt_Amount,","$Recei +pt_Amount1,","$date,","$brnchid \n"; my $sql ="INSERT INTO nwsa (code_id,name_code,inr_col,out_ +col,brnch_id) VALUES (?,?,?,?,?)"; print "$sql\n"; my $sth = $dbh->prepare($sql); $sth = execute($f_column,$s_column,$Receipt_Amount,$Receip +t_Amount1,$brnchid); $sth->finish(); } } $dbh->disconnect(); close ($fh);

not sure what is wrong in the program.. below is my output

C:\Users\lala\Desktop\mama>nwsaparsing.pl DBI::db=HASH(0x1ae41b4) trace level set to 0x0/4 (DBI @ 0x0/0) in +DBI 1.636- ithread (pid 4464) -> STORE for DBD::Oracle::db (DBI::db=HASH(0x1ae41b4)~INNER 'Fetch +HashKeyNam e' 'NAME_lc') thr#126d844 STORE DBI::db=HASH(0x1ae41b4) 'FetchHashKeyName' => 'NAME_lc' <- STORE= ( 1 ) [1 items] at C:/Strawberry/perl/vendor/lib/DBI.pm +line 739 v ia at C:\Users\lala\Desktop\mama\nwsaparsing.pl line 16 -> STORE for DBD::Oracle::db (DBI::db=HASH(0x1ae41b4)~INNER 'ora_d +rcp' 1) th r#126d844 <- STORE= ( 1 ) [1 items] at C:/Strawberry/perl/vendor/lib/DBI.pm +line 739 v ia at C:\Users\lala\Desktop\mama\nwsaparsing.pl line 16 -> connected in DBD::_::db for DBD::Oracle::db (DBI::db=HASH(0x1ae +40c4)~0x1a e41b4 'dbi:Oracle:host=192.168.1.4;sid=orcl;port=1521;SERVER=POOLED' ' +mama' ** ** HASH(0x46b1f4)) thr#126d844 <- connected= ( undef ) [1 items] at C:/Strawberry/perl/vendor/lib +/DBI.pm li ne 746 -> STORE for DBD::Oracle::db (DBI::db=HASH(0x1ae41b4)~INNER 'dbi_c +onnect_clo sure' CODE(0x12829b4)) thr#126d844 STORE DBI::db=HASH(0x1ae41b4) 'dbi_connect_closure' => CODE(0x1282 +9b4) <- STORE= ( 1 ) [1 items] at C:/Strawberry/perl/vendor/lib/DBI.pm +line 755 v ia at C:\Users\lala\Desktop\mama\nwsaparsing.pl line 16 419041001.46 0 L01A ,CURRENT ACCOUNT + , 419041001.46,0,20161128,00336 INSERT INTO nwsa (code_id,name_code,inr_col,out_col,brnch_id) VALUES ( +?,?,?,?,?) -> prepare for DBD::Oracle::db (DBI::db=HASH(0x1ae40c4)~0x1ae41b4 +'INSERT IN TO nwsa (code_id,name_code,inr_col,out_col,brnch_id) VALUES (?,?,?,?,? +)') thr#12 6d844 dbd_preparse scanned 5 distinct placeholders dbd_st_prepare'd sql INSERT ( auto_lob1, check_sql1) dbd_describe skipped for INSERT <- prepare= ( DBI::st=HASH(0x1ae4e14) ) [1 items] at C:\Users\lala +\Desktop\sa chin\nwsaparsing.pl line 54 Undefined subroutine &main::execute called at C:\Users\lala\Desktop\ma +ma\nwsapa rsing.pl line 55, <$fh> line 11. -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x1ae4db4)~INNER) thr +#126d844 <- DESTROY= ( undef ) [1 items] at C:\Users\lala\Desktop\mama\nwsa +parsing.p l line 55 via at C:\Users\lala\Desktop\mama\nwsaparsing.pl line 55 -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x1ae41b4)~INNER) thr +#126d844 <- DESTROY= ( undef ) [1 items] at C:\Users\lala\Desktop\mama\nwsa +parsing.p l line 55 via at C:\Users\lala\Desktop\mama\nwsaparsing.pl line 55 C:\Users\lala\Desktop\mama>

Replies are listed 'Best First'.
Re: not able to insert data in database using perl DBI
by Corion (Patriarch) on Dec 08, 2016 at 09:16 UTC

    The error message is:

    Undefined subroutine &main::execute called at ...

    This originates from this line:

    $sth = execute(...)

    That's not how you should call the ->execute method of a statement handle. Use $sth->... as you do everywhere else.

      Hi sachin,

      I did some minor reformatting of your code and used array splices instead of your rather long assignments

      You already heared of array splices?

      I changed what Corion suggested and also moved the prepare statement out of the loop, because this is the sense of a prepare statement

      I also declared a new srray, because naming something like $column_1 (or $first_column and so on) always looks like an array structure.

      But maybe this naming has to do with your contrieved example.

      use strict; use warnings;; my $dir = "tt"; my $date = "dd"; my $brnchid = "kk"; my $path = join "/", $dir, $date; my $dbfile = "xxx.xx.xx.xx"; my $dsn = "dbi:Oracle:host=$dbfile"; my $user = "ma"; my $password = "mama"; my $dbh = DBI->connect( 'dbi:Oracle:host=xx.xx.xx.xx;sid=orcl;port=1521;SERVER=POOLED', $user, $password, { PrintError => 1, RaiseError => 1, AutoCommit => 1, FetchHashKeyName => 'NAME_lc', TraceLevel => 4, } ) or die "Cannot connect to Database: $DBI::errstr\n"; chdir($path); open( my $fh, '< nwsa.txt' ) or die "Could not open file $!"; my $sql = "INSERT INTO nwsa (code_id,name_code,inr_col,out_col,brnch_i +d) VALUES (?,?,?,?,?)"; print "$sql\n"; my $sth = $dbh->prepare($sql); while ( my $current_row = <$fh> ) { if ( $current_row =~ /^[AL]/ ) { my @sachin = split //, $current_row; my @column; # a fresh array for each row $column[1] = join( "", @sachin[0,6] ); $column[2] = join( "", @sachin[7,77] ); $column[3] = join( "", @sachin[85,101] ); my @newdigit = split /[,]+/, $column[3]; my $string = join( '', map {"$_"} @newdigit ); my $Receipt_Amount = ( $string * 100 ) / 100; print "$Receipt_Amount\n"; $column[4] = join( "", @sachin[106,126] ); my @newdigit1 = split /[,]+/, $column[4]; $string = join( '', map {"$_"} @newdigit1 ); my $Receipt_Amount1 = ( $string * 100 ) / 100; print "$Receipt_Amount1\n"; print "$column[1],", "$column[2],", "$Receipt_Amount,", "$Rece +ipt_Amount1,", "$date,", "$brnchid \n"; my $rc = $sth->execute( $column[1], $column[2], $Receipt_Amoun +t, $Receipt_Amount1, $brnchid ); #$sth->finish(); # counterproductive esp. when m +oving prepare statement out of while loop } } $dbh->disconnect(); close($fh);

      Hope this will work as intended, I have no Oracle host to work with.

      Cheers Bedani

        Your array slices need a slight adjustment. Instead of the comma, you need to use the range operator. Otherwise you'd only be getting 2 of the fields in each statement instead of the full required range.

        $column[1] = join( "", @sachin[0 .. 6] ); $column[2] = join( "", @sachin[7 .. 77] ); $column[3] = join( "", @sachin[85 .. 101] );
Re: not able to insert data in database using perl DBI
by Discipulus (Abbot) on Dec 08, 2016 at 15:38 UTC
    Hello sachin raj aryan and welcome to the monastery!

    you got good replies, next time be sure to copy your base DBI code from a Perl DBI milestone:DBI recipes

    L*

    There are no rules, there are no thumbs..
    Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.