Bug 118767 - FIREBIRD: Access2Base: Dlookup function generates SQL error
Summary: FIREBIRD: Access2Base: Dlookup function generates SQL error
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.0.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2018-07-14 20:30 UTC by Drew Jensen
Modified: 2018-07-20 20:20 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
screen shot with selection and error message (215.34 KB, image/png)
2018-07-14 20:30 UTC, Drew Jensen
Details
sample file (3.21 MB, application/vnd.oasis.opendocument.database)
2018-07-18 18:21 UTC, Xisco Faulí
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2018-07-14 20:30:03 UTC
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:
Comment 1 Drew Jensen 2018-07-14 20:30:43 UTC
Created attachment 143550 [details]
screen shot with selection and error message
Comment 2 Jean-Pierre Ledure 2018-07-17 13:43:36 UTC
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
Comment 3 Xisco Faulí 2018-07-18 18:21:33 UTC
Created attachment 143627 [details]
sample file
Comment 4 Xisco Faulí 2018-07-18 18:28:41 UTC
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
Comment 5 Drew Jensen 2018-07-18 19:19:10 UTC
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
		                        
....
Comment 6 Commit Notification 2018-07-19 17:03:16 UTC
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.
Comment 7 Drew Jensen 2018-07-20 20:20:14 UTC
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.