cross
Foswiki General Assembly will be held on 28th November 2019, 1200 UTC on Freenode IRC channel #foswiki-association. See AgendaTenthGeneralAssembly

Item14849: Strange formatted query results when match occurs in start of field names like time and timeStamp

pencil
Priority: Urgent
Current State: Confirmed
Released In: n/a
Target Release:
Applies To: Extension
Component: SqlPlugin
Branches:
Reported By: BramVanOosterhout
Waiting For:
Last Change By: BramVanOosterhout
Hi there, I am using SqlPlugin (09 Sep 2016, 3.03).

I got a unexpected result when formatting an %SQL query with:
format="|$tank|$timeStamp|$level|$time|"

I tracked the problem down to the retrieval of the hash keys in
Core.pm line 274: foreach my $key (keys %$res) {

The hash does not guarantee the order of retrieval of the keys. And hence sometimes $time will be retrieved and substituted before $timeStamp leading to the result: hh:mmStamp. The issue is resolved by reverse sorting the keys, so that longer keys come before shorter keys and hence $timeStamp is always substituted before $time.

Patch follows:
diff --git a/lib/Foswiki/Plugins/SqlPlugin/Core.pm b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
index d766a14..41ddb0b 100644
--- a/lib/Foswiki/Plugins/SqlPlugin/Core.pm
+++ b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
@@ -271,10 +271,10 @@ sub formatResult {
       next if $theSkip && $index <= $theSkip;
       my $line = $theFormat;
 
-      foreach my $key (keys %$res) {
+      foreach my $key (reverse sort keys %$res) {
         my $val = $res->{$key} || '';
         $line =~ s/\$index/$index/g;
-        $line =~ s/\$\Q$key/$val/g;
+        $line =~ s/\$\Q$key\E/$val/g;
       }
       push @lines, $line;
       last if $theLimit && $index >= $theLimit;

-- BramVanOosterhout - 02 Aug 2019

OIC. But then it should not depend on any key order as the one you are enforcing could lead to other problems on some other column names.

Best would be to use something like

$line =~ s/\$\Q$key\E\b/$val/g;

Could you try that and not sort keys? Thanks.

-- MichaelDaum - 02 Aug 2019

Hi Michael, Ah, very nice. That works for me. Here is the patch.
diff --git a/lib/Foswiki/Plugins/SqlPlugin/Core.pm b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
index d766a14..f3ee118 100644
--- a/lib/Foswiki/Plugins/SqlPlugin/Core.pm
+++ b/lib/Foswiki/Plugins/SqlPlugin/Core.pm
@@ -274,7 +274,7 @@ sub formatResult {
       foreach my $key (keys %$res) {
         my $val = $res->{$key} || '';
         $line =~ s/\$index/$index/g;
-        $line =~ s/\$\Q$key/$val/g;
+        $line =~ s/\$\Q$key\E\b/$val/g;
       }
       push @lines, $line;
       last if $theLimit && $index >= $theLimit;

Also changed the summary to reflect the problem.

-- BramVanOosterhout - 02 Aug 2019
 

ItemTemplate edit

Summary Strange formatted query results when match occurs in start of field names like time and timeStamp
ReportedBy BramVanOosterhout
Codebase 2.1.6
SVN Range
AppliesTo Extension
Component SqlPlugin
Priority Urgent
CurrentState Confirmed
WaitingFor
Checkins
ReleasedIn n/a
CheckinsOnBranches
trunkCheckins
masterCheckins
ItemBranchCheckins
Release02x01Checkins
Release02x00Checkins
Release01x01Checkins
Topic revision: r3 - 02 Aug 2019, BramVanOosterhout - This page was cached on 20 Nov 2019 - 11:16.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy