How to replace fetchrow_array() 1

[JackTheRussel]

Hi all.

I run sql-query (select time, values from table)
and I get this kind of results:

| time                | value |
+---------------------+-------+
| 2006-12-15 08:51:16 | 14502 |
| 2006-12-15 08:53:43 | 14542 |
| 2006-12-15 08:59:02 | 14678 |

My old program used to use fetctrow_array() function
and it worked fine.

Here I get sql-results and print them into file:

my $sth = $db_connection->prepare($sql_clause);
$sth->execute();
while(@results=$sth->fetchrow_array()){
    
open (SESAME, ">>$filename");
print SESAME "$results[0] $results[1]\n";
}
close SESAME;

BUT. Now I use functions to get results from database and I
"can't" use fetchrow_fuction() anymore.

my @results = &function_select($sql_clause);

How can I print data into file in same way, when not using
fetchrow_array() function ?

 

[KevinADC]

if @results returns the same data as before:

my @results = &function_select($sql_clause);
open (FH, ">>$filename")  or die "$!";
print FH "$results[0] $results[1]\n";
close FH;

- Kevin, perl coder unexceptional!

 

[stevexff]

Jack

You can return a reference to an array from your function. Also, you might want to look at fetchall_arrayref(). Instead of making repeated calls in a while loop, it gets all the data in one hit, as a reference to an array of arrays, one for each row. Something like (untested)

my $data = function_select($sql, @params);

foreach (@$data) {
   print join(' ', @$_), "\n";
}

function_select {
   my $sql = shift;
   my @parms = @_;
   my $sth = $dbh->prepare($sql);
   $sth->execute(@parms);
   return $sth->fetchall_arrayref();
}

maybe?

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 for your answers!


Kevin.

Now it prints only one record in the file. How I should write some (while or for) sentence which would print all records in file?

Steve.

Your code-proposal sounds quite intresting. I'll have to try it.

 

[stevexff]

Your current function includes the query execution. So you can't call it in a while loop, 'cos it re-executes the query each time, and you'll always return the first row.

With fetchall_arrayref() you can get around this problem. So assuming that my function_select() example works as advertised, then

my $sql = "SELECT * FROM tbl WHERE blah = ? AND foo = ?";
my $result = function_select($sql, 5, 6);

should return a reference to an array of all the rows where blah = 5 and foo = 6. You can then iterate over the array to do any other processing you need, like printing.

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]

Thank you steve very much!

 

[KevinADC]

Did Steves suggestion solve your problem? Are you printing all the records now?

- Kevin, perl coder unexceptional!

 

[JackTheRussel]

Kevin.

Yes it solve my problem... almost.

When I print records into file I can't make extra space character.

Now my file looks like this:

2006-12-15 08:51:1614502
2006-12-15 08:53:4314542
2006-12-15 08:59:0214678

It supposed look like this

2006-12-15 08:51:16 14502
2006-12-15 08:53:43 14542
2006-12-15 08:59:02 14678

How can I make extra character?

I have try everything:

example:

print SESAME @$_,"\n";
print SESAME @$_, ' ', "\n";
print SESAME ' ', @$_, "\n";
print SESAME  @$_, ' ', ' ', "\n";
etc...

But nothing works...

 

[MillerH]

But nothing works...

The level of ...

Oh, nevermind.

Steve already told you how to do this:

foreach (@$data) {
   print join(' ', @$_), "\n";
}

Just do either of the following commands instead:

print SESAME join(' ', @$_), "\n";
print SESAME $_->[0], ' ', $_->[1], "\n";

 

[JackTheRussel]

Thanks MillerH

My mistake

 

[stevexff]

print takes a list. If you give it an array, it prints each array item, one after the other, with no spaces, just as if you'd said

print $array[0], $array[1];

to do anything else, you either have to be explicit in the print statement, or use join. join returns all the elements of an array, separated by whatever you put in the first parameter. In the example quoted by MillerH above, it's a space.

HTH

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]

 

[KevinADC]

just another way to do it:

{
local $" = ' ';
print SESAME @$_;
}

$" is the list seperator character



- Kevin, perl coder unexceptional!