DBI and Select *

[jez]

Hi everyone,

Is there a way to run a query with DBI that is like this;-

SELECT * FROM table;

Then what i want to do is refer to the results like this ;-

$data['product']

However this query will return about 100 columns per row, each is required but there are too many to map to variable names, i need to bind the column results to the the DB column names which may not be known to the perl query.

I have looked at lots of DBI stuff on the web, but at some point you always seem to have to define the name of the vars to attach the results to.

Thanks.

 

[stevexff]

Use the DBI methods fetchrow_hashref or fetchall_hashref. The easiest way to figure out the layout and how to interpret what comes back is to print it with Data:umper...

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]

 

[jez]

Thanks for the suggestion, but i was not able to get either of the fetchall_hasref or fetchall_arrayref to work. I could not find a complete example of how to use it.

Also using data::dumper was useful to see the full results but still doesn't provide the column names.

What i did in the end was to use this ;-

$sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));

Which seems to work ok.

thanks.

 

[stevexff]

jez

Borrowed freely from the DBI docs, and completely untested...

$dbh->{FetchHashKeyName} = 'NAME_lc';
my $sth = $dbh->prepare("SELECT FOO, BAR, ID, NAME, BAZ FROM mytable");
$sth->execute;
my $stuff = $sth->fetchall_hashref('id');
print Dumper($stuff);

assumes that 'id' is the column name of your primary (unique) key.

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]

 

[ryagatich]

When you want to access a hash reference, you can access it in the following manner: $row->{name}

Accessing your data via $row['name'] isn't really productive here. But, if you want $row{'name'} instead (or $row{name} for those of us lazy people), you can dereference the hash by doing: my %row = %{$row}; - but you really don't want to do this, as performance is pretty important sometimes. Below is some code [that works]

<quote>
my $sth = $dbh->prepare('select a,b from foo');
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
print "a: ",(defined($row->{a})) ? $row->{a} : 'NULL',"\n";
print "b: ",(defined($row->{b})) ? $row->{b} : 'NULL',"\n";
}
$sth->finish;

</quote>

What you should take out of this is at least the following:
1) I used 'select a,b from foo' as my query -- note that I forced specific columns [and their case is lower case]
2) I'm maintaining the fact that I'm reading references
3) I step through my list by doing one row at a time [rather than using 'fetchall', as when dealing with a million rows, the DB is better at filtering than your code will be!

 

[jez]

Hi guys,

Thanks for the answers, although i am little confused now.

After trying the fetchall approach i then went back to
fetchrow_hashref and i seem to have the data i want in there but i cannot get at it.

....
  $sth->execute();
  while ($hash_ref = $sth->fetchrow_hashref) {
  # print $hash_ref;   
    push(@retVal, {$hash_ref});
 };
    $sth->finish();
    return @retVal;
# end of sub routine

Where i think i am getting lost with examples is that i cannot print from the same location as i loop through the results from the DB.
So this code is in a sub in an include file, then i need to return the data to the html page where it is output with a loop.


So... although the responses here make sense (also the examples elsewhere on line) i need to take it a step further and fill up an array with the data and be able to access it.


What i need here is NO declaration of the columns being fetched. Data being loaded into some variable for use (and re-use elsehwere).
Then way to access that data.

Using the code above i do not know what the syntax for calls to the data might be.

I am trying to do this (where @storeData is the return val from the sub.

for $row(@storedData){
   #print $row;
   print $row{'supplier_name'};
}

Jez

 

[ryagatich]

So what if you do this:

my @storedData;
while (my $row = $sth->fetchrow_hashref) {
push(@storedData, $row);
}
$sth->finish;

# blah blah, more stuff

foreach my $storedRow (@storedData) {
print $storedRow->{'supplier_name'};
}

 

[jez]

That works, thank you very much !!!

So where i was doing

 push(@retVal, {$hash_ref});

I didn't need the curly braces and then when outputting i need to loop through in the same way populated it then i can treat printing the output like the examples.

Thankyou, this is a lot clearer now.