Description: The DLookup function generates an SQL select statement not suitable for the Firebird engine. Full Access2Base error message is: Error #1510 (DFunction execution failed, SQL=SELECT TOP 1 "SupplierID" AS "TEMP83032" FROM "Suppliers" WHERE "CompanyName"='Escargots Nouveaux') occurred in DLookup The use of TOP 1 will need to be changed for Firebird. Steps to Reproduce: 1. Download the example file at https://nextcloud.documentfoundation.org/s/ePAmqMj3N2Ywms4 2. Open the form 'Products_ListBoxFilter' 3. Clear the initial error box 4. All controls should load with data 5. Click on any item in either of the two top list boxes on the left side of the form. (see screen shot) Actual Results: Error triggered from Dlookup function. Expected Results: Select statement executes and updates UI. Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 143550 [details] screen shot with selection and error message
The SQL statement should not contain SELECT TOP 1 but, if I'm not wrong, SELECT FIRST 1 instead. The statement is built in module "Database" of the Access2Base library from line 1145 on: Select Case UCase(MetaData.getDatabaseProductName()) In LO 6.1 the value returned by getDatabaseProductName() is an empty string. This is clearly a bug. What does LO 6.2 return ? To test it, Drew, insert this simple code typically in the TEST module of the provided database and run it: Sub TestType MsgBox Application.CurrentDb().MetaData.getDatabaseProductName() End Sub With this answer I can easily complete the Select Case on lines 1145 etc and make the DLookup run smoothly. Thanks. JPL
Created attachment 143627 [details] sample file
Also reproduced in Version: 6.0.0.0.alpha1+ Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9 CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); Calc: group threaded
Opened an issue for the metadata uno service returning a null string for productname. https://bugs.documentfoundation.org/show_bug.cgi?id=118809 added it to 'see also' here. Even with that property not returning a name the code could handle it by checking the URL property. For the URL there would be at least two, embedded and file. So, off the code in _DFunction could maybe change to sProductName = MetaData.getDatabaseProductName() If sProductName = "" Then If MetaData.URL = "sdbc:embedded:firebird" Or Left( MetaData.URL, 13) = "sdbc:firebird" Then sProductName = "Firebird" End If End If Select Case UCase(sProductName) 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 "FIREBIRD" If psFunction = "" Then sTarget = "FIRST 1 " & psExpr Else sTarget = UCase(psFunction) & "(" & psExpr & ")" End If sSql = "SELECT " & sTarget & " AS " & sTempField & " FROM " & psDomain & sWhere & sOrderBy ....
Jean-Pierre Ledure committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3e39524d4171f0ecadad5658d6e03cf44126b2a0 Access2Base - tdf#118767 Fix DLookup for Firebird It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
verified fix with build: Version: 6.2.0.0.alpha0+ Build ID: 7f697ec27b1175ed94228155935d9ccd3cd5606c CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-07-20_13:34:48 Locale: en-US (en_US.UTF-8); Calc: group threaded DLookup is working as expected, also the faulty SQL statement in the example form has been updated for Firebird (Thank you Jean-Pierre for that). The updated FB Northwind file has been updated on the tdf nextcloud server.
The above commits solved the issue in releases LO 6.0 up to LO 6.2. They are a workaround to the lack of metadata info about the current DBMS. Since the implementation described in https://bugs.documentfoundation.org/show_bug.cgi?id=118809, the applied correction is obsolete: - < 6.3: the MetaData.getProductName() returned "" for Firebird database - >= 6.3: ... "FIREBIRD (ENGINE12)" (see https://forum.openoffice.org/en/forum/viewtopic.php?f=47&t=61447&p=503397#p503365) Hence the decision to reopen the bug.
Bug corrected with fix https://gerrit.libreoffice.org/c/core/+/108185 (pushed to libreoffice-7-0). The code in 7.1 and master is already correct.
Jean-Pierre Ledure committed a patch related to this issue. It has been pushed to "libreoffice-7-0": https://git.libreoffice.org/core/commit/c7eef3cbceacca944785daf6ca5e8d8301df1644 tdf#118767: Access2Base - Remove workaround on Firebird product name It will be available in 7.0.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.