Apache OpenOffice (AOO) Bugzilla – Issue 75442
ODBC connection IBM UniVerse fails to retrieve meta-data and/or data
Last modified: 2013-08-07 15:45:17 UTC
Please refer to issue 3860 - the "symptoms" of the current problem are very similar to those reported there. Using IBM UniVerse ODBC driver, can successfully define and test connection to a UniVerse database - . However when trying to access a table or create a query the following occurs User is prompted for username and password Almost immediately a message is displayed as follows: soffice.bin.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created On restarting OOo, the recovery process starts up and, after manually starting the errorlog an error was reported - The ID of the error report is rpvn2b. I have played with various combinations of the Advanced Settings but the problem remains the same. As reported in previous issue 3860, the IBM UniVerse ODBC driver is used to access UniVerse from StarOffice 5.2 (yes we still use 5.2 as it is the only way we can successfully create OOo documents via the ODBC on users PCs that do not have MSOffice or other expensive software installed), MSAccess, MSQuery, COGNOS IMPROMPTU, Crystal Reports and other ODBC-compliant applications. Regards Ian Stuart
Unfortunately the report whose ID you mentioned does not contain any useful data. Currently waiting for Ian's feedback to (similar) issue 75444, I have hope we can both prove to be duplicate to issue 74732.
No, it seems that 75442 is still a problem, ie ODBC connections to the same datasources as accessed via JDBC fail although the symtoms are now different. However all is not lost as there is definitely an improvement over previous OOo versions and testing I've done with UniVerse. As background - UniVerse supports 2 types of SQL "schemas" 1. a "true" SQL schema with Catalog defintions, triggers, constraints, views etc which are enforced by the DBMS and 2. a "free-form" "ACCOUNT" which enables the user to create non-SQL tables/files, and implement rules in client applications only (although triggers are also supported). (A third option also exists in that non-SQL tables can be created in SQL Schemas - used for speed eg hashed tables). However non-SQL files are "sqlised" on the fly by the ODBC driver as long as the dictionaries of these files have been massaged to make the data ODBC compliant and the Account is marked as accessible via ODBC. The symptoms are different when connecting to a SQL schema vs Universe ACCOUNT as follows: Connecting to "true" SQL Schema: I can connect to the data source but no tables are shown. Trying to select Query facilities, eg Create Query in SQL View does not enable me to access the database or create queries. This is unusual as, for example, "true" UniVerse SQL Schemas are more easily accessed via ODBC applications than UniVerse Accounts as the Schema and catalog take care of everything, ie there is no manual intervention required to make non-SQL tables accessible via ODBC. I have also tested that the username has been GRANTed the correct priveleges. Also, I do get the same error message as reported in 75442 when OOo crashes. Connecting to an UniVerse Account: The behaviour changes slightly here: While OOo does not retrieve/display the table definitions as for SQL Schemas, I am able to create a query in Create Query in SQL View and retrieve data from the table - however trying to then enter design view results in a message stating that the "CUSTOMER_MASTER" is not a table or a query. I have tried disabling schema and catalog names in Advanced Settings but that makes no difference. In an Account I can also access the facilities to create tables and the correct data types for columns is shown, which is not the case with the JDBC, although UniVerse disallows actual creation of the tables as SQL DML is not permitted in UniVerse accounts. In both cases (Schemas and Accounts) if I check on the server a connection has been made and a call to obtain the schema details is made. Regards Ian Stuart
Hi Frank, If it were possible to provide you with a copy of the most recent release of UniVerse and sample databases would you be willing to have another look at the ODBC and other JDBC issues. UniVerse 10.2 has a much easier installation process with easier to use client tools to manage the DBMS. If so how would I get the CDs to you. I really think you are close to having the ODBC work - it works with nonSQL Schemas in that I can create SQL queries; the JDBC it seems is working quite ok except for not being able to create databases which at this time is not a real issue but would be nice to have. Also, UniVerse 10.2 has a built in Web Services server - would it be possible to retrieve data via SOAP/Web Services I'm meeting with Susie Siegusmund who heads up the U2 (UniVerse & UniData) database division on 16th April - I will also ask her whether IBM would be willing to provide some resources - would this be acceptable to you if they could provide some technical input - how would they contact you. The IBM U2 team port Universe to Solaris, AIX, HP-UX systems as well as Redhat/SUSE and Windows. If I knew where to start looking in OOo I would , but I think the complexity of OOo is going to put any meaningful input outside of my reach. Regards Ian Stuart
sorry for the delay in response, there were some bank holidays here ... Yes, if I could evade the pain of installing Universe again, this would would increase my willingness to look into the problems - the easier to install, the better. I cannot do any promises, since, honestly, Universe support isn't requested too often, which effectively means its priority is not very high (hey, what about your company sponsoring a developer (for a month or so) who looks into the problems, with our help?). So, please take this as expression of willingness, but no promise. > If so how would I get the CDs to you. If you cannot put them online for download somewhere, I could mail you my business address. > Also, UniVerse 10.2 has a built in Web Services server - would it be > possible to retrieve data via SOAP/Web Services There's no driver for this kind of data currently. > I'm meeting with Susie Siegusmund who heads up the U2 (UniVerse & > UniData) database division on 16th April - I will also ask her > whether IBM would be willing to provide some resources - would > this be acceptable to you if they could provide some technical > input - how would they contact you. The IBM U2 team port Universe > to Solaris, AIX, HP-UX systems as well as Redhat/SUSE and Windows. This sounds even better. I know that IBM has a great interest in OpenOffice.org, so it might be worth asking them whether they are willing to sponsor somebody investigating the problems. In any case, dev@dba.openoffice.org is the public place for somebody embarking on this. > If I knew where to start looking in OOo I would , but I think the > complexity of OOo is going to put any meaningful input outside of my > reach. I currently implement some logging facilities in some selected SDBC drivers in OOo - definitely the JDBC bridge, not yet sure about the ODBC bridge. My hope is that this can help in the future to do some remote investigations.
Hi Had a reasonably positive meeting with the IBM and the local distributor of U2 products re assisting with this issue and others related to JDBC and ODBC Initially, the distributor has agreed to provide a technical support person to assist who will also have contact with IBM. What skills level do you envisage this person will need to have, eg should they be C++ proficient etc. Regarding logs - how does one turn the SDBC logging on if it is available at OOo 2.2 Regards Ian Stuart
Ian, sorry for the delay, have been off for a while (and probably will the next weeks, again, so you'll find me responding irregularily only). Well, somebody with the ability to debug the problem would be ideal, of course. In this case s/he could try to find out what's going on, and we could discuss possible technical solutions. So, if you have somebody with C++ background, http://wiki.services.openoffice.org/wiki/O3-build could allow to kickstart compiling OOo. I'd be glad to discuss pointers where to look in the code in dev@dba.openoffice.org. Re logging: That's not available in 2.2, yet, it is near before integration into the upcoming developer builds (which would be sufficient for the purpose of obtaining a log, I suppose). However, I did not finish it, yet, and for the absense reasons mentioned above, I might need a few more weeks. Perhaps I am able to publish a preliminary build with those logging capabilities, will evaluate. Linux and Windows non-product (http://wiki.services.openoffice.org/wiki/Non_Product_Build) might be easy to do.
Ian, with snapshot build 680m218 (should materialize soon at http://download.openoffice.org/680/index.html), you can turn on JDBC logging as described in http://wiki.services.openoffice.org/wiki/Logging_JDBC_Activity. Perhaps this can give us some insights.
Argh. The logging is for JDBC, and this issue is about ODBC, isn't it? Sorry, this will not help us, then.
does this problem still occure in a current version?
Yes, this still occurs at OOo 2.4.0 as described 16 Mar 2007. Many thanks for keeping this issue open
change owner
Have tested UniVerse ODBC connection with OOo 3.0.0 OOO300m8 with some success and some previously experienced issues remain. 1. After defining the data source it is still necessary to run the following macro before attempting to connect to the datasource REM ***** BASIC ***** Sub Main oDBC = createUnoService( "com.sun.star.sdb.DatabaseContext" ) oDB = oDBC.getByName( "file:///C:/path/to/database/document.odb" ) Dim nFilterMode as Integer nFilterMode = 0 AddDataSourceSetting( oDB, "TableTypeFilterMode", nFilterMode ) Dim bBeRespectful As Boolean bBeRespectful = TRUE AddDataSourceSetting( oDB, "RespectDriverResultSetType", bBeRespectful ) oDB.DatabaseDocument.store() End Sub Function AddDataSourceSetting( oDB as Object, sSettingsName as String, aSettingsValue as Variant ) as Variant ' append the new setting Dim aInfo as Variant aInfo = oDB.Info aInfo = AddInfo( aInfo, sSettingsName, aSettingsValue ) oDB.Info = aInfo End Function Function AddInfo( aOldInfo() as new com.sun.star.beans.PropertyValue,sSettingsName as String, aSettingsValue as Variant ) as Variant Dim nLower as Integer Dim nUpper as Integer nLower = LBound( aOldInfo() ) nUpper = UBound( aOldInfo() ) ' look if the setting is already present Dim bNeedAdd as Boolean bNeedAdd = TRUE Dim i As Integer For i = nLower To nUpper If ( aOldInfo( i ).Name = sSettingsName ) Then aOldInfo( i ).Value = aSettingsValue bNeedAdd = FALSE End If Next i ' allocate the new array Dim nNewSize as Integer nNewSize = ( nUpper - nLower ) If bNeedAdd Then nNewSize = nNewSize + 1 Dim aNewInfo( nNewSize ) as new com.sun.star.beans.PropertyValue ' copy the elements (a simply copy does not work in Basic) For i = nLower To nUpper aNewInfo( i ) = aOldInfo( i ) Next i ' append the new setting, if necessary If ( bNeedAdd ) Then aNewInfo( nUpper + 1 ).Name = sSettingsName aNewInfo( nUpper + 1 ).Value = aSettingsValue End If AddInfo = aNewInfo() End Function Once this macro has been run it is then possible to connect to the database without OOo crashing. 2. Change the database advanced settings not to use Catalog name Thereafter it is possible to create an SQL using Create Query in SQL View and selecting Run SQL command directly. Remaining problems include: When using Create Query in Design View, all the tables and columns are shown and can be joined and columns selected for the query. However on running the query or when attempting to switch design view off OOo crashes. The Query Wizard crashes on "Finish" with the following error dialog Runtime Error! Program c:\Program Files\OpenOffice.org 3\program\soffice.bin R6025 - pure virtual fucntion call