Issue 3860

Hi Ian,

sorry, but your bug description is rather useless. Please belive me
that in general, ODBC data sources and queries should (and do) work.

So please kindly provide:
* which type of database are you accessing (version, running on the
same machine or somewhere else, whatever)
* which driver version do you use, which driver if it's not an obvious
one (where do we get it to reproduce the problem)
* what is the structure of the table you try to access
* what is the query you try to build and execute
* what exactly are you doing

All of these would help ....

reassign

The database is UniVerse version 9.4 on AIX 4.2.1 - UniVerse is 
available from
IBM/INFORMIX and is supplied with ODBC  drivers for MS 95/98/NT.  A 2 
user developer
copy of UniVerse version 10 (the latest release) can be obtained from 
IBM for either
NT/2000 or Redhat Linux at no cost to developers.  UniVerse is 
classified under IBMs
U2 database product set.

Client Config:
NT Workstation 4.0 with service pack 5
MS ODBC Driver Manager is 3.520.5303.2
Client ODBC Driver is Informix (UniVerse) 3.6

I am trying to return data from the database using the query tool in 
OpenOffice.org
641

With OpenOffice.org 641 I can

    Define an ODBC  datasource (using Spreadsheet\Tools\Data 
Sources\New Data Source)
    Connect to the data source (using  Spreadsheet\Tools\Data Sources)
    Create a new query (using Spreadsheet\Tools\Data Sources\New 
Query Design)
    Select any table from the available tables displayed
    Select columns to display in the query i.e. build the query

but, when running the query the message
"The Data Content could not be loaded [Microsoft][ODBC Driver 
Manager] Driver does not
support this function"
is displayed immediately and no data is returned at all.

The query below is simple;  however any query to any table in the 
schema fails with
the identical message whether entered as SQL or created by selecting 
the columns from
the table

SELECT "BILL_TO_REF", "NAME1", "ADDRESS1" 
FROM "CUSTOMER_MASTER" "CUSTOMER_MASTER"

This query is identical to a query submitted with StarOffice 5.2 and 
6 which returns
data as expected (Unfortunately StarOffice 6 beta has now expired.)

Structure of the CUSTOMER_MASTER table:

AT_ID primary key CHARACTER,10
BILL_TO_REF CHARACTER,8
NAME1 CHARACTER,30
ADDRESS1 CHARACTER,30

What baffles me is that applications that do work using the ODBC 
driver  include:

StarOffice 5.2
StarOffice 6.0
Cognos Impromptu
Crystal Reports
Microsoft Office Query 95/97/2000/XP

I am experiencing exactly the same problem as Ian, However my 
configuration is slightly different;
Client Config
Windows 2000 SP2 
MS ODBC Driver Manager 3.52.6019
Client ODBC Driver - Ardent Universe 3.07.01

Database is Universe V 9.5.1 for HPUX 11.0
I can also link via Staroffice 6.0 Beta, Cognos and Microsoft query

I am experiencing exactly the same problem as Ian, However my 
configuration is slightly different;
Client Config
Windows 2000 SP2 
MS ODBC Driver Manager 3.52.6019
Client ODBC Driver - Ardent Universe 3.07.01

Database is Universe V 9.5.1 for HPUX 11.0
I can also link via Staroffice 6.0 Beta, Cognos and Microsoft query

I've got the same error using ODBC middleware from Simba. (our native database, Simba 
ODBC networking, Simba ODBC client for Win32).
My client OS is any Win32; the client software versions seem to be irrelevant.

A support request to Simba lead to the following answer, which might help:

<cite>
We reproduced this issue without going through client server. OpenOffice appears to be copying the 
access style of MS Jet very closely. They may in fact be using ADO 'under the hood'. I believe it 
is the failed call to SqlFetchScroll (which ultimately resolve to an extended fetch) that is 
causing the problems.
...
However, having gone over the error logs, we strongly suspect that the use of fetch scroll instead 
of fetch by open office is the problem. OpenOffice seems to be the problem here as it should be 
querying SqlFunctions before choosing which to use and it doesn't appear to be doing that.
</cite>

Im *very* interested in a solution. If someone needs more information, e.g. log file contents, 
please contact me.

Xaver,

log files can't do any harm ... you could attach them to this issue,
if they do not contain any sensitive data.

I grab the ownership of this bug for the moment, as we originally
assigned it to Ian to get more info (which we now seem to have :).

I'd like to set this to "Unconfirmed", as we do not have the databases
to reproduce (UniVerse & Simba), but this is not possible once a bug
is "New".

Anyway, I'll grab it for the moment, though I can't promise anything.
Perhaps we can have a look at the logs, and try a solution in theory
and then supply somebody with a patched lib. But please do not expect
any time frame for this :)

Frank

Created attachment 1837 [details]
client SQL log file

Simba has tested ODBC access with OpenOffice with a modified 
development driver. I've got the following statement this morning:

<cite>
We can now say with certainty that Open Office needs to make a patch 
for this.  Apparently, it doesn't check to see if extended fetch is
available on the driver or not (it just assumes it does).  Moreover, 
(and this is what's the confusing part) they seem to use a regular 
fetch for the metadata, but when it comes to real data, it defaults to 
extended/scrolling fetch without checking with the driver's 
capabilities.  That's the issue that you're seeing.
</cite>

In short: OpenOffice must test if the ODBC driver supports 
the function SQLFetchScroll. If not, it must use SQLFetch.

Simba is working on an improved driver which supports 
SQLFetchScroll. They will publish their new driver somewhen in future 
(this year, I hope...). But until then and for other databases not 
supporting ODBC 3.0 OpenOffice should ask whether SQLFetchScroll is 
supported by the ODBC driver.

Well, the general statement for ODBC access in OOo is that ODBC 3.0 is
the required minimum. At the time we decided this (long time ago), it
seemed reasonable - it saves the costs of maintaining separate drivers
(which would be necessary for performance reasons) for ODBC 2 and ODBC
3, and we though (and still think) that the majority of drivers should
work this way.

Of course it's not really satisfying, if it excludes too many
"real-world" installations.

Writing and maintaining an own ODBC 2 (-only) driver seems to be out
of reach to me - I simply think that the effort (which is quite high)
is not worth the gain. The point is that a lot of drivers support ODBC
3 partially, the ones which are pure ODBC 2 are a minority (in my
opinion).

Instead, I think a possible way might be to customize the existent
driver in some aspects where it currently uses an ODBC 3
functionality, to move ODBC 2 (at specific points and upon request only).

This may be possible for the SQLFetchScroll problematic, but needs
investigation.

Xaver, before this I would be interested in more details the Simba
people told you. For instance, I am somewhat curious why the ODBC
driver does not translate our SQLFetchScroll call to SQLExtendedFetch
calls - by definition, it _should_ when it encounteres a driver which
does not support ODBC 3. So I am wondering where exactly the call
fails - does the manager not translate the call, or does it translate
it, and the translated calls fail in Simba, anyway?

In addition, it would be interesting what else would need to be
adjusted for this special problem.

It may be possible to use SQLExtendedFetch instead of SQLFetchScroll
in our driver (as said, may need more investigation, also with respect
to the resulting performance loss which would hit all ODBC connections).
But if this only reveals another unsupported ODBC 3 method, and
probably more after this, then we may need to draw a line - as
mentioned, I do not think that we want to move _too_ far into the
direction of an own ODBC 2 driver (and this is where this would go).

Maybe we can create a prototype library (I am not promising anything
here :), and you can check what happens then ... Have to evaluate ...

Created attachment 4057 [details]
very preliminary, non-production, untested test version

the attachement contains a version of the 2 relevant driver libraries,
compiled for OOo 1.0.3 (which is no yet out, but 1.0.2 should work, too).

Beware:
* This is not tested in any way, it comes WITHOUT ANY WARRANTIES, USE
IT AT YOUR OWN RISK. If it explodes your nearest reactor when you use
it, or anything like that - don't complain, you have been warned.

* The only thing done here is that the SQLFetchScroll( NEXT ) call
when traveling (forward) through a result set has been replaced with a
SQLFetch call. It is in no way sure that this (even if it works) is
sufficient. There is no replacement for other SQLFecthScroll-calls
(for instance positioning absolute).
With a high probability, some code in DBA will use driver methods
which rely on such other SQLFetchScroll calls, and thus fail. I don't
know if this happens immediately after connection, or years later.

targeting

Ocke, now that we have at least one confirmed report that the patched
driver worked, please evaluate if we can use SQLGetFunctions(
SQL_API_SQLFETCHSCROLL ) to determine the availability of
SQLFetchScroll. We should try to decide at runtime if we use
SQLFecthScroll or SQLFetch/SQLExtendedFetch, if this doesn't have side
effects on existing drivers.

Hello,

I fixed this in cws dba02.

Best regards,

Ocke

Open to send to QA

Please verify in dba02

fixed in CWS DBA02

hi ian and xavier,

can you please verify this again in a OOo 1.1 Beta which will be 
available soon.

Thanks Marc

As mentioned on the qa dev list on March 5th I will close all resolved
<wontfix/duplicate/worksforme/invalid> issues. Please see this posting for details. 

Hi

Many thanks for working on this issue

What version of OpenOffice has the changes, and from where does one 
download it.

Regards

Ian

> What version of OpenOffice has the changes, and from where does one 
> download it.

As m4 is the last public avaiable snapshot
(http://www.openoffice.org/dev_docs/source/644/), and the fix made it
into m4s3 only, I fear there is no such public build, yet. Currently,
OOo 1.1 Beta is being built, so I'd like to ask you to wait for this
version. Thanks :)

I'm not sure if this should be a new issue as this appears to be a 
variation of the original problem on OpenOffice1.0.x that is being 
experienced with OpenOffice1.1Beta.

OpenOffice1.0.2 with the modified odbc driver worked with the 
configuration as described earlier.  However OpenOffice1.1beta does 
not work as expected i.e. fails in a similar way to OpenOffice1.0.2 
when run without the modified odbc driver.

From with OpenOffice1.1Beta spreadsheet Tools\Data Sources

1. Create new data source - successful
2. Connect to data source - successful
3. Click on "Tables"
   Only "All Tables" check box appears - not a list of all tables
4. Create SQL query (SELECT * FROM CUSTOMER_MASTER)
   This results in "Error while connecting to Data Source"
   When click on "More" just displays "Error" - no detail

I then downloaded StarOffice 6.1 Beta 1 - this works as expected.

Many thanks

Ian Stuart

Apologies for reopening this issue in error - The problem only seems 
to be on  AIX 4.3.3 database server.  OpenOffice1.1Beta connects to 
our Redhat UniVerse database server as does StarOffice 6.1 beta on the 
same revision of UniVerse 10.0.4

Many thanks

Ian Stuart

close this because all should be fixed in beta2

change subcomponent to 'none'