adding columns to db at runtime and inserting to db

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kanishka1213
    New Member
    • Jul 2009
    • 22
    #1

    adding columns to db at runtime and inserting to db

    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.

    Code:
    #!/perl/bin/perl

use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use File::Find;
use DBI;
use HTML::Scrubber;
use Tie::IxHash;

print header;

my $count=0;
my $lines;


#===============================================================================
#first entry is the database column name and second is the file parameter name which is to be parsed from file
open FILE,"test.txt";
while(<FILE>)
{
    push @query_ref,[split];
}
#print $query_ref[1][1];

close (FILE);
#===============================================================================

#Defining directory of the location of streams
my $dir="C:/type";

#Connection String
my $dbh=DBI->connect('dbi:Oracle:test1','scott','tiger',{RaiseError => 1, AutoCommit => 1})or die "Can't connect to database $DBI::errstr\n";

my %params=();

find(\&edits,$dir);
 
    sub edits
    {
        if (/\.html$/)  #looks for html files in folder
        {
	    $count++;
	    print "<BR>$count)..<BR>";
	   
	    
	     
	    my $scrubber = HTML::Scrubber->new; 	#initialize html scrubber
	    
	    			
	    
	    tie %params,"Tie::IxHash";
	    
	    open(FILE,$File::Find::name) or die $!;
	
	    
	    
	    while(defined(my $line = <FILE>))
            {
		my $line= $scrubber->scrub($line);    #scrub all html Tags
		
		$params{$query_ref[0][1]}{$_} = 1;
		$params{$query_ref[1][1]}={};
		$params{$query_ref[2][1]}{$File::Find::name} = 1;
		
		
		foreach my $key (keys %params)
		{   
		    if($line=~/$key/)
		    {
			print "<br>$line";  #[B]when age: 23 appears it prints #correctly[/B]
			if($line =~ m/: (.*)/)
			{					
			    $params{$key}{$1} = 1;		#[B]<<<==i think here is #prob[/B]
			}
		    }
		}    
	    #------------------SPECIAL CASES------------------------------------------------
		    
	    }
	    
	}
	    
	# Transform hash data into 'xxx,yyy,zzz' forms as needed
	foreach my $key (keys %params)
	{
	    $params{$key} = join(',', keys(%{$params{$key}}));
		
	    if (!$params{$key})
	    {
		$params{$key} = 'Absent';
	    }	
	}
	    
	for my $key ( keys %params ) 
	{
	   my $value = $params{$key};
	   print "<Br>$key => $value<BR>";
	}
	
	 
	    my $query = "insert into pooja values ('" .join("','", map { $params{$$_[1]} } @query_ref) . "')";
	    my $sql=qq{$query};
	    my $sth=$dbh->prepare($sql);
	    $sth->execute();
	      close(FILE);
	}
               
    
print "<br>$count files updated"; 
$dbh->disconnect if defined($dbh);
    Tags: None
    • nithinpes
      Recognized Expert Contributor
      • Dec 2007
      • 410
      #2
      The problem is in this line:
      Code:
      $params{$query_ref[1][1]}={};
      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:
      Code:
      $params{$key}{$1} = 1;
      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:

      Code:
      $params{$query_ref[1][1]}={};

while(defined(my $line = <FILE>))

        Comment

        • kanishka1213
          New Member
          • Jul 2009
          • 22
          #3
          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.

            Comment

            • kanishka1213
              New Member
              • Jul 2009
              • 22
              #4
              hey.. in addition to your suggestion for shifting that line up it was also
              tie %params,"Tie::I xHash"; 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::I xHash";
              find(\&edits,$d ir);

              thankyou so much for your help. it works very well now for all files :-) :-)

                Comment

                Previous template Next
                Working...