From 5f46f90f39b947affd0e2e37add753eb6285da93 Mon Sep 17 00:00:00 2001
From: Jean-Pierre Ledure <jp@ledure.be>
Date: Thu, 5 May 2016 16:33:03 +0200
Subject: 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
---
 wizards/source/access2base/Database.xba | 30 ++++++++++++++++++++----------
 1 file changed, 20 insertions(+), 10 deletions(-)

(limited to 'wizards/source/access2base/Database.xba')

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()
-- 
cgit v1.2.3