Apache OpenOffice (AOO) Bugzilla – Issue 3860
ODBC query results in The Data Content could not be loaded [Microsoft][ODBC Driver Manager] Driver does not support this function
Last modified: 2006-05-31 14:29:06 UTC
When submitting a query to an ODBC data source an error message results. SQL Status: IM001 [Microsoft][ODBC Driver Manager] Driver does not support this function However when building the query all tables are accessible and columns can be selected. This occurs when submitting a query built thru Spreadsheet\Tools\Data Sources\Queries The ODBC source is however accessible from StarOffice 5.2 and 6.0 as well as other tools such as MSQuery, COGNOS Impromptu, Crystal Reports Seagate etc.
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'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'