Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

not able to insert data in database using perl DBI

by sachin raj aryan (Acolyte)
on Dec 08, 2016 at 09:03 UTC ( #1177486=perlquestion: print w/replies, xml ) Need Help??

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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1177486]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (7)
As of 2022-05-20 14:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (73 votes). Check out past polls.

    Notices?