Thread: moving from mySQL to pgsql, need a bit of help (perl)

    Hello, I'm trying to move from mySQL to PostgreSQL, and I've got to port a
web app over that's written in perl. It uses the DBD/DBI stuff, and works
great so far with mySQL. I've got a very specific question relating to
pulling data out of a database, I hope this is the correct forum for it.
Here goes:

    I have a database set up with a table that has 3 fields in it:

    userid
    username
    userdata

    In my current code (perl/mySQL), I execute the following query: "SELECT *
from table_name" - then use the following code to put it in a hash:

$j = 0;
while (@row = $sth->fetchrow_array)
{
   for ($i = 0; $i < $fields; $i++)
   {
      $taghash[$j]{$$fieldnames[$i]} = $row[$i];
   }
   $j++;
}

    What I end up with is an array of hashes containing all the table data.
Each element in the array is a hash called $taghash[$i], and the fields can
be accessed via $taghash[$i]{'userid'}, $taghash[$i]{'username'}, and
$taghash[$i]{'userdata'}. $fields and $fieldnames are variables created by a
subroutine I wrote which is called after the query is executed. $fields is
simply the number of fields in the table, and $fieldnames is an array
containing the names of the columns in the table, in order, produced by
"$fieldnames = $sth->{NAME};" HERE is the problem. The PostgreSQL DBI/DBD
doesn't seem to support this. I'm looking for something that returns the
names of the columns in the table, so I can duplicate my code without
changing too much. Is there anything available to do this? I've yet to find
it in the documentation, though I may have simply missed it, as I'm new to
PostgreSQL... Any help is appreciated...

Thanks,
Matt (jaeger@morpheus.net)

Matt Housh wrote:
>
>         In my current code (perl/mySQL), I execute the following query: "SELECT *
> from table_name" - then use the following code to put it in a hash:
>
> $j = 0;
> while (@row = $sth->fetchrow_array)
> {
>    for ($i = 0; $i < $fields; $i++)
>    {
>       $taghash[$j]{$$fieldnames[$i]} = $row[$i];
>    }
>    $j++;
> }
>
>         What I end up with is an array of hashes containing all the table data.
> Each element in the array is a hash called $taghash[$i], and the fields can
> be accessed via $taghash[$i]{'userid'}, $taghash[$i]{'username'}, and
> $taghash[$i]{'userdata'}. $fields and $fieldnames are variables created by a
> subroutine I wrote which is called after the query is executed. $fields is
> simply the number of fields in the table, and $fieldnames is an array
> containing the names of the columns in the table, in order, produced by
> "$fieldnames = $sth->{NAME};" HERE is the problem. The PostgreSQL DBI/DBD
> doesn't seem to support this. I'm looking for something that returns the
> names of the columns in the table, so I can duplicate my code without
> changing too much. Is there anything available to do this? I've yet to find
> it in the documentation, though I may have simply missed it, as I'm new to
> PostgreSQL... Any help is appreciated...

I believe DBI/DBD does this for you:

while ($row_href = $sth->fetchrow_hashref)
{
   push( @taghash, $row_href);
}



Regards,
Ed Loehr

Ed Loehrwrote:
> I believe DBI/DBD does this for you:
>
> while ($row_href = $sth->fetchrow_hashref)
> {
>    push( @taghash, $row_href);
> }

Don't do that! Each hashref in that array will point to the same hash in some
future version of the DBI. You should do this instead:

  while($row=$sth->fetchrow_hashref){
    push @taghash,{%$row};
  }

Or, better yet:

  $taghash=$sth->fetchall_arrayref({});

to get everything all in one go.

--
    Peter Haworth    pmh@edison.ioppublishing.com
"Master, does Emacs have the Buddha nature?" the novice asked.
The Chief Priest had been in the temple for many years and could be
relied upon to know these things.  He thought for several minutes before
replying, "I don't see why not.  It's got bloody well everything else."