Priority: Urgent
Current State: Closed
Released In: n/a
Target Release:
Applies To: Extension
Component: SqlPlugin
Branches: master
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