in reply to Re: Using perl to parse a file and update values into a database
in thread Using perl to parse a file and update values into a database

Hi.. I am able to read the file and that was working perfect. I am thinking to use the same code with little modification. tried this as well I will post code for you.. have look once 
my $config = Config::Tiny->read('testcase_.txt');
        foreach my $section (sort keys %$config)
        {
                print "[$section]\n";
                foreach my $parameter ( keys %{$config->{$section}})
                {
                        $parameter = $config->{$section}->{$parameter}
+;
                      
                        #$SQL =~ s/.*\=//g;
                       #print "$SQL\n";
                        my $sth = $dbh->prepare(qq{update $section set
+ $prepare where Id = 101});
                        $sth->execute();
                        $sth->finish();
                        print"$parameter\n";


                }
        }

[download]
Now I have to read like table, column and values..
  • Comment on Re^2: Using perl to parse a file and update values into a database
  • Download Code

Replies are listed 'Best First'.
Re^3: Using perl to parse a file and update values into a database
by 1nickt (Canon) on Jul 08, 2015 at 11:09 UTC

    Looks OK. Would help if you can post your test data file. (UPDATE: I see the file. But It looks like each section is a record, not a table.)

    You should always use placeholders in your calls to DBI. 

    my $sql;
foreach my $table (sort keys %$config) {
  my $sql = qq{ insert into $table ( id, balance ) values ( ?, ? ) };
  my $sth = $dbh->prepare( $sql );
    
  while (my ($col, $val) = each %{$config->{$table}}) {
    $sth->execute( $col, $val );
  }
}

    [download]
    Remember: Ne dederis in spiritu molere illegitimi!
[reply]
[d/l]
      yeah sure, below is the text file: 
      
[Subscriber]
Id=101
Account_balance=2000000
[Subscriber1]
Id1=102
Account_balance1=3000000

      [download]
[reply]
[d/l]
      HI I tried with the '?' placeholders.. still the code is not that flexible
[reply]
      Hi.. Subscriber is the table..Account balance column name and so on. I think your code is not generic which takes only specific values.. consider a text file where I don't know which are the tables, column and values..
[reply]

        Yes, you are quite right, my code was not generic. Because you have not provided a complete picture of what you want.

        First you said that the .ini file contained DB table info in sections. But then the file you posted contained DB records in sections. And you have still not posted a complete working script that I can test on my machine. It is impossible to help until you do this.

        As I have mentioned to you more than once, including this hour, you should slow down. Of course you are anxious to get a resolution, but people are going to get tired of trying to help you if you do not take the steps necessary to help yourself. This includes posting a well-formed question with a complete working script! Please see and review the guide to posting an effective question.

        Yes, it will take you some minutes to make a test script that demonstrates your problem. But then you will get your answer!

        ( It seems you are maybe getting off track with an .ini file for each user, or for each update statement you want to do. How are you creating the .ini files? It would be helpful if you (slow down and) provide a complete description of what you want to do from start to finish. )

        And of course the code can be made more generic, but I still don't know what you are really working with.

        Update: added where clause to SQL example. 

        
foreach my $table( @some_list_of_tables ) {

  ## some code to read another .ini file into $config where
  ## the sections are cols and the k=v pairs are user=new_val
  ## 
  ## (for example!)

  foreach my $col (sort keys %$config) {
    my $sql = qq{ update $table set $col = ? where user = ?};
    my $sth = $dbh->prepare( $sql );
    
    while (my ($user, $new_val) = each %{$config->{$col}}) {
      $sth->execute( $new_val, $user );
    }
  }
}

        [download]
        Remember: Ne dederis in spiritu molere illegitimi!
[reply]
[d/l]

In Section Seekers of Perl Wisdom