not able to insert data in database using perl DBI

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);
[download]

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>
[download]

Comment on not able to insert data in database using perl DBI Select or Download Code

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 ...` [download] This originates from this line: `$sth = execute(...)` [download] That's not how you should call the `->execute` method of a statement handle. Use `$sth->...` as you do everywhere else.	[reply] [d/l] [select]
Re^2: not able to insert data in database using perl DBI by Anonymous Monk on Dec 08, 2016 at 14:29 UTC
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); [download] Hope this will work as intended, I have no Oracle host to work with. Cheers Bedani	[reply] [d/l]
Re^3: not able to insert data in database using perl DBI by fishmonger (Chaplain) on Dec 08, 2016 at 15:36 UTC
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] );` [download]	[reply] [d/l]
Re: not able to insert data in database using perl DBI by Discipulus (Canon) 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.	[reply] [d/l]


Perl-Sensitive Sunglasses
	PerlMonks