DBI help - MS Access random field order

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

Dear Monks, I'm having a problem with Perl DBI returning a random field order when interacting with a MS Access 2000 database.

Query follows:

$query = "select id_code, title, author, html_hyperlink, entry_date, i
+sbn, ddc, dewey_class, lcc,book_details, language, physical_descripti
+on, discard_ind from books_main where $sort_by = '$id_code' order by 
+$sort_by";
[download]

Where variable $sort_by is one of the other fields listed above (isbn, id_code, etc).

My problem is with data display. When the query returns records from MS Access, the fields are not in the order selected above, and in fact are in different orders depending on the value of the where clause. IE if ISBN is selected, the fields are returned in different order than if AUTHOR is selected.

I am viewing the data via something like following:

  
!$db->Sql($query) || Handle_Errors("$current_time couldn't do query: $
+query because " . $db->Error() ,1, $db->Error() . " $? $!",1);
<p> 
  while($db->FetchRow()) { 
    @data = $db->DataHash();   
           
     $cycle_1         = $data[0];## physical desc field_name 
         $cycle_2         = $data[1]; physical desc data 
         $cycle_3       = $data[2];## language field name 
          ###etc.....
[download]

Is there anything I can add to the query, or a different manner of output where the fields will appear in a consistent manner? This makes any field-specific manipulation post-query very difficult. Thanks.

Perl Version:

This is perl, v5.8.8 built for MSWin32-x86-multi-thread (with 33 registered patches, see perl -V for more detail)

Comment on DBI help - MS Access random field order Select or Download Code

Replies are listed 'Best First'.
Re: DBI help - MS Access random field order by afoken (Chancellor) on Feb 10, 2010 at 19:14 UTC
What does `$db->DataHash()` return? A hash? (Or something like `values %hash`)? So why do you assign it to an array? Hashes have no defined order, so every single run can give you a different order of columns. It has nothing to do with the ORDER BY clause in your SQL statement. Corion already said that your code does not look like DBI. Are you using some kind of home-grown DBI wrapper? Your code is vulnerable to SQL injection, because you don't use placeholders, but instead insert data into the SQL statement(s). It also sabotages statement caching. Change that code to use placeholders. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re: DBI help - MS Access random field order by Corion (Patriarch) on Feb 10, 2010 at 18:54 UTC
It strikes me as quite odd that the order of columns in the resultset wouldn't be the same as the order of columns in the query. But you don't seem to be using DBI, as you call `->Sql` and `->FetchRow` methods which are not part of the DBI API, so whatever sits in between your code and DBI might screw things up there. If all else fails, you can use DBI's `Slice => {}` option to have queries return hashes with the keys being the column names and the values being the associated query results for each row.	[reply] [d/l] [select]