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

Hi Monks, I am using DBI (oracle) to do some SQL select query. I notice that the returned column order is not the same as the order of the SQL statement. E.g. select name, age, address from table; DBI would return address, name, age Below is an example of my code. Is there anyway to maintain the order based on the SQL query? Thanks. iPhony. 
$dbh = DBI->connect ( "dbi:Oracle:host=$ip;sid=$sd;port=1521", $id, $p
+w, {RaiseError => 1, AutoCommit => 0} );

    my $sqlexe = $dbh->prepare( $sql );
    my @tempdata;
    my @data;

        $sqlexe->bind_columns ( undef, \$tempdata[0][0], \$tempdata[0]
+[1] );
        while ( $sqlexe->fetch() ) {
            push @{@data}, [ $tempdata[0][0], $tempdata[0][1] ];
        }

[download]

Replies are listed 'Best First'.
Re: Perl::DBI - column not returned in order of SQL command
by mje (Curate) on Dec 11, 2008 at 08:48 UTC

    We cannot see much from your example as you omitted the SQL. However, I don't think you should be passing undef to bind_columns as the first argument as bind_columns as defined like this http://search.cpan.org/~timb/DBI-1.607/DBI.pm#bind_columns and takes a list of references. If you are selecting 3 columns you need to bind with 3 references. Your example is also missing an execute call and according to the DBI docs you should bind your columns after execute.

[reply]
Re: Perl::DBI - column not returned in order of SQL command
by iphony (Acolyte) on Dec 11, 2008 at 09:01 UTC
    Thanks for the reply. Below are the code. My SQL statement is in the form of "select name, address, age from table". But the actual statement is simply a much longer form i.e. it has 28 columns. I tried removing the "udef" without any success. I did run execute before bindings. Btw, I also uses "$sqlexe->{NAME}$i" to get the column name and I can see that it's sequence is not the same as that of the SQL statement. iPhony 
    my $dbh = DBI->connect ( "dbi:Oracle:host=$ip;sid=$sd;port=1521", $id,
+ $pw, {RaiseError => 1, AutoCommit => 0} );
$dbh->{LongTruncOk} = 1;

my $sqlexe = $dbh->prepare( $sql );
$sqlexe->execute();

$sqlexe->bind_columns ( undef, \$tempdata[0], \$tempdata[1], \$tempdat
+a[2], \$tempdata[3], \$tempdata[4] );
while ( $sqlexe->fetch() ) {
    push @{@data}, [ $tempdata[0], $tempdata[1], $tempdata[2], $tempda
+ta[3], $tempdata[4] ];
}
$sqlexe->finish();
$dbh->disconnect();

    [download]
[reply]
[d/l]

      This sounds to me more like an issue of DBI, or rather, DBD::Oracle then. But as you steadfastly refuse to show the SQL and the relevant data (that is, a Data::Dumper output of the first two rows fetched or something like that), we can't help you. Have you tried doing a simple ->fetchrow_array instead of binding your columns?

[reply]
[d/l]
      My SQL statement is in the form of "select name, address, age from table". But the actual statement is simply a much longer form i.e. it has 28 columns.

      So does the error also appear with a simple SQL statement? <op>And can you reproduce it with $sqlexe->fetchrow_array instead of bind? (If not, it's likely a problem with the parameter binding). What happens if you use references to scalars instead of references to array elements for binding?

[reply]
[d/l]

Back to Seekers of Perl Wisdom