How I should write into file? (smart way) 1

[JackTheRussel]

Hi.

I have a program which gets information into database and then the program writes it into a file.

In the beginning the program checks $option1 and $option2 values.

If $option1 == 0 then database query results could for example be something like this:

Def: 2
Aut: 2
Min: 3
Vid: 1

And if the $option1 == 1 then results could be something like this:

Def: 0
Aut: 0
Min: 4 // Def + Aut
Vid: 4 // Min + Vid

And $option2 works like $option1 but "titles" are different.

Yit:
Opt:
Hre:
Tre:

So I run two sql-querys:
$sqlQuery1 and $sql-query2

And I get results into tables:

@SqlQueryResults
@SqlQueryResults2


In this point I need some help. Now when I write query results into file I use Tie:File.

It's working, but It is really messy. I would like to do this a little bit smarter:

tie @array, 'Tie::File', $file or die;

if ($option1 == 0){

$array[0] = "Def: $SqlQueryResults[0];
$array[1] = "Aut: $SqlQueryResults[1];
$array[2] = "Min: $SqlQueryResults[2];
$array[3] = "Vid: $SqlQueryResults[3];
}
elsif ($option1 == 1){

$array[0] = "Def: 0;
$array[1] = "Aut: 0;
$array[2] = "Min: $SqlQueryResults[0];
$array[3] = "Vid: $SqlQueryResults[1];
}
else { print "Something is wrong\n"; }

if ($option2 == 0){

$array[4] = "Yit: $SqlQueryResults2[0];
$array[5] = "Opt: $SqlQueryResults2[1];
$array[6] = "Hre: $SqlQueryResults2[2];
$array[7] = "Tre: $SqlQueryResults2[3];
}

elsif ($option2 == 1){

$array[4] = "Yit: 0;
$array[5] = "Opt: $SqlQueryResults2[0];
$array[6] = "Hre: 0;
$array[7] = "Tre: $SqlQueryResults2[1];
}

else { print "something is wrong\n"; }

I know this is messy question, but ask more if you did't get it.

Thanks.

 

[KevinADC]

I wouldn't use Tie::File for this unless there is another reason you neeed to use it. Besides that minor suggestion, I don't see anything bad or wrong or messy about the code.

- Kevin, perl coder unexceptional!

 

[JackTheRussel]

Ok. Thanks Kevin.

I would have an second question:

If I run query like this:

SELECT name,lastname,age,location FROM table Where....

How can I write into file one record / line ?

I want that my file would look like this:

John    Doe    32    Washington
Mike   Monroe  53    Alaska
Paul   Newton  43    New York

 

[stevexff]

Use DBI's fetchall_arrayref() method. This brings back a reference to an array of arrays containing the whole result set.

Use Data:umper to print out the resulting values, which will show you how they are structured. Then it's just a case of iterating over the resulting structure to print out the rows.

Also (pauses to put on database design head), please tell me that the 'age' column is calculated from the date of birth stored on the table as part of the query, and not actually stored as a physical column?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Steve.

Now my program uses fetchall_arrayref method, but I don't get how I should use Data:umber?. -> Don't understand how it works.

For now on my database calculate the age. I just realize the benefits. Thanks.

 

[stevexff]

use strict;
use warnings;
use Data::Dumper;

...
...

my $ref = $sth->fetchall_arrayref();

print Dumper($ref);

Data:umper is really handy if you have a data structure returned from a function or module, and you want to see what it looks like.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Thanks a lot steve. One more guestion:

So how I can save data into file (one record at line) ?

Now I have reference to an array where all data exist.

open (HANDLE, ">>$file") or die "Couldn't open $file: $!";

print HANDLE " ??? ";

close HANDLE;

 

[stevexff]

Assuming the results of the fetchall_arrayref are in $ref, something like

print HANDLE join("\t", @$_), "\n" foreach (@$ref);

ought to do it (untested).

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[stevexff]

For now on my database calculate the age. I just realize the benefits.

It's a classic database "gotcha". Unless it's a transient table that is going to be rebuilt daily/weekly, calculating the age in the query (you can put this in a view to make life easier) is the way to go.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Thanks steve.

Once again I learned a lot of important things from you.