Apache OpenOffice (AOO) Bugzilla – Issue 15113
JDBC Query fails to retrieve metadata or data
Last modified: 2006-05-31 14:29:06 UTC
Hi, On OOo1.03 I can define and connect to a JDBC datasource and "manually" enter a SQL query succesfully. However the the tablenames (and hence column names) are not shown when defining the datasource (only the ALL TABLES checked box is displayed). It is therefore not possible to create a query using the Query Designer. On OOo1.1Beta2 when defining the same datasource and clicking on the "TABLES" tab an error message is returned as follows: No Connection could be established Error: Illegal TYPE parameters SQL Status: OKDJT Unlike the behaviour at 1.0.3 it is not possible to enter a manual SQL query in 1.1Beta2 as an error occurs as follows: The data control could not be loaded Result Set type:{1} is not supported Some background (hope this helps) - the JDBC driver we are using, supplied by IBM for the UniVerse/U2 databases implements JDBC1.0 with some JDBC2.0 functionality. It uses the JDBC API DataBaseMetaData interface and supports getSchema, getTables,getColumns. The JDBC manual with this driver states that, depending on whether one is accessing a schema as opposed to an account, when calling DatabaseMetaData methods either the schema should be passed as a parameter (if a schema) or null (if an account) A UniVerse Schema implements SQL "fully" whereas an ACCOUNT is just a collection of uniVerse files in a directory that, although can be update/queried using SQL does not have a full SQL implementation. (Is the JDBC driver supposed to detect a schema vs Account via the DatabaseMetaData methods or is the application, ie OOo, expected to determine what parameter to pass i.e is it supposed to "know" that it is connecting to a schema vs account) Note that, apart from an aliasing issue (see issue 3860) both UniVerse schemas and accounts are accesible as ODBC datasources Some methods not supported by this JDBC driver include: Connection.getCatalog .setCatalog .getTypeMap .setTypeMap DatabaseMetaData.getProcedures(String catalog,String schemaPattern,String procedureNamePattern) .getProcedureColumns(String catalog,StringschemaPattern,String procedureNamePattern,String columnNamePattern) .getCatalogs() .getColumnPrivileges(String catalog, String schemaPattern, String tableNamePattern) .getTablePrivileges(String catalog,String schemaPattern,String tableNamePattern) .getBestRowIdentifier(String catalog, String schema, String table,int scopemboolean nullable) .getVersionColumns(string catalog,String schema,String table) .getImportedKeys(String catalog,String schema, String table) .getExportedKeys(String catalog, String schema, String table) .getCrossReference(String primaryCatalog,String primarySchema,String primaryTable,String foreignCatalog,String foreignschema,String foreignTable) .getIndexInfo(String catalog,String schema,String table,boolean unique booleam approximate) .getUDTs(String catalog,String schemaPattern,String typeNamePattern,int[] types) ResultSet interface methods not supported are: .getCharacterStream(int) .getCharacterStream(string) ResultSetMetadata.getColumnClass(int)
correcting sub component (http://www.openoffice.org/issues/describecomponents.cgi?component=database%20access) and default owner
With OOo1.1 can now no longer enter an SQL query manually (see previous comments) Eg SELECT * FROM CUSTOMER results in error "ResultSet Type {1,005} is not supported." SQL Status HYC00 Error Code 951012 The data source is accessible using MSACCESS, MSQuery, Cognos Impromptu, Crystal Reports via ODBC but not with OpenOffice.org using ODBC (see issue 3860)
I have also experienced this problem that OO 1.0.3 allows me to create a manual query, but OO 1.1.0 returns the same error as reported previously ie "ResultSet Type {1,005} is not supported." This message changed from Result Type 1 to 1,005 once I'd upgraded to a later JDBC driver. Even with the later driver OO 1.0.3 does not display tables in the query designer.
Here is some additional details on this problem. In OOo 1.0.2 and 1.0.3, even though the Query Designer does not display the tables available in the database, if a manual query is created and saved eg SELECT * FROM CM and used in the Data Pilot, all the column names are returned to the dialog control and a crosstab/pivot table can be created. However in OOo 1.1.0 the message "ResultSet Type {1,005} is not supported." is returned. The resolution of this problem is very important to us as it is the single remaining stumbling block preventing us from moving off the remaining 50 users of MicroSoft Office onto OpenOffice. We have successfully implemented OOo1.1 on a Linux server serving 40 users in a thin-client environment using the LTSP thin-client and the performance is very good.
I have installed SDBTestTool from Marc Neumann and tested against the JDBC driver with OpenOffice.org1.1.0. This is certainly a useful tool. Apart from some methods not supported by this JDBC driver I am able to connect to and retrieve metadata and data without a problem, whereas on connecting to the datasource using OpenOffice.org1.1.0 using Tools->Data Sources-> the message "illegal type" as reported before is returned when clicking on the "Tables" tab or when entering an SQL query. For example selecting Show Metadata in SDBTestTool results in the following: ## Single MetaData ## allProceduresAreCallable true allTablesAreSelectable true dataDefinitionCausesTransactionCommit false dataDefinitionIgnoredInTransactions false doesMaxRowSizeIncludeBlobs false getCatalogSeparator getCatalogTerm Catalog getDatabaseProductName Universe getDatabaseProductVersion 10.1.0 getDefaultTransactionIsolation 2 getDriverMajorVersion 1 getDriverMinorVersion 2 getDriverName IBM Universe/Unidata JDBC Driver getDriverVersion 1.2 getExtraNameCharacters getIdentifierQuoteString " getMaxBinaryLiteralLength 0 getMaxCatalogNameLength 0 getMaxCharLiteralLength 1 getMaxColumnNameLength 128 getMaxColumnsInGroupBy 9 getMaxColumnsInIndex 0 getMaxColumnsInOrderBy 21 getMaxColumnsInSelect 0 getMaxColumnsInTable 1000 getMaxConnections 0 getMaxCursorNameLength 0 getMaxIndexLength 0 getMaxProcedureNameLength 128 getMaxRowSize 0 getMaxSchemaNameLength 128 getMaxStatementLength 10000 getMaxStatements 10 getMaxTableNameLength 48 getMaxTablesInSelect 8 getMaxUserNameLength 0 getNumericFunctions ABS,ACOS,ASIN,ATAN,COS,INT,ISNULL,LN,MOD,NVL,POWER,REUSE,ROUND,SIGN,SIN,SQRT,TAN,TRUNC getProcedureTerm Cataloged-Basic-Program getSchemaTerm SCHEMA getSearchStringEscape getStringFunctions LENGTH,TO_NUMBER,ISNULL,NVL,|| getSystemFunctions getTimeDateFunctions ADD_MONTHS,LAST_DAY,NEXT_DAY,DATEADD,MONTH_BETWEEN,DATEDIFF,DATEPART,GETDATE getURL jdbc:ibm-u2://cflnxdta/SQLTEST getUserName isCatalogAtStart false isReadOnly false nullPlusNonNullIsNull true nullsAreSortedAtEnd false nullsAreSortedAtStart false nullsAreSortedHigh false nullsAreSortedLow true storesLowerCaseIdentifiers false storesLowerCaseQuotedIdentifiers false storesMixedCaseIdentifiers true storesMixedCaseQuotedIdentifiers true storesUpperCaseIdentifiers false storesUpperCaseQuotedIdentifiers false supportsAlterTableWithAddColumn true supportsAlterTableWithDropColumn true supportsANSI92EntryLevelSQL false supportsANSI92FullSQL false supportsANSI92IntermediateSQL true supportsCatalogsInDataManipulation false supportsCatalogsInIndexDefinitions false supportsCatalogsInPrivilegeDefinitions false supportsCatalogsInProcedureCalls false supportsCatalogsInTableDefinitions false supportsColumnAliasing true supportsConvert typ1,1 true supportsCoreSQLGrammar true supportsCorrelatedSubqueries true supportsDataDefinitionAndDataManipulationTransactions false supportsDataManipulationTransactionsOnly true supportsDifferentTableCorrelationNames false supportsExpressionsInOrderBy false supportsExtendedSQLGrammar false supportsFullOuterJoins false supportsGroupBy true supportsGroupByBeyondSelect false supportsGroupByUnrelated true supportsIntegrityEnhancementFacility false supportsLikeEscapeClause true supportsLimitedOuterJoins true supportsMinimumSQLGrammar true supportsMixedCaseIdentifiers false supportsMixedCaseQuotedIdentifiers true supportsMultipleResultSets false supportsMultipleTransactions true supportsNonNullableColumns true supportsOpenCursorsAcrossCommit false supportsOpenCursorsAcrossRollback false supportsOpenStatementsAcrossCommit false supportsOpenStatementsAcrossRollback false supportsOrderByUnrelated true supportsOuterJoins true supportsPositionedDelete false supportsPositionedUpdate false supportsSchemasInDataManipulation true supportsSchemasInIndexDefinitions false supportsSchemasInPrivilegeDefinitions false supportsSchemasInProcedureCalls false supportsSchemasInTableDefinitions false supportsSelectForUpdate false supportsStoredProcedures true supportsSubqueriesInComparisons true supportsSubqueriesInExists true supportsSubqueriesInIns true supportsSubqueriesInQuantifieds true supportsTableCorrelationNames true supportsTransactionIsolationLevel false supportsTransactionIsolationLevel false supportsTransactionIsolationLevel false supportsTransactionIsolationLevel false supportsTransactions true supportsUnion true supportsUnionAll true usesLocalFilePerTable false usesLocalFiles false
change subcomponent to 'none'
add keyword needmoreinfo. See mailing from Frank Schönheit in users@dba.openoffice.org
change owner
clu->ianst: does this problem still occure in a current version?
In response to last comment "does this problem still occure in a current version?" Yes, this occurs on both Linux and Windows version of OOo 1.1.1. When attempting to access the "Tables" tab when defining a data source, a message box saying " No Connection could be established for the URL jdbc:ibm-u2//remhosta/ar Error Illegal TYPE parameter. Then, if one creates an SQL query against a table in the data source, the user name and password is prompted for and the quey designer is activated. Once the query is entered and submitted this message is reported: The data content could not be loaded ResultSet type 1,005:is not supported. This works with 1.0.1, 1.0.2 and 1.0.3 of OOo. Many thanks for persisting with this issue.
CLU->MSC: like we have spoken - you want to handle it
I introduced additional settings, which force several parts of OOo to respect the perculiarities of the JDBC driver for IBM's Universe. The good news is that with these settings properly set, I see tables and views, and can even do queries, as expected. The bad news is that there is no UI for those settings, and there won't be in 2.0 at all, since the UI deadline for 2.0 already passed. I am going to attach a macro which does those settings, for a given database document. This macro has to be run once for every database document (aka: .odb file) working on an Universe database via JDBC.
Created attachment 22423 [details] Basic macro to run for a database document, to enable the settings needed for U2/JDBC connections
hi, verified in cws dba24 Bye Marc
Hi, fixed in current developer build -> close. The current developer build can be found at http://download.openoffice.org/680/index.html Feel free to reopen if this issue is not fixed in the developer build. Bye Marc
Hi, not sure if this is the right place, but many thanks for persisting with this issue to date. Have installed OpenOffice.org.1.9.100 and modified Universe settings.bas to point to my data source document. When running the macro the following message is displayed: Basic runtime error Property or method not found The line of code "highlighted" in the macro is oDB.Store() Tks Ian Stuart
sorry, we slightly changed the API since I created the Basic macro :) Please repleace oDB.store() with oDB.DatabaseDocument.store()
Thank you, thank you... This works great. I can now query the UniVerse schemas, SQL catalog, databases, do joins, create tables etc. The anomolies now occuring are UniVerse specific eg tablenames in UniVerse may contain "." - this "confuses" OOo when generating SQL but is not an OOo problem and quite easily fixed manually or by creating synonymns for the tables and columns. Although I can create tables on the UniVerse server I can only get read-only access to the tables even though I set ALL PRIVILEGES for PUBLIC and log in as the SQL administrator. Any suggestions or should I raise a new issue ? Again thanks to Frank and everyone who have worked on providing this solution. Ian Stuart
great to hear that it works for you :) > Although I can create tables on the UniVerse server I can only get read-only > access to the tables even though I set ALL PRIVILEGES for PUBLIC and log in as > the SQL administrator. Any suggestions or should I raise a new issue ? Definately a new issue, please (http://qa.openoffice.org/issue_handling/basic_rules.html#one_per_issue). However, IIRC, the JDBC driver only supports the READONLY concurrency (as opposed to UPDATABLE, see http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html#CONCUR_READ_ONLY). OOo previously always tried UPDATABLE. Other drivers downgraded this automatically when necessary, however IBM's Universe driver just complained that it only supports READONLY. This is what the second setting in the macro cares for: it tells OOo to always respect what the driver supports, instead of trying the maximum. Thus, when the result set is not updatable with this setting being <TRUE/>, it means that the driver claims to not support updatable result sets. So: feel free to submit an issue, so we can track it, but I strongly assume that it's a driver problem on IBM's side.
I close this issue now, because the reporter verified this issue as fixed.