summaryrefslogtreecommitdiff
path: root/wizards
diff options
context:
space:
mode:
authorJean-Pierre Ledure <jp@ledure.be>2016-05-05 16:33:03 +0200
committerJean-Pierre Ledure <jp@ledure.be>2016-05-05 16:37:42 +0200
commit5f46f90f39b947affd0e2e37add753eb6285da93 (patch)
tree6876d2b7fff4d572086441a5a95c911a46d150ec /wizards
parente6c8d8a75f2d985ea9a14e2108d9dda89b6fd00d (diff)
Access2Base - DLookup function works now correctly with MySql and Sqlite
Bug revealed on https://ask.libreoffice.org/en/question/68080/access2base-dfunctions-with-mysql/ Root cause: SELECT TOP 1 construction is invalid for MySql. Use LIMIT keyword instead. Change-Id: Idb0bebe1adb8ca1f88dbc8f8ba039f117456337c
Diffstat (limited to 'wizards')
-rw-r--r--wizards/source/access2base/Database.xba30
1 files changed, 20 insertions, 10 deletions
diff --git a/wizards/source/access2base/Database.xba b/wizards/source/access2base/Database.xba
index 84f1112d745c..968d394aa8ee 100644
--- a/wizards/source/access2base/Database.xba
+++ b/wizards/source/access2base/Database.xba
@@ -979,19 +979,29 @@ Dim oStatement As Object &apos;For CreateStatement method
Dim sExpr As String &apos;For inclusion of aggregate function
Dim sTempField As String &apos;Random temporary field in SQL expression
- vResult = Null
+Dim sTarget as String, sWhere As String, sOrderBy As String, sLimit As String
- If psFunction = &quot;&quot; Then sExpr = &quot;TOP 1 &quot; &amp; psExpr Else sExpr = UCase(psFunction) &amp; &quot;(&quot; &amp; psExpr &amp; &quot;)&quot;
+ vResult = Null
Randomize 2^14-1
- sTempField = &quot;TEMP&quot; &amp; Right(&quot;00000&quot; &amp; Int(100000 * Rnd), 5)
- sSql = &quot;SELECT &quot; &amp; sExpr &amp; &quot; AS [&quot; &amp; sTempField &amp; &quot;] FROM &quot; &amp; psDomain
- If pvCriteria &lt;&gt; &quot;&quot; Then
- sSql = sSql &amp; &quot; WHERE &quot; &amp; pvCriteria
- End If
- If pvOrderClause &lt;&gt; &quot;&quot; Then
- sSql = sSql &amp; &quot; ORDER BY &quot; &amp; pvOrderClause
- End If
+ sTempField = &quot;[TEMP&quot; &amp; Right(&quot;00000&quot; &amp; Int(100000 * Rnd), 5) &amp; &quot;]&quot;
+ If pvCriteria &lt;&gt; &quot;&quot; Then sWhere = &quot; WHERE &quot; &amp; pvCriteria Else sWhere = &quot;&quot;
+ If pvOrderClause &lt;&gt; &quot;&quot; Then sOrderBy = &quot; ORDER BY &quot; &amp; pvOrderClause Else sOrderBy = &quot;&quot;
+ sLimit = &quot;&quot;
+
+ Select Case UCase(MetaData.getDatabaseProductName())
+ Case &quot;MYSQL&quot;, &quot;SQLITE&quot;
+ If psFunction = &quot;&quot; Then
+ sTarget = psExpr
+ sLimit = &quot; LIMIT 1&quot;
+ Else
+ sTarget = UCase(psFunction) &amp; &quot;(&quot; &amp; psExpr &amp; &quot;)&quot;
+ End If
+ sSql = &quot;SELECT &quot; &amp; sTarget &amp; &quot; AS &quot; &amp; sTempField &amp; &quot; FROM &quot; &amp; psDomain &amp; sWhere &amp; sOrderBy &amp; sLimit
+ Case Else &apos; Standard syntax - Includes HSQLDB
+ If psFunction = &quot;&quot; Then sTarget = &quot;TOP 1 &quot; &amp; psExpr Else sTarget = UCase(psFunction) &amp; &quot;(&quot; &amp; psExpr &amp; &quot;)&quot;
+ sSql = &quot;SELECT &quot; &amp; sTarget &amp; &quot; AS &quot; &amp; sTempField &amp; &quot; FROM &quot; &amp; psDomain &amp; sWhere &amp; sOrderBy
+ End Select
&apos;Lookup the value.
Set oStatement = Connection.createStatement()