By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

adding columns to db at runtime and inserting to db

P: 22
file test.txt where first entry is the name in db columns and second is name that is in html files used for parsing.
i make a search using the second elements on every line
name and stream location has a _ before and after becuause i dnt parse them from file . i simply assign them value /

name _Name_
age age
stream_loc _Stream Location_
html file to be parsed is a simple file
<html><body> age: 2<br></body></html>
i can print the line where age;2 occured but somehow when i extract it, it doesnt go into db as 2 but as "absent".

this means the html file entries are not being parsed.or if parsed but not assinged to the hash variable ..

there is no problem with first and last element i can get their values correctly in db. but not for age.

Expand|Select|Wrap|Line Numbers
  1.  
  2. #!/perl/bin/perl
  3.  
  4. use CGI qw(:standard);
  5. use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
  6. use File::Find;
  7. use DBI;
  8. use HTML::Scrubber;
  9. use Tie::IxHash;
  10.  
  11. print header;
  12.  
  13. my $count=0;
  14. my $lines;
  15.  
  16.  
  17. #===============================================================================
  18. #first entry is the database column name and second is the file parameter name which is to be parsed from file
  19. open FILE,"test.txt";
  20. while(<FILE>)
  21. {
  22.     push @query_ref,[split];
  23. }
  24. #print $query_ref[1][1];
  25.  
  26. close (FILE);
  27. #===============================================================================
  28.  
  29. #Defining directory of the location of streams
  30. my $dir="C:/type";
  31.  
  32. #Connection String
  33. my $dbh=DBI->connect('dbi:Oracle:test1','scott','tiger',{RaiseError => 1, AutoCommit => 1})or die "Can't connect to database $DBI::errstr\n";
  34.  
  35. my %params=();
  36.  
  37. find(\&edits,$dir);
  38.  
  39.     sub edits
  40.     {
  41.         if (/\.html$/)  #looks for html files in folder
  42.         {
  43.         $count++;
  44.         print "<BR>$count)..<BR>";
  45.  
  46.  
  47.  
  48.         my $scrubber = HTML::Scrubber->new;     #initialize html scrubber
  49.  
  50.  
  51.  
  52.         tie %params,"Tie::IxHash";
  53.  
  54.         open(FILE,$File::Find::name) or die $!;
  55.  
  56.  
  57.  
  58.         while(defined(my $line = <FILE>))
  59.             {
  60.         my $line= $scrubber->scrub($line);    #scrub all html Tags
  61.  
  62.         $params{$query_ref[0][1]}{$_} = 1;
  63.         $params{$query_ref[1][1]}={};
  64.         $params{$query_ref[2][1]}{$File::Find::name} = 1;
  65.  
  66.  
  67.         foreach my $key (keys %params)
  68.         {   
  69.             if($line=~/$key/)
  70.             {
  71.             print "<br>$line";  #when age: 23 appears it prints #correctly
  72.             if($line =~ m/: (.*)/)
  73.             {                    
  74.                 $params{$key}{$1} = 1;        #<<<==i think here is #prob
  75.             }
  76.             }
  77.         }    
  78.         #------------------SPECIAL CASES------------------------------------------------
  79.  
  80.         }
  81.  
  82.     }
  83.  
  84.     # Transform hash data into 'xxx,yyy,zzz' forms as needed
  85.     foreach my $key (keys %params)
  86.     {
  87.         $params{$key} = join(',', keys(%{$params{$key}}));
  88.  
  89.         if (!$params{$key})
  90.         {
  91.         $params{$key} = 'Absent';
  92.         }    
  93.     }
  94.  
  95.     for my $key ( keys %params ) 
  96.     {
  97.        my $value = $params{$key};
  98.        print "<Br>$key => $value<BR>";
  99.     }
  100.  
  101.  
  102.         my $query = "insert into pooja values ('" .join("','", map { $params{$$_[1]} } @query_ref) . "')";
  103.         my $sql=qq{$query};
  104.         my $sth=$dbh->prepare($sql);
  105.         $sth->execute();
  106.           close(FILE);
  107.     }
  108.  
  109.  
  110. print "<br>$count files updated"; 
  111. $dbh->disconnect if defined($dbh);
  112.  
  113.  
  114.  
  115.  
Aug 17 '09 #1
Share this Question
Share on Google+
3 Replies


nithinpes
Expert 100+
P: 410
The problem is in this line:
Expand|Select|Wrap|Line Numbers
  1. $params{$query_ref[1][1]}={};
  2.  
With each iteration of the while loop, value for the key 'age' will be re-assigned to an anonymous hash, overwriting the value (key-value pair) assigned in:
Expand|Select|Wrap|Line Numbers
  1. $params{$key}{$1} = 1; 
  2.  
The structure for "age" key is different from others in your script. If that is how you want it to be, define that line before while loop:

Expand|Select|Wrap|Line Numbers
  1. $params{$query_ref[1][1]}={};
  2.  
  3. while(defined(my $line = <FILE>)) 
  4.  
Aug 20 '09 #2

P: 22
Thanks for the reply. i did that . but then "Age" doesnt show up at all in the final hash entries. i can just see two values Name and stream location

also i noticed one more thing. with the initial same code if i input a text file it works very well and fetches age 's value too.

then y not in html. as u can see i have used scrubber package too that scrubs all html tags.
Aug 20 '09 #3

P: 22
hey.. in addition to your suggestion for shifting that line up it was also
tie %params,"Tie::IxHash"; function which i had placed inside the sub/ it should be in the declaration part exactly after declaration of hash.

my %params;
tie %params,"Tie::IxHash";
find(\&edits,$dir);

thankyou so much for your help. it works very well now for all files :-) :-)
Aug 20 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.