sybperl-l Archive

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: getting results from select in an associative array
Date: Jul 23 1998 4:55PM

Lee Falkenhagen writes:
 > I am executing a stored procedure that executes as its last statement a 
 > select statement.  When in WISQL, the sp returns the rows in order.  
 > However, with the following code, it returns the rows a different order.  
 > I can not figure out what order it returns it in.
 > 
 > I need to keep the results in the same order because I am creating a 
 > flat file to load on a mainframe.  I need to have the key, because, 
 > later in the loop, I do not want to print certain columns.
 > 
 > Anyone know how to get the columns in the order of the select?
 > 
 >         $::dbh->dbcmd("exec proc_oi_case ");
 >         $::dbh->dbsqlexec();
 >         $::dbh->dbresults();
 >         if ($::dbh->DBROWS ne FAIL) {
 >         $dbh1 = Sybase::DBlib->dbopen();
 >         $dbh1->dbuse($::DBNAME);
 >         open(OUTPUT, ">".$::SOURCEDIR."kc.kc300100.data");
 >         $l_datacount = 0;
 >         while (%data = $::dbh->dbnextrow(1)) {
 >                 while (($key,$value) = each %data) {
 >                         print "key = $key \n";
 >                         print OUTPUT "$value";
 >                 }

A hash is by definition not ordered...

You should do it differently:

while(@data = $dbh->dbnextrow) {
   if(keys(%cols) == 0) {
	for($i = 0; $i < scalar(@data); $i++) {
	    $cols{$i} = $dbh->dbcolname($i+1);
        }
   }
   for($i=0; $i < scalar(@data); ++$i) {
	if($cols{$i} eq 'the_column_I_want_to_skip') {
	    next;
	}
	print OUTPUT "$data[$i]";
   }
}

This is one way to do it, although I can imagine at least one other
way...

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@mbay.net       -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com