[Libreoffice-commits] core.git: wizards/source

[Jean-Pierre Ledure]

 wizards/source/access2base/Database.xba |   30 ++++++++++++++++++++----------
 1 file changed, 20 insertions(+), 10 deletions(-)

New commits:
commit 5f46f90f39b947affd0e2e37add753eb6285da93
Author: Jean-Pierre Ledure <jp at ledure.be>
Date:   Thu May 5 16:33:03 2016 +0200

    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

diff --git a/wizards/source/access2base/Database.xba b/wizards/source/access2base/Database.xba
index 84f1112..968d394 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()