Hash Keys Order Anomaly 1

[1DMF]

I'm creating an XLS/CSV file from an array of hashes.

The array of hashes (basicaly record set) has the table columns relative to the select statement.

So i'm assuming (correct me if i'm wrong), that the order of keys in the hash is the same order of the SQL select statement.

However when I use the following code..

    for(@logs){
        $i++;
        my $row;

        # Build Header + Row info 
        while (my ($key, $value) = each(%$_)){

            if($i == 1){                
                $head .= "\"$key\",";
            }   

            if($key eq "Used"){
                my @tm = split(/\./,$value);
                $value = $tm[0];
            }                    

            $row .= "\"$value\",";
        }
    
        # drop extra commas + add newline char
        $row = substr($row,0,length($row)-1);
        $row .= "\n";
        $xls .= $row; 
               
    }

The $head var has the order of the column headings different.

Is their some internal ordering going on here?

What's causing the difference in column(keys) order?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[prex1]

There is no guaranteed ordering of hash keys in perl; the only possibility of obtaining the ordering you want is by [tt]sort keys[/tt] .
The only way of keeping an initial ordering of keys is by collecting them into an array before the hash is created. Also keep in mind, though this is likely not the case of column headings, that transferring the keys from the array to the hash will kill any duplicates.

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[1DMF]

Not sure what you mean regarding transferring keys from array to hash and duplicates!

The hash is created by the Win32:ODBC module, I wrongly assumed it would reflect the order of the columns used in the SQL select statement.

But having googled it seems it's an apparent major bug bare with PERL. Hash order seems to be random.

Who's great idea was that?

I assume there is some reason for this but it doesn't help much and if I want to order the hash keys, it's gonna mean major code bloat just to facilitate this plus the extra time to code.

I guess the received XLS spreadsheet will just have to have the columns in the order it arrives and be done with it.

If the Boss complains, then he has to accept it's gonna take longer to write code just to faclilitate the column order.





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[KevinADC]

Hashes have always been that way. Their order is not random but they have no guaranteed order. Its not a bug or a design flaw. Hashes are lists with random access, arrays are lists with ordered access (indexing). You can have an indexed hash:

http://search.cpan.org/~mhx/Tie-Hash-Indexed-0.05/lib/Tie/Hash/Indexed.pm

------------------------------------------
- Kevin, perl coder unexceptional!

 

[feherke]

Hi

But having googled it seems it's an apparent major bug bare with PERL.

Certainly not a [tt]perl[/tt]-only feature. Hashes are working the same way in [tt]awk[/tt], [tt]tcl[/tt] and [tt]bash[/tt] too.

Feherke.

http://rootshell.be/~feherke/

 

[audiopro]

What is the array of hashes created from?
If it is a fixed layout source it may be easier creating a multi dimensional array.


Keith

http://www.studiosoft.co.uk

 

[KevinADC]

http://perldoc.perl.org/perldsc.html#ARRAYS-OF-HASHES

------------------------------------------
- Kevin, perl coder unexceptional!

 

[Xaqte]

You might want to look at this: thread219-1528345

 

[1DMF]

Wow lots of replies thanks guys & gals.

Their order is not random but they have no guaranteed order.

hmm, if there is no guaranteed order, then that makes it random doesn't it? otherwise there would be 'order' and not 'kaos' ;-)

Certainly not a perl-only feature.

ok so it's not just perl people are moaning about, doesn't make me feel better or solve my problem - lol

What is the array of hashes created from?

Win32:ODBC module it's a record set. here's the code..

# Run SQL Command
if(!$db->Sql("$sel")) {

	# Loop SQL Record Set
		while($db->FetchRow()){ 
				# Build Array of Hashes with SQL Data
				my %dt = $db->DataHash();
			    $rs[@rs] = \%dt;	
		}
	# Close DB Connection
	$db->Close();

	# Return Record Set Array of Hashes
	@rs;

}

You might want to look at this: thread219-1528345: Maintain order of hash keys

thanks, I did , but I don't see how it is going to help me, Kevin even adds make them an array of hashes, but it already is?


Sort a<=>b , is not an option either as I don't want them in any alphabetical order ASC or DESC, I want them to be returned in the order they were selected by the SQL statement.

SQL would return them in order on other platforms if they were a normal 'recordset' or column query, but I'm getting the feeling this is not going to be possible in perl using hashes.

Any bright ideas anyone?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[prex1]

You need to go back where the SQL statement is created, and generate an array containing the field names to be SELECTed.
You can do this either at time of creation, if you use a module to generate the SQL, or immediately after, by writing an interpreter of the SQL (not necessarily a complex task, especially if your SQL won't contain aliases and other specialties).
With that array (that, if necessary, you can bind to the SQL for example by setting its index 0 to the SQL itself) you'll output the rows with the columns in the correct order, something like:

$xls='"'.join('","',@header).'"'."\n";
for my$set(@logs){
  $xls.='"'.join('","',map{$$set{$_}}@header).'"'."\n";
}

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[1DMF]

You've completely lost me prex1.

The hash is created by Win32:ODBC and set by the ->DataHash() method.

it would be no different than if I was using the DBI module and ->selectrow_hashref() method.

I can't go editing either of these modules!

Nor do I want to change my SQL module as it would be putting extra overheads into the module to perform a task of re-ordering the ODBC/DBI hashref, when 99% of the time, it is not required on SQL commands as usually the data is not required to be exported to XLS/CSV in a specific order.

I guess I have no choice but in the XLS routine to hand code the specific columns for output one at a time, as it would seem a foreach keys performed on a hash is unreliable for hash key order.

... the penny has dropped slightly in terms of what you are recommending.

yes i could have a holding var with the required column names in the order I so wish, split to an array, loop array and build the string calling the hash key by key in the array. something like this...

my $vars = "field1,field2,field3,field4,field5,field6,field7,field8,field9";
my @head = split(/\,/,$vars);

    for(@logs){
        my $row;
        foreach my $col(@head){
            my $value = $_->{$col};

            if($col eq "Used"){
                my @tm = split(/\./,$value);
                $value = $tm[0];
            }                    

            $row .= "\"$value\",";
        }
    
        # drop extra commas + add newline char
        $row = substr($row,0,length($row)-1);
        $row .= "\n";
        $xls .= $row;                
    }

I think that would do it, don't you?




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[prex1]

Well, your code does the same as mine in about ten lines instead of one...
Seriously: what I was proposing was to extract the field list from the SQL statement (a string), based on the assumption that you would have different statements to handle. But if you always have the same field list, then there is not much more to say...

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[1DMF]

Well, your code does the same as mine in about ten lines instead of one...

is that including after it has been pre-interpreted and the code folded for execution? (not sure of correct terminology)

Seriously: what I was proposing was to extract the field list from the SQL statement (a string)

here is the line which creates the array of hashes...

my @logs = &getSQL("Tracking_Log INNER JOIN Members ON Tracking_log.UserID=Members.ID",[b]"Tracking_log.UserID,Tracking_log.Used,Tracking_log.Area,FirstName,LastName"[/b],"1=1","RecID DESC");

So you can see by the highlighted line, the columns selection isn't a string var by your terminology.

To break your code down so I can understand it better and perhaps improve mine...i'm thinking the following doesn't exist in my code

$xls='"'.join('","',@header).'"'."\n";

how was @header being populated with an array of the column names?

That's my code..

my $vars = "field1,field2,field3,field4,field5,field6,field7,field8,field9";
my @head = split(/\,/,$vars);

however, later on (which I didn't post) , I do do this...

    # create column headings
    for(@head){
       $head .= "\"$_\",";
    }

    # create heading + data
    $head = substr($head,0,length($head)-1);
    $head .= "\n$xls";

what you're saying is I can add the columns to the data XLS string first! so do...

$xls='"'.join('","',@head).'"'."\n";

which also removes the need for striping the extra comma!

I understand the following code...

$xls.='"'.join('","',map{$$set{$_}}@header).'"'."\n";

but because I need to do some data formating on sepcific fields, I don't think I can use your example, my slightly modified version is now..

# set Vars
my $time = time;
my $vars = "field1,field2,field3,field4,field5,field6,field7,field8,field9";
my @head = split(/\,/,$vars);
my $xls='"'.join('","',@head).'"'."\n";

      for(@logs){
        my $row;
        foreach my $col(@head){
            my $value = $_->{$col};

            if($col eq "Used"){
                my @tm = split(/\./,$value);
                $value = $tm[0];
            }                    

            $row .= "\"$value\",";
        }
    
        # drop extra commas + add newline char
        $row = substr($row,0,length($row)-1);
        $row .= "\n";
        $xls .= $row;                
      } 

    # Build file string 
    my $file = "c:/YOUR_FILE_PATH/$time.csv";

    #create XLS file     
    open(CATAL, ">$file"); 
    flock(CATAL, 2);
    binmode(CATAL);
    truncate(CATAL, length($head));
    seek(CATAL, 0, 0);
    print CATAL "$xls";
    close(CATAL);

is that better?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[prex1]

If I understand you correctly (I've never used the ODBC module), the second string supplied to the [tt]getSQL[/tt] sub is the SELECT list of the SQL statement. Now, assuming you want to drop the db name from the field names, you would populate the [tt]@head[/tt] array like so:

for(split(/,/,$second_arg)){
  my($db,$field)=split/\./;
  if($field){
    push@head,$field;
  }else{
    push@head,$db;
  }
}

In this way your field list is not hard coded, and your code will accept any field list, provided the form of the SQL statement is always as above.

And of course I'm not seriously criticizing your code, we all first of all need to write code that works, all the rest is refinement, sometimes useful, most of the time useless.

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[1DMF]

Hi Franco,

You are correct regarding the way getSQL works, here is the sub I wrote for my SQL module..

sub getSQL {

#_0 = Table
#_1 = Columns
#_2 = Where
#_3 = Order By

# Define Record Set Array & Hash
my @rs;

#Build SQL Statement
my $sel = "SELECT $_[1] FROM $_[0] WHERE $_[2]";

# Check for ORDER BY
if($_[3]){$sel .= " ORDER BY $_[3]";}

# Open DB Connection
my $db;

if(!$HLP){
    $db = new Win32::ODBC($MDB) || die "getSQL Error Connecting: " . Win32::ODBC::Error();
}
else{ 

    $db = new Win32::ODBC("FILEDSN=$DSN;") || die "getSQL Error Connecting: " . Win32::ODBC::Error();
}


# Run SQL Command
if(!$db->Sql("$sel")) {

	# Loop SQL Record Set
		while($db->FetchRow()){ 
				# Build Array of Hashes with SQL Data
				my %dt = $db->DataHash();
			    $rs[@rs] = \%dt;	
		}
	# Close DB Connection
	$db->Close();

	# Return Record Set Array of Hashes
	@rs;

} 
else{die "Error in getSQL ($sel)" . Win32::ODBC::Error();}

}

now if I was to change anything I guess it would be in the SQL module and create a global array var which always held last SQL statement fields.

Unfortuantely due to the flexability of the getSQL sub, it's not always just a field/column list.

you can use

my @rs = &getSQL("Table","TOP 20 FirstName,LastName","Where","Order");

or

my @rs = &getSQL("Table","COUNT(1) as Cnt,FirstName,LastName","Where","Order");

etc. I think you get the idea.

So trying to neatly format or truncate the string into simple column headings wouldn't work.

It really is easier in my opinion to simply hard code (only where this XLS function is required) the fields required and thier order.

OK I could turn the XLS into a sub on it's own and pass in the data, but that's overkill as it's only the odd place within a labarynth of scripts where this is required.

I never thought for a minute you were having a dig at me or my code and I'm always open to improving my myself as a programmer and the code I write!

So I really do appreciate your input, you've already cut the code down dramatically, so give yourself a pat on the back as job well jone!

Have a star you star!


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

Another reason I have found for using a comma separated string var for holding table column names as well as an additional one for the XLS/CSV column headings is it gives you the flexability to export data from the DB to CSV and have 'User Friendly' column headings in the resulting CSV file. ;-)

So instead of them getting a spreadsheet with "Sub_Date","Comp_Date" etc..

They can have "Date of Submission","Date of Completion", it works great

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!