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

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		'For CreateStatement method
 Dim sExpr As String				'For inclusion of aggregate function
 Dim sTempField As String		'Random temporary field in SQL expression
 
-    vResult = Null
+Dim sTarget as String, sWhere As String, sOrderBy As String, sLimit As String
 
-	If psFunction = "" Then sExpr = "TOP 1 " & psExpr Else sExpr = UCase(psFunction) & "(" & psExpr & ")"
+    vResult = Null
 
 	Randomize 2^14-1
-	sTempField = "TEMP" & Right("00000" & Int(100000 * Rnd), 5)
-    sSql = "SELECT " & sExpr & " AS [" & sTempField & "] FROM " & psDomain
-    If pvCriteria <> "" Then
-        sSql = sSql & " WHERE " & pvCriteria
-    End If
-    If pvOrderClause <> "" Then
-        sSql = sSql & " ORDER BY " & pvOrderClause
-    End If
+	sTempField = "[TEMP" & Right("00000" & Int(100000 * Rnd), 5) & "]"
+	If pvCriteria <> "" Then sWhere = " WHERE " & pvCriteria Else sWhere = ""
+	If pvOrderClause <> "" Then sOrderBy = " ORDER BY " & pvOrderClause Else sOrderBy = ""
+	sLimit = ""
+
+	Select Case UCase(MetaData.getDatabaseProductName())
+		Case "MYSQL", "SQLITE"
+			If psFunction = "" Then
+				sTarget = psExpr
+				sLimit = " LIMIT 1"
+			Else
+				sTarget = UCase(psFunction) & "(" & psExpr & ")"
+			End If
+			sSql = "SELECT " & sTarget & " AS " & sTempField & " FROM " & psDomain &  sWhere & sOrderBy & sLimit
+		Case Else		'	Standard syntax - Includes HSQLDB
+			If psFunction = "" Then sTarget = "TOP 1 " & psExpr Else sTarget = UCase(psFunction) & "(" & psExpr & ")"
+			sSql = "SELECT " & sTarget & " AS " & sTempField & " FROM " & psDomain & sWhere & sOrderBy
+	End Select
 
     'Lookup the value.
     Set oStatement = Connection.createStatement()