Populate date from file into database 1

[proggybilly]

I have been working on a script, and have tried searching for what I am looking for but feel i may be asking the wrong question.

I can open a directory, pull a list of files, and open each of those files into an array and display them on my screen.

I need now to input some of the data in the file into a database. I thought I had the right syntax but I am unable to get it to work. My code follows:

## Populate Database from txt files
$dir = "/u/u/msilog";
opendir(dir, $dir) or die("Cannot open directory");
@files = grep !/^\./, readdir dir;
closedir dir;

foreach my $file(@files){
 $path = "$dir/$file";
 open(fi, $path);
 @file =<fi>;
 $myquery = $dbh->do("INSERT INTO backup(server, filename, num_files) values('@file[1]','@file[3]','@file[5]')");
 $myquery->execute();
}

 

[max1x]

what Perl module are you using and have read it's HOW-TO?

 

[toolic]

I know nothing about databases, but if you are trying to interpolate variable values into a quoted string, you must not use single quotes. For example, '@file[1]' returns the literal string, rather than the value which is in element 1 of the @file array.

If you are really trying to access array values, then you should use $files[1], for example, as use warnings would tell you.

A couple other tips:

readdir returns a list of all files and sub-directories.
To grab just files (whose names do not begin with a dot), use:

@files = grep { -f "$dir/$_" and !/^\./ } readdir dir;

Always check the return status of open.

 

[proggybilly]

I am using the DBI Module.

#!/usr/bin/perl
use DBI;
# Connect to database
$dsn = "DBI:mysql:database=msilog;host=server1";
$dbh = DBI->connect($dsn, 'root', 'inox-1a', {'RaiseError' => 1});

## Populate Database from txt files
$dir = "/u/u/msilog";
opendir(dir, $dir) or die("Cannot open directory");
@files = grep !/^\./, readdir dir;
closedir dir;

foreach my $file(@files){
 $path = "$dir/$file";
 open(fi, $path);
 @file =<fi>;
 $myquery = $dbh->do("INSERT INTO backup(server, filename, num_files) values('@file[1]','@file[3]','@file[5]')");
 $myquery->execute();
}

I have tried several ways with single quotes and no quotes.. I am getting an error that says something is wrong with my mysql syntax.

 

[Annihilannic]

Maybe print out the generated SQL before executing it so you (and we, if you post it) can verify. Could it just be a missing semi-colon on the end of the statement? I'm not sure that' srequired by the DBI interface...

Annihilannic.

 

[Kirsle]

 $myquery = $dbh->do("INSERT INTO backup(server, filename, num_files) values('[red]$[/red]file[1]','[red]$[/red]file[3]','[red]$[/red]file[5]')");

When using an element from an array use $ instead of @. @ is only used when creating an array or when you're referring to the array as a whole and not the individual items (e.g. foreach(@array), map{}@array, sort{}@array etc etc)

My guess is that this is the biggest problem in the code, cuz @array in double quotes makes it print all values inside the array, separated by spaces... which no doubt would break your SQL line with errors.

Kirsle.net | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[Annihilannic]

Eagle eyes!

Annihilannic.

 

[proggybilly]

Thanks y'all, Kirsle got it right.. Once I made that change it worked great! Good to have a forum where people can pick things out like that for new guys like me!

Have a GREAT day all of you!

 

[proggybilly]

Maybe y'all can help me with another issue.

Now that my database is populated, I need to find the location of a file pulled from my data that is set in variable $file[3]. I am executing:

$find_file = system "find /u/u -name $file[3]";
 print $find_file;

But when I run it, I get this displaying:

/u/u/rsync/backup/mail0/mail0200910112010.tbz2
0/u/u/rsync/backup/sql2/sql2200910122209.tbz2
0/u/u/rsync/backup/vpn-f/vpn-f200910110410.tbz2
0/u/u/rsync/backup/web/web-200910122242.tbz2

As you can see, the 0 is being tossed on the beginning of every line after the first line.. I do not understand this.
Can you help?

 

[proggybilly]

Scratch that call for help, I figured it all out and decided to do it this way.

$myquery = $dbh->prepare("SELECT filename, size, num_files from backup");
$myquery->execute();
while (chomp(@row = $myquery->fetchrow_array)){
 chomp($find_files = qx(find /u/u -name $row[0]));

}

Gets me what I want

 

[Kirsle]

$find_file = system "find /u/u -name $file[3]";
 print $find_file;

system is the wrong command to use here; system just returns the exit code of the program (where 0 means no errors in the program). So what you saw in your terminal,

[red]/u/u/rsync/backup/mail0/mail0200910112010.tbz2[/red]
[blue]0[/blue][red]/u/u/rsync/backup/sql2/sql2200910122209.tbz2[/red]
[blue]0[/blue][red]/u/u/rsync/backup/vpn-f/vpn-f200910110410.tbz2[/red]
[blue]0[/blue][red]/u/u/rsync/backup/web/web-200910122242.tbz2[/red]

The blue is what your Perl script was printing (the exit code 0 of the system commands); the red was actually printed by the find command, and not your Perl script (your Perl script therefore doesn't actually have access to what find printed here; find just sent it to the same terminal as Perl and that's it).

What you probably wanted here was backticks ``

$find_file = `find /u/u -name $file[3]`;
 print $find_file;

backticks execute a command like system, but return the command's STDOUT when done. If you need STDERR also,

$find_file = `find /u/u -name $file[3] [blue]2>>&1[/blue]`;
 print $find_file;

2>>&1 redirects STDERR to STDOUT (there's a few other tricks like this, but you can find more info about those by googling about the bash shell for example; anything that goes into these system commands could be typed into a terminal yourself and run).

$? always contains the exit code of the last-executed command, so if you use backticks to get the command's output but also wanted to get its exit code, just check $?

Kirsle.net | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'