Maintaining column name and column order using DBI

Tatnall has asked for the wisdom of the Perl Monks concerning the following question:

Hello all,

I need the column name to be associated with the column value and I need the data order to stay in the same order as they appear in the table.

I am trying to find a way to pull a single row from the database keeping data in the order of the table columns.

I would like to be able to use a join also and keep the data ordered by the tables I specify and then the columns in each table.

I went after the easier problem first. Wanting data associated to a column name meant a hash, but hashes aren't ordered. I thought I could use Tie::IxHash and my problems would all go away. Example below. I've used super search, been through the DBI docs, but been unsuccesfu and definitely need some monk wisdom.

use strict;
use Data::Dumper;
use DBI qw(:sql_types);
use Tie::IxHash;

tie my %results, "Tie::IxHash";

... connect to db ....

my $stmt = qq~SELECT * FROM registrants WHERE registrant_id = ?~;
%results = % { $dbh->selectrow_hashref($stmt, undef, $registrant_id) }
+;
[download]

Using the tie gave me the exact same results as not using it, which lead me to believe that the order dbi stores the hash might be out of my control. An example of the more complicated statement, I'd like to use:

    my $stmt = qq~SELECT * FROM registrants, hotels, cadavers WHERE re
+gistrants.registrant_id = ? AND registrants.registrant_id = hotels.re
+gistrant_id~;
    %results = % { $dbh->selectrow_hashref($stmt, undef, $registrant_i
+d) };
[download]

Has anybody run across this before?

"Recognizing who we aren't is only the first step toward knowing who we are." - Os Guinness

Comment on Maintaining column name and column order using DBI Select or Download Code

Replies are listed 'Best First'.
Re: Maintaining column name and column order using DBI by bobf (Monsignor) on Mar 15, 2006 at 18:17 UTC
It sounds like you're looking for the `NAME` (or `NAME_lc` or `NAME_uc`) statement handle attribute. Per the DBI docs: NAME (array-ref, read-only) Returns a reference to an array of field names for each column. The names may contain spaces but should not be truncated or have any trailing space. Note that the names have the letter case (upper, lower or mixed) as returned by the driver being used. Portable applications should use NAME_lc or NAME_uc. `print "First column name: $sth->{NAME}->[0]\n";` HTH	[reply] [d/l] [select]
Re^2: Maintaining column name and column order using DBI by Tatnall (Beadle) on Mar 15, 2006 at 18:58 UTC
Excellent, that's the type of thing I was looking for. Thank you. "Recognizing who we aren't is only the first step toward knowing who we are." - Os Guinness	[reply]
Re: Maintaining column name and column order using DBI by blokhead (Monsignor) on Mar 15, 2006 at 18:18 UTC
You can use the "NAME" (or NAME_uc, NAME_lc) statement handle attribute (see this section of the docs) to access the names of the columns as an arrayref. Their ordering should be preserved (may be DBD-dependent, though). If there are multiple columns with the same name, they will get clobbered when DBI puts them into a hash for you, so keep that in mind as well. You may also need to bypass DBI's `selectrow_` shortcuts so that you actually get access a statement handle for this NAME attribute. BTW, many DB designers/coders will tell you that `SELECT ` queries are bad style. blokhead	[reply]
Re: Maintaining column name and column order using DBI by jZed (Prior) on Mar 15, 2006 at 17:41 UTC
Maybe I'm missing something ... but if you want to get the columns in a particular order, specify the columns in the order you want as part of the SELECT clause, and then use $dbh->selectrow_array() or $sth->fetchrow_array() to retrieve the results.	[reply]
Re^2: Maintaining column name and column order using DBI by Tatnall (Beadle) on Mar 15, 2006 at 17:49 UTC
Thanks for the reply! I probably need to be more specific. I need to use the column name, column value pair later in my script. Using an array won't keep the column name associated with the value, which won't do me any good. I need the column name to be associated with the column value and I need the data order to stay in the same order as they appear in the table. "Recognizing who we aren't is only the first step toward knowing who we are." - Os Guinness	[reply]
Re: Maintaining column name and column order using DBI by kwaping (Priest) on Mar 15, 2006 at 19:23 UTC
~~I recommend you also investigate DBI's `selectrow_hashref` and `fetchrow_hashref` methods as well.~~ Update: Woops, didn't notice OP is already using those! --- It's all fine and dandy until someone has to look at the code.	[reply] [d/l] [select]