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

2 files changed, 19 insertions, 20 deletions

diff --git a/wizards/source/access2base/Control.xba b/wizards/source/access2base/Control.xba
index d1a2a8292cbd..3a41609ef48e 100644
--- a/wizards/source/access2base/Control.xba
+++ b/wizards/source/access2base/Control.xba
@@ -1800,26 +1800,14 @@ Dim oControlEvents As Object, sEventName As String
 									End If
 								End If
 							Case Else
-								vCurrentValue = ControlModel.getCurrentValue()		'	Space or uninitialized array if no selection at all
-								If IsArray(vCurrentValue) Then						'	Is an array if MultiSelect
-									If UBound(vCurrentValue) >= LBound(vCurrentValue) Then
-										vListboxValue = vCurrentValue(0)
-									Else
-										vListboxValue = ""
-									End If
-								Else
-									vListboxValue = vCurrentValue
-								End If
-								lListIndex = -1					'	Speed up getting value PM PASTIM 12/02/2013
-								If vListboxValue <> "" Then
-									If Ubound(ControlModel.SelectedItems) >= 0 Then lListIndex = Controlmodel.Selecteditems(0)
-								End If
+								'getCurrentValue does not return any significant value anymore
+								'	Speed up getting value PM PASTIM 12/02/2013
+								If Ubound(ControlModel.SelectedItems) >= 0 Then lListIndex = Controlmodel.Selecteditems(0) Else lListIndex = -1
 								' If listbox has hidden column = real bound field, then explore ValueItemList
-								bListboxBound = _ListboxBound()
-								If bListboxBound Then
+								If _ListboxBound() Then
 									If lListIndex > -1 Then vGet = ControlModel.ValueItemList(lListIndex)	'	PASTIM
 								Else
-									vGet = vListboxValue
+									If lListIndex > -1 Then vGet = ControlModel.getItemText(lListIndex)
 								End If
 						End Select
 					End If
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