Bug 122779 - FILEOPEN (BASE) Connection to Oracle DB via ODBC ends in [ODBC][ORACLE] Optional Feature not implemented
Summary: FILEOPEN (BASE) Connection to Oracle DB via ODBC ends in [ODBC][ORACLE] Optio...
Status: NEEDINFO
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.3.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisectRequest, regression
Depends on:
Blocks:
 
Reported: 2019-01-17 07:53 UTC by Thierry Menigoz
Modified: 2020-05-13 11:19 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Screen captures of error and LibO "About" (2.54 MB, application/vnd.oasis.opendocument.text)
2019-01-17 07:53 UTC, Thierry Menigoz
Details
Screen captures of Oracle ODBC Driver parameters (97.20 KB, application/vnd.oasis.opendocument.text)
2019-01-17 08:21 UTC, Thierry Menigoz
Details
bt Windows (windbg) (44.00 KB, text/plain)
2020-04-28 10:21 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Thierry Menigoz 2019-01-17 07:53:34 UTC
Created attachment 148384 [details]
Screen captures of error and LibO "About"

I had been using for a long time a connection to Oracle Database data through Base using the Oracle ODBC Driver and this was perfectly working until I updated to LibreOffice 6.2 (Orignally 6.2.1 and now 6.2.2 but does not change result).
I tried to return to LibreOffice 6.1.4 (before installing 6.2, I was running 6.1.2) but, unfortunately any call to display a table, a view or a query ends up with message [ODBC][ORACLE] Optional Feature not implemented.
Error displayed is HYC00.
I tried to reset my UserProfile but no improvement.

I underline that the ODBC driver is fully operational when used from other Windows apllications like MS Excel or MS Access.

I tried to re-create a new Oracle ODBC linkage and connect through it but same message appear. Curiously, when testing connection from my Base File properties menu, test is OK.
Comment 1 Thierry Menigoz 2019-01-17 08:21:02 UTC
Created attachment 148386 [details]
Screen captures of Oracle ODBC Driver parameters

I forgot to highlight that, for compatibility reasons with our ERP system, Oracle Driver is a 32-bit version and i always install LibreOffice in 32-bit version as the 64-bit does not support 32-bit drivers
Comment 2 Julien Nabet 2019-01-17 08:36:57 UTC
There's no 6.2.1 or 6.2.2 yet.

Just to be sure, there's no problem with 6.1.2 but you've got problem with 6.1.4 and 6.2.0?
It could be interesting to have the result with 6.1.3 so we'd know exactly from which LO version the pb appears.
Comment 3 Thierry Menigoz 2019-01-17 11:56:39 UTC
Sorry, my mistake, last updates were 6.2.0.1 & 6.2.0.2 :(

I downgraded to version 6.1.2.1 from my original sources refreshing to a new User Profile through the FailSafe menu and I confirm that I have not the above mentioned error launching display of data in Tables, Views or Queries.

I then upgraded to version 6.1.3 and error re-appear with this version ...
Comment 4 Julien Nabet 2019-01-17 18:13:52 UTC
Ok so the earliest version is 6.1.3.2 and it's a regression.
Comment 5 Xisco Faulí 2019-03-21 12:53:15 UTC
Hello Thierry,
LibreOffice 6.2.2.2 is going to be released today, could you please try again
with this version to see if the problem has been resolved meanwhile? Thanks in
advance
Comment 6 Thierry Menigoz 2019-03-21 13:30:02 UTC
Dear Xisco,

I recently installed in parallel Version 6.2.2.1 and bug is still present in this version.
I will try with version 6.2.2.2 when available and keep you posted.
Comment 7 Thierry Menigoz 2019-03-25 09:59:30 UTC
I did a fresh install of version 6.2.2.2 and still get the same error # HYC00 - [Oracle][ODBC]Optional feature not implemented.

As before, list of Oracle Schemes, Tables or Views is displayed but error happen when I try to display data of these Tables or Views or Queries registered in a LibO Base file.
Note that all queries registered in the Base file are with the "Direct Execution of SQL Statement" feature enabled.
Comment 8 Thierry Menigoz 2019-07-23 07:30:22 UTC
I installed Version 6.3.0.1 - 32 bits and error do not appear anymore!
I am again able to display Table data, views or result of queries.

Therefore, I change status to "Resolved" / "Fixed"
Comment 9 Robert Großkopf 2019-07-23 07:48:02 UTC
There hasn't been submitted a fix for this bug. Seems the bug has been gone without fixing. So I changed to "Worksforme"
Comment 10 Johan 2020-04-09 17:15:56 UTC
It be great if a real solution could be provided instead of the workaround to install 32 bit LO. I have multiple users with 64 bit LO installed who can't open dashboards in Base / Calc anymore due to this issue. 

Latest LO 5 series, 5.4.7.2, 64 bit, works without any issue. Users with LO 6, 64 bit, consistently report 'HYC00 - Optional feature not implemented'.
Comment 11 Julien Nabet 2020-04-09 18:06:57 UTC
(In reply to Johan from comment #10)
> It be great if a real solution could be provided instead of the workaround
> to install 32 bit LO. I have multiple users with 64 bit LO installed who
> can't open dashboards in Base / Calc anymore due to this issue. 
> 
> Latest LO 5 series, 5.4.7.2, 64 bit, works without any issue. Users with LO
> 6, 64 bit, consistently report 'HYC00 - Optional feature not implemented'.

What about using a 64 bit driver with LO 64 bits?
If there's no 64 bits driver, only 32 bits, it's expected to require LO 32 bits.
Comment 12 Johan 2020-04-20 14:53:46 UTC
(In reply to Julien Nabet from comment #11)
> What about using a 64 bit driver with LO 64 bits?
> If there's no 64 bits driver, only 32 bits, it's expected to require LO 32
> bits.

By default we're using the 64 bit ODBC driver with LO 64 bit. When I remove the 64 bit ODBC driver, install only the 32 bit ODBC driver and create a DSN in the 32 bit windows configuration interface, LO Base responds with:

"The specified DSN contains an architecture mismatch between the Driver and Application"
Comment 13 Julien Nabet 2020-04-20 15:00:07 UTC
(In reply to Johan from comment #12)
> (In reply to Julien Nabet from comment #11)
> > What about using a 64 bit driver with LO 64 bits?
> > If there's no 64 bits driver, only 32 bits, it's expected to require LO 32
> > bits.
> 
> By default we're using the 64 bit ODBC driver with LO 64 bit.
Does this config work?

> When I remove
> the 64 bit ODBC driver, install only the 32 bit ODBC driver and create a DSN
> in the 32 bit windows configuration interface, LO Base responds with:
> 
> "The specified DSN contains an architecture mismatch between the Driver and
> Application"
If you use LO 64 bits with 32 bit ODBC driver + DSN in the 32 bit Windows conf, I think it can be expected.
What about if in this 32 bit case, you uninstall LO 64 bits and install LO 32 bits?
Comment 14 Johan 2020-04-20 15:44:56 UTC
> > By default we're using the 64 bit ODBC driver with LO 64 bit.
> Does this config work?
No, see above comment. On LO 5.4.7.2 64 bit this works. Since LO 6 64 bit it doesn't work.

> What about if in this 32 bit case, you uninstall LO 64 bits and install LO
> 32 bits?
Haven tested it, but a previous comment(#8) suggests this works. Corporate is pushing 64 bit LO. So this is not really something I can check, nor is this something that scales to the other users.
Comment 15 Julien Nabet 2020-04-20 17:04:30 UTC
(In reply to Johan from comment #14)
> > > By default we're using the 64 bit ODBC driver with LO 64 bit.
> > Does this config work?
> No, see above comment. On LO 5.4.7.2 64 bit this works. Since LO 6 64 bit it
> doesn't work.
> 
> > What about if in this 32 bit case, you uninstall LO 64 bits and install LO
> > 32 bits?
> Haven tested it, but a previous comment(#8) suggests this works. Corporate
> is pushing 64 bit LO. So this is not really something I can check, nor is
> this something that scales to the other users.

I think we must focus on the error with full 64 bits or full 32 bits but not with hybrid since expected.

The pb is: "HYC00 - Optional feature not implemented" is quite generic and don't provide much hints.
Searching about this, I got:
    290 Reference< XPreparedStatement > SAL_CALL OConnection::prepareCall( const OUString& /*sql*/ )
    291 {
    292     ::dbtools::throwFeatureNotImplementedSQLException( "XConnection::prepareCall", *this );
    293     return nullptr;
    294 }
See https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/odbc/OConnection.cxx?r=1dd9200b#292

and
    450 void SAL_CALL OConnection::setTypeMap( const Reference< css::container::XNameAccess >& /*typeMap*/ )
    451 {
    452     ::dbtools::throwFeatureNotImplementedSQLException( "XConnection::setTypeMap", *this );
    453 }
See https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/odbc/OConnection.cxx?r=1dd9200b#450

But I expect it fails even if we full 64 bits or full 32 bits install.
Comment 16 Thierry Menigoz 2020-04-22 09:50:49 UTC
From my experience, the 32-bit driver works again with the 32-bit LibO since release of 6.3.
The point is that, when extracting a big amount of data (i.e. more that 10000 lines or so), LibO 6.3 or 6.4 in 32-bit installed on my Win10 64-bit OS crashes when saving the files including these extractions making them virtually unusable.
As it is very difficult (impossible?) to get both 32-bit and 64-bit Oracle drivers working on the same machine (and our ERP system is designed to work exclusively with the 32-bit Oracle Driver), I then cannot got for a full 64-bit solution.

My current work around is to use a parallel install of LibO 6.1 in 32-bit that allows me to save the file after extraction (a bit slow to save the files but, well, it works) and then re-open the file with LibO 6.4 64-bit that allows me to do the calculations and Pivot Tables as the 64-bit does not crash when saving these big files (and is also quicker than the 32-bit edition).

For the crashes in saving big Calc files with 32-bit version, I have never been able to identify the reason. The file closes without any message, WinDebug installed and used as detailed in the WiKi, gives no backtrace and when tracing the use of memory and CPU's, nothing looks wrong. Usually, the progress bar goes quickly to around 90% of progress and then very slowly before everything collapsed before the last percents of the bar were reached. I did fresh reinstall, delete my profile, tried to adjust memory parameters, tried to close all other applications to free up all capacities but all were, until now, unsuccessful.
In my tests, I also tried with a file only with extracted data and no other calculation or spreadsheet but he results the same. I just noticed that the status bar message shown seems always the same : "calculating cells height ..." I never found a way to disable this "cells height adjustement" and therefore have not been able to confirm if this is the reason for the crash.
Comment 17 Julien Nabet 2020-04-22 10:02:09 UTC
Thierry: do you have a machine on Linux to give it a try?

If not, ideally, it could be useful to build sources with debug (enable-dbgutil) see https://wiki.documentfoundation.org/Development/BuildingOnWindows.
Of course, I know it takes some time to retrieve prerequisites (Visual C++, Cygwin, etc.), download sources, build...

I build Lo sources on a Linux machine with enable-dbgutil but I don't have Oracle DB.
I know there's Oracle Express but it's 2GB download and am not even sure I may install it without pb on Linux Debian. Moreover, I suppose it needs some config/tuning.
Comment 18 Thierry Menigoz 2020-04-22 13:16:26 UTC
I do have Linux machine but at home and not connected to the Oracle System at my work. So, I cannot give it a try :(

Regarding a possible build, I had a quick look into the link you provided and this looks a process a bit beyond my own capabilities ...
As a workaround, do these build sources need to be machine-specific or would it be possible to use one of yours with the enable-dbgutil option to test on my work computer that runs under Win10?

Regarding Oracle Drivers, yes these need some config/tuning and it has got a bit dificult nowadays as Oracle has limited access to most of its knowledge database.
Comment 19 Julien Nabet 2020-04-22 13:35:23 UTC
(In reply to Thierry Menigoz from comment #18)
> ...
> Regarding a possible build, I had a quick look into the link you provided
> and this looks a process a bit beyond my own capabilities ...
It's more a matter of time rather than a complex thing.

> As a workaround, do these build sources need to be machine-specific or would
> it be possible to use one of yours with the enable-dbgutil option to test on
> my work computer that runs under Win10?
I'm on Linux too at home but even if had Win10 at home, I wouldn't know how to create an enable-dbgutil build.

> 
> Regarding Oracle Drivers, yes these need some config/tuning and it has got a
> bit dificult nowadays as Oracle has limited access to most of its knowledge
> database.
I wonder why some try to migrate away from Oracle... :-)

Xisco: any idea how to provide a Win10 debug master build? I don't see it on https://dev-builds.libreoffice.org/daily/master/ ?
Comment 20 Julien Nabet 2020-04-28 10:18:19 UTC
On Win10 64 bits with master sources updated today, I could open an Oracle DB with ODBC.

I installed "Oracle Instant Client Basic"
+ pack "ODBC Oracle Instant Client"

Here extra steps I did:
- unpack "Oracle Instant Client Basic" in C:\oracle\instantclient_19_6
- unpack "ODBC Oracle Instant Client" in C:\oracle\instantclient_19_6
- create subdirs: C:\oracle\instantclient_19_6\network and C:\oracle\instantclient_19_6\network\admin
- created a file Tnsnames.ora in C:\oracle\instantclient_19_6\network and C:\oracle\instantclient_19_6\network\admin
- filled Tnsnames.ora by taking example from https://www.orafaq.com/wiki/Tnsnames.ora
- modified system env var path to add "C:\oracle\instantclient_19_6"
- created a new system env var TNS_ADMIN : C:\oracle\instantclient_19_6\network\admin
- restart PC so sys var envs changes are taken into account
- create User datasource

Then I created a brand new odb file and use ODBC connection + require password, selected the connection and tested it ok.

Finally, I could open a table but noticed this on console:
warn:legacy.osl:11208:1336:connectivity/source/drivers/odbc/OConnection.cxx:68: Failure from SQLDisconnect
Comment 21 Julien Nabet 2020-04-28 10:21:05 UTC
Created attachment 160021 [details]
bt Windows (windbg)

Here's a bt when trying to close table.
Comment 22 Julien Nabet 2020-05-12 17:17:23 UTC
I noticed that some functions weren't implemented in ODBC:
- setFetchDirection
- getBinaryStream
- getCharacterStream
- getArray
- getClob
- getBlob
- getRef
- updateLong
- hashBookmark

Thierry: just for the test, do you reproduce the crash with a table containing just a key field (NUMBER) and a test field (VARCHAR2)? Indeed, I just wonder if it could be due to a type which would need a non implemented yet function.

Before testing, give a try to recent (if possible last one) ODBC Oracle driver, see https://bugs.documentfoundation.org/show_bug.cgi?id=122779#c20
Comment 23 Julien Nabet 2020-05-13 09:07:30 UTC
Lionel: I'm not sure the bt I retrieved corresponds to the Thierry's crash since it's not at the same moment but it seems we still got ressource management (that's why I put these 2 other tdfs in cc).
Any thoughts?
Comment 24 Lionel Elie Mamane 2020-05-13 10:19:33 UTC
So, if I skimmed correctly, this bug is not anymore about "optional feature not implemented", but about a crash when getting "a lof of data" now?
Comment 25 Julien Nabet 2020-05-13 10:39:22 UTC
(In reply to Lionel Elie Mamane from comment #24)
> So, if I skimmed correctly, this bug is not anymore about "optional feature
> not implemented", but about a crash when getting "a lof of data" now?

There are perhaps 2 bugs (related or not I don't know):
- error message, Optional Feature not implemented. Error displayed is HYC00.
- the crash I retrieved
Comment 26 Lionel Elie Mamane 2020-05-13 10:52:18 UTC
For the "optional feature not implemented", does the button "more" on the error message give any more information?
Comment 27 Lionel Elie Mamane 2020-05-13 11:05:35 UTC
The backtrace looks like a crash in the deletion of m_pRowStatusArray. Weird, unless the ODBC driver takes ownership of the pointer when we pass it... which would be *really* unexpected.
Comment 28 Lionel Elie Mamane 2020-05-13 11:11:43 UTC
Thierry, could you try without the "force WCHAR" option in the driver?
Comment 29 Julien Nabet 2020-05-13 11:19:51 UTC
(In reply to Lionel Elie Mamane from comment #26)
> ...
> The backtrace looks like a crash in the deletion of m_pRowStatusArray.
> Weird, unless the ODBC driver takes ownership of the pointer when we pass
> it... which would be *really* unexpected.

I tested several things:
1) I put in OResultSet dtr:
osl_atomic_increment( &m_refCount );
m_pSkipDeletedSet.reset();
m_pRowStatusArray.reset();
osl_atomic_decrement( &m_refCount );

since m_pSkipDeletedSet is initialized with m_pSkipDeletedSet.reset( new OSkipDeletedSet(this) );

Same thing

2) I also tried to convert unique_ptr to simple pointer like in OStatement
osl_atomic_increment( &m_refCount );
m_pSkipDeletedSet.reset();
delete[] m_pRowStatusArray;
osl_atomic_decrement( &m_refCount );

Idem

I also tried to add:
setStmtOption<SQLUSMALLINT*, SQL_IS_POINTER>(SQL_ATTR_ROW_STATUS_PTR, nullptr);
before deleting or reset, idem.

It doesn't crash only if it's simple pointer and no delete[] (but I suppose it would leak here)

with all mechanism of dispose, release, close, weak ref, component helper, Reference, dtr + the fact that ODBC driver does its own ressource management and LO must adapt to it, it's not easy to disantangle all this.