Access2Base - tdf#118767 Fix DLookup for Firebird

Firebird requires
   SELECT FIRST 1 ...
syntax, while HSQLDB and other RDBMS use
   SELECT TOP 1

Additionally the Value property for monoselect listboxes has been reworked

1 files changed, 14 insertions, 3 deletions

diff --git a/wizards/source/access2base/Database.xba b/wizards/source/access2base/Database.xba
index 3e94c151b626..3bd3bced482b 100644
--- a/wizards/source/access2base/Database.xba
+++ b/wizards/source/access2base/Database.xba
@@ -1135,6 +1135,7 @@ Dim sExpr As String				'For inclusion of aggregate function
 Dim sTempField As String		'Random temporary field in SQL expression
 
 Dim sTarget as String, sWhere As String, sOrderBy As String, sLimit As String
+Dim sProductName As String
 
     vResult = Null
 
@@ -1144,7 +1145,14 @@ Dim sTarget as String, sWhere As String, sOrderBy As String, sLimit As String
 	If pvOrderClause <> "" Then sOrderBy = " ORDER BY " & pvOrderClause Else sOrderBy = ""
 	sLimit = ""
 
-	Select Case UCase(MetaData.getDatabaseProductName())
+'	Workaround for https://bugs.documentfoundation.org/show_bug.cgi?id=118767
+'	awaiting solution for https://bugs.documentfoundation.org/show_bug.cgi?id=118809
+	sProductName = UCase(MetaData.getDatabaseProductName())
+	If sProductName = "" Then
+		If MetaData.URL = "sdbc:embedded:firebird" Or Left(MetaData.URL, 13) = "sdbc:firebird" Then sProductName = "FIREBIRD"
+	End If
+
+	Select Case sProductName
 		Case "MYSQL", "SQLITE"
 			If psFunction = "" Then
 				sTarget = psExpr
@@ -1153,6 +1161,9 @@ Dim sTarget as String, sWhere As String, sOrderBy As String, sLimit As String
 				sTarget = UCase(psFunction) & "(" & psExpr & ")"
 			End If
 			sSql = "SELECT " & sTarget & " AS " & sTempField & " FROM " & psDomain &  sWhere & sOrderBy & sLimit
+		Case "FIREBIRD"
+			If psFunction = "" Then sTarget = "FIRST 1 " & psExpr Else sTarget = UCase(psFunction) & "(" & psExpr & ")"
+			sSql = "SELECT " & sTarget & " AS " & sTempField & " FROM " & psDomain & sWhere & sOrderBy
 		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
@@ -1167,8 +1178,8 @@ Dim sTarget as String, sWhere As String, sOrderBy As String, sLimit As String
 	    sSql = _ReplaceSquareBrackets(sSql)		'Substitute [] by quote string
 		Set oResult = .executeQuery(sSql)
 	    If Not IsNull(oResult) And Not IsEmpty(oResult) Then
-   				If Not oResult.next() Then Goto Exit_Function
-       		    vResult = Utils._getResultSetColumnValue(oResult, 1, True)		'	Force return of binary field
+			If Not oResult.next() Then Goto Exit_Function
+			vResult = Utils._getResultSetColumnValue(oResult, 1, True)		'	Force return of binary field
     	End If
    	End With