[ic] Tag->data for mulitple keys

Barry Treahy, Jr. interchange-users@icdevgroup.org
Wed Aug 28 00:51:01 2002


Barry Treahy, Jr. wrote:

> Kevin Walsh wrote:
>
>>> Is there a method to use Tag->data for finding a row based on 
>>> multiple keys with the GDBM database?  I've seen nice solutions for 
>>> SQL...  If not the tag, any other suggestions?
>>>
>>>   
>>
>> I suggest the [query] tag with SQL.  The SQL::Statement module will
>> handle queries such as "SELECT foo FROM bar WHERE a = 1 AND b = 2"
>> against GDBM tables.
>>
>> Do yourself a favour and move your GDBM data into MySQL or PostgreSQL.
>>  
>>
> Advise well appreciated and heeded next time...
>
> Ok, here is the code snippet:
>
>  my $sql = "select discount from tiers where tier ='" . $tier ."' and 
> quantity<=" . $quantity ." order by quantity desc"; 


Ok, this is what I've identified, the query is not handling the quantity 
test as if it numeric, but rather the ASCII collating sequence which 
doesn't work because 100 is less than 25 in that situation!

What can I do to resolve this???

Barry

>
>
> #here you must use the wantarray => 1, cause the documentation says so :)
> #The documentation also says that using that will return an arrayref 
> of your
> #results, a hashref of the column names, and an arrayref of the column 
> names.
>  my ($results,$colname_hashref,$colname_arrayref) = $Tag->query({ 
> wantarray =>1,sql => $sql, } );
>
> #take the arrayref, and convert to an array @results, which is an array
> #of arrayrefs.
>  my @results = @{$results};
>  my $return;
>  foreach my $ref (@results) {
>    my @tmp = @{$ref}; #dereference each arrayref
>    foreach my $test (@tmp) { #@temp now holds actual (dereferenced) 
> results
>      $return .= "$test\n";
>    }
>  }
>
> #just for debug:
>  $Tag->Log("Tier_Pricing: return: " . $return);
>  $factor=$return;
>  $Tag->Log("Tier_Pricing: Ending: sku: " . $sku . ", tier: " . $tier .
>    ", quantity: " . $quantity . ", factor: " . $factor .
>    ", pricing: " . $price);
>
> which is returning in the $return variable, all records that match the 
> $tier regardless of the quantity. 
> Another oddity, is that if I take the descending off the select, the 
> order is in proper descending order, still all fifteen records that 
> match at the $tier, but the sort order is in reverse.
>
> Additionally, in the catalog error.log, I'm seeing this:
> mml2.mmaz.com 5xfUbemU:mmaz.com - [27/August/2002:18:17:27 -0700] 
> mmb2c /cgi-bin
> /mmb2c/ATT-0298-03-SMA-02.html Safe: Can't locate object method 
> "query" via pack
> age "Vend::Table::GDBM" (perhaps you forgot to load 
> "Vend::Table::GDBM"?) at /ib
> in/interchange/lib/Vend/Interpolate.pm line 5235.
> >
> >  $Tag->Tier_Pricing( $item->{code}, $item->{quantity} )
> >
>
> even though the CommonAdjust of:
>
> CommonAdjust "& $Tag->Tier_Pricing( $item->{code}, $item->{quantity} )"
>
> does appear to be called, otherwise I'd never get the results captured 
> in my debug file:
>
> Log: Tier_Pricing: Startup: sku: ADT-2840-NF-SMF-02, quantity: 1
> Log: Tier_Pricing: return: 0.48
> 0.60
> 0.72
> 0.88
> 0.51
> 0.63
> 0.95
> 0.78
> 0.54
> 0.45
> 0.57
> 0.67
> 0.80
> 0.98
> 1.00
> Log: Tier_Pricing: Ending: sku: ADT-2840-NF-SMF-02, tier: T2, 
> quantity: 1, factor: 0.48
> 0.60
> 0.72
> 0.88
> 0.51
> 0.63
> 0.95
> 0.78
> 0.54
> 0.45
> 0.57
> 0.67
> 0.80
> 0.98
> 1.00
> , pricing: 78.80
>
> Anyway, I'm grabbing a quick bite with my wife before she shoots me... 
> Barry
>
>
> _______________________________________________
> interchange-users mailing list
> interchange-users@icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
>
>

-- 

Barry Treahy, Jr  *  Midwest Microwave  *  Vice President & CIO 

E-mail: Treahy@mmaz.com * Phone: 480/314-1320 * FAX: 480/661-7028