Connect to a postgreSQL-database through ODBC. Try to create a view in the table-pane. Try Insert > View Design - will be greyed out. Try Insert > View (Simple) - also greyed out. Now create a view in the database through Tools > SQL: CREATE VIEW "public"."tview" AS SELECT * FROM "public"."table1" The view will be created. You could see this after setting View > Refresh Tables. Close the *.odb-file after saving it. Reopen the *.odb-file. Now the possibility appears to create a view through the GUI of LO. You will see in the table-pane "Create View" and all the other possibilities are available.
I imagine that this is linked to the problem identified in: https://bugs.documentfoundation.org/show_bug.cgi?id=124531#c7
On pc Debian x86-64 with master sources updated today, I could reproduce this.
Indeed, we enter the "if" block if ( aReturn.bEnabled ) { Reference<XViewsSupplier> xViewsSup( getConnection(), UNO_QUERY ); aReturn.bEnabled = xViewsSup.is(); } but xViewsSup.is() returns false. Whereas we use the same odbc implementation in LO, I don't reproduce this with an ODBC connection to a Firebird database. Perhaps not related to LO but to ODBC postgresql driver?
Dear Robert Großkopf, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Created attachment 178250 [details] View execution image
Sorry image added to wrong bug. S/B tdf#124531 will post there
Bug is still the same in LO 7.3.0.3 und OpenSUSE 15.3 64bit rpm Linux. You couldn't create a view in PostgreSQL through ODBC (and direct connection) in GUI. You have to create a vie in Tools → SQL before. JDBC-connector shows the possibility to create a view directly.
It's not specific to Postgresql via ODBC, I could reproduce this too with MariaDB. So I suppose it's rather specific to ODBC.
After some debugging, here's a code pointer which explains why the creating a view option only shows when a view exists: 306 // check if we support types 307 if ( xMeta.is() ) 308 { 309 Reference<XResultSet> xRes = xMeta->getTableTypes(); 310 if(xRes.is()) 311 { 312 Reference<XRow> xRow(xRes,UNO_QUERY); 313 while(xRes->next()) 314 { 315 OUString sValue = xRow->getString(1); 316 if( !xRow->wasNull() && sValue == "VIEW") 317 { 318 m_bSupportsViews = true; 319 break; 320 } 321 } 322 } 323 // some dbs don't support this type so we should ask if a XViewsSupplier is supported 324 if(!m_bSupportsViews) 325 { 326 Reference< XViewsSupplier > xMaster(getMasterTables(),UNO_QUERY); 327 328 if (xMaster.is() && xMaster->getViews().is()) 329 m_bSupportsViews = true; 330 } 331 if(m_bSupportsViews) 332 { 333 m_pViews.reset( new OViewContainer(*this, m_aMutex, this, bCase, this, m_nInAppend) ); 334 m_pViews->addContainerListener(m_pTables.get()); 335 m_pTables->addContainerListener(m_pViews.get()); 336 } see https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/dataaccess/connection.cxx?r=bc3a0205#307 getTableTypes is implemented for each database connector so also for ODBC. Reference< XResultSet > SAL_CALL ODatabaseMetaData::getTableTypes( ) { Reference< XResultSet > xRef; try { rtl::Reference<ODatabaseMetaDataResultSet> pResult = new ODatabaseMetaDataResultSet(m_pConnection); xRef = pResult; pResult->openTablesTypes(); } catch(SQLException&) { xRef = new ::connectivity::ODatabaseMetaDataResultSet(::connectivity::ODatabaseMetaDataResultSet::eTableTypes); } return xRef; } See https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/odbc/ODatabaseMetaData.cxx?r=dffe9495#715 It uses "openTablesTypes" void ODatabaseMetaDataResultSet::openTablesTypes( ) { SQLRETURN nRetcode = N3SQLTables(m_aStatementHandle, nullptr,0, nullptr,0, nullptr,0, reinterpret_cast<SDB_ODBC_CHAR *>(const_cast<char *>(SQL_ALL_TABLE_TYPES)),SQL_NTS); OTools::ThrowException(m_pConnection.get(),nRetcode,m_aStatementHandle,SQL_HANDLE_STMT,*this); m_aColMapping.clear(); m_aColMapping.push_back(-1); m_aColMapping.push_back(4); m_xMetaData = new OResultSetMetaData(m_pConnection.get(),m_aStatementHandle,std::vector(m_aColMapping)); checkColumnCount(); } See https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/odbc/ODatabaseMetaDataResultSet.cxx?r=7c3990c3#872 The pb is the ODBC function used (https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqltables-function?view=sql-server-ver15) retrieves types from existing elements not types from every elements possible in a database like original "getTableTypes" from JDBC (see https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTableTypes()). So if there's no existing VIEW in the database (Mysql, Postgresql, whatever), the first method never goes in line 318 ( m_bSupportsViews = true;) So LO tries another way by calling getMasterTables() and when digging a bit, it tries to call getDataDefinitionByURLAndConnection() (See https://opengrok.libreoffice.org/xref/core/connectivity/source/commontools/dbtools2.cxx?r=d8538d7f#660) 660 Reference< XTablesSupplier> getDataDefinitionByURLAndConnection( 661 const OUString& _rsUrl, 662 const Reference< XConnection>& _xConnection, 663 const Reference< XComponentContext >& _rxContext) 664 { 665 Reference< XTablesSupplier> xTablesSup; 666 try 667 { 668 Reference< XDriverManager2 > xManager = DriverManager::create( _rxContext ); 669 Reference< XDataDefinitionSupplier > xSupp( xManager->getDriverByURL( _rsUrl ), UNO_QUERY ); 670 671 if ( xSupp.is() ) 672 { 673 xTablesSup = xSupp->getDataDefinitionByConnection( _xConnection ); 674 OSL_ENSURE(xTablesSup.is(),"No table supplier!"); 675 } 676 } 677 catch( const Exception& ) 678 { 679 DBG_UNHANDLED_EXCEPTION("connectivity.commontools"); 680 } 681 return xTablesSup; 682 } and finally since ODBC connector in LO doesn't implement XDataDefinitionSupplier, it doesn't go into the "if" and returns empty ref "xTablesSup". So now, what to do? Taking a look how to implement "XDataDefinitionSupplier", it seems we need catalog part, which needs tables part and perhaps views part. I tried this with Mysql direct connector, it takes some time and perhaps there are regressions in what I did. Moreover, calling ODBC functions is less easy than looking into INFORMATION_SCHEMA. A radical (and I suppose wrong) workaround would be in ODatabaseMetaData::getTableTypes from odbc part to just return "VIEW" (in a Reference< XResultSet >) since getTableTypes is only used to know if "VIEW" concept is known. It would mean we'd consider every database which can be accessed from ODBC driver would support VIEWS (!!). Lionel: if you've got some idea here, don't hesitate! :-)
Call SQLGetInfo(handle, SQL_CREATE_VIEW, &result, ...) Assuming a "good" ODBC driver, the database supports views if and only if (result & SQL_CV_CREATE_VIEW) to test whether the database supports creations of views. Documentation from https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetinfo-function An SQLUINTEGER bitmask enumerating the clauses in the CREATE VIEW statement, as defined in SQL-92, supported by the data source. The following bitmasks are used to determine which clauses are supported: SQL_CV_CREATE_VIEW SQL_CV_CHECK_OPTION SQL_CV_CASCADED SQL_CV_LOCAL A return value of "0" means that the CREATE VIEW statement is not supported. An SQL-92 Entry level-conformant driver will always return the SQL_CV_CREATE_VIEW and SQL_CV_CHECK_OPTION options as supported. An SQL-92 Full level-conformant driver will always return all of these options as supported.
(In reply to Lionel Elie Mamane from comment #10) > Call > SQLGetInfo(handle, SQL_CREATE_VIEW, &result, ...) > Assuming a "good" ODBC driver, the database supports views if and only if > (result & SQL_CV_CREATE_VIEW) > ... Thank you Lionel for your feedback! I began to implement this in https://gerrit.libreoffice.org/c/core/+/130052 but don't understand at all ODatabaseMetaDataResultSet, especially m_aColMapping mechanism. It would be so easier getTableTypes() just returns an std::vector with known types for a database instead of all these complicated (at least I hope useful) mechanisms.
Bug is still the same in LO 24.2.0.3 on OpenSUSE 15.4 64bit rpm Linux.