Hi Pascal,

Have you tried this with the latest RC (RC8) of OOo as well to see whether the bug is specific to LibO ?


Alex

(In reply to comment #1)
> Hi Pascal,
> 
> Have you tried this with the latest RC (RC8) of OOo as well to see whether the
> bug is specific to LibO ?
> 
> 
> Alex

Hi Alex,

I've done the test with a new installation of OO (RC8) and the result is still the same (connect to the database is possible but reading data in a table is not working).
Re-installing a 3.2 version of OO solve this problem.

Pascal

@Pascal : you need to report this to the OOo bug program too, otherwise the developers at Oracle won't know about it.

Have you tried again with LibO 3.3 final or OOo 3.3 final to see if the bug was fixed in the meantime ?


Alex

I have tried establishing an ODBC connection with both OOo 3.3.0 final and LibO 3.3.0 final.

Driver : MyODBC 5.01.0008 (32 bit)
Server : localhost mysql 5.1
OS : Mac OSX 10.6.6

I can connect to the db, but none of the tables are displayed. Additionally, none of the tables are accessible via the query, form or report modules. The bug is present in both OOo and LibO. I might add that I don't get any error message, so this may be the same underlying problem, or then again, it may be a platform / driver specific problem.


Alex

changing version to final

changing platform to all

Resembles bug 34432

(In reply to comment #7)
> Resembles bug 34432

Hi Sasha.
Just for information, I've installed the latest LibO (3.3.0)on a Windows 7 o.s (32bits).
I get the same problem than before.
I succeed to connect to my database using an odbc driver ( and a jdbc.odbc connector too).
I can see tables but I can't read data.

This problem is exactly the same by connecting to an existing database or to an new one.

Regards.

Pascal

Setting CONFIRMED status in Whiteboard

Just to give an update, is the pb still there on 3.4.4 (current version) or master (future 3.5) ?

*** Bug 43274 has been marked as a duplicate of this bug. ***

*** Bug 34650 has been marked as a duplicate of this bug. ***

(In reply to comment #10)
> Just to give an update, is the pb still there on 3.4.4 (current version) or
> master (future 3.5) ?

Hi Julien

I'm running with the 3.4.4 version (LibreOffice 3.4.4 OOO340m1 (Build:402)) and keeping a previous version of OO (3.2.1)to use my databases.

Regards.

Pascal

Hi all,

The errors here are reminiscent of cursor scroll concurrency/position problems, so perhaps the handling of a failure within the code is incorrect, or perhaps there should be some override where the ODBC driver returns that it can only manage one type of scrolling concurrency ?

Since the error occurs after opening a connection to the db, at a wild guess, possibly somewhere in this code of ODatabaseMetaDataResult.cxx, lines 545 and onward :


sal_Bool SAL_CALL ODatabaseMetaDataResultSet::first(  ) throw(SQLException, RuntimeException)
{

    checkDisposed(ODatabaseMetaDataResultSet_BASE::rBHelper.bDisposed);
    ::osl::MutexGuard aGuard( m_aMutex );


    m_nCurrentFetchState = N3SQLFetchScroll(m_aStatementHandle,SQL_FETCH_FIRST,0);
    OTools::ThrowException(m_pConnection,m_nCurrentFetchState,m_aStatementHandle,SQL_HANDLE_STMT,*this);
    sal_Bool bRet = ( m_nCurrentFetchState == SQL_SUCCESS || m_nCurrentFetchState == SQL_SUCCESS_WITH_INFO );
    if( bRet )
        m_nRowPos = 1;
    return bRet;
}
// -------------------------------------------------------------------------

sal_Bool SAL_CALL ODatabaseMetaDataResultSet::last(  ) throw(SQLException, RuntimeException)
{
    checkDisposed(ODatabaseMetaDataResultSet_BASE::rBHelper.bDisposed );
    ::osl::MutexGuard aGuard( m_aMutex );


    m_nCurrentFetchState = N3SQLFetchScroll(m_aStatementHandle,SQL_FETCH_LAST,0);
    OTools::ThrowException(m_pConnection,m_nCurrentFetchState,m_aStatementHandle,SQL_HANDLE_STMT,*this);
    // here I know definitely that I stand on the last record
    return (m_nCurrentFetchState == SQL_SUCCESS || m_nCurrentFetchState == SQL_SUCCESS_WITH_INFO);
}
// -------------------------------------------------------------------------
sal_Bool SAL_CALL ODatabaseMetaDataResultSet::absolute( sal_Int32 row ) throw(SQLException, RuntimeException)
{

    checkDisposed(ODatabaseMetaDataResultSet_BASE::rBHelper.bDisposed);
    ::osl::MutexGuard aGuard( m_aMutex );


    m_nCurrentFetchState = N3SQLFetchScroll(m_aStatementHandle,SQL_FETCH_ABSOLUTE,row);
    OTools::ThrowException(m_pConnection,m_nCurrentFetchState,m_aStatementHandle,SQL_HANDLE_STMT,*this);
    sal_Bool bRet = m_nCurrentFetchState == SQL_SUCCESS || m_nCurrentFetchState == SQL_SUCCESS_WITH_INFO;
    if(bRet)
        m_nRowPos = row;
    return bRet;
}
// -------------------------------------------------------------------------
sal_Bool SAL_CALL ODatabaseMetaDataResultSet::relative( sal_Int32 row ) throw(SQLException, RuntimeException)
{

    checkDisposed(ODatabaseMetaDataResultSet_BASE::rBHelper.bDisposed);
    ::osl::MutexGuard aGuard( m_aMutex );


    m_nCurrentFetchState = N3SQLFetchScroll(m_aStatementHandle,SQL_FETCH_RELATIVE,row);
    OTools::ThrowException(m_pConnection,m_nCurrentFetchState,m_aStatementHandle,SQL_HANDLE_STMT,*this);
    sal_Bool bRet = m_nCurrentFetchState == SQL_SUCCESS || m_nCurrentFetchState == SQL_SUCCESS_WITH_INFO;
    if(bRet)
        m_nRowPos += row;
    return bRet;
}
// -------------------------------------------------------------------------
sal_Bool SAL_CALL ODatabaseMetaDataResultSet::previous(  ) throw(SQLException, RuntimeException)
{

    checkDisposed(ODatabaseMetaDataResultSet_BASE::rBHelper.bDisposed);
    ::osl::MutexGuard aGuard( m_aMutex );


    m_nCurrentFetchState = N3SQLFetchScroll(m_aStatementHandle,SQL_FETCH_PRIOR,0);
    OTools::ThrowException(m_pConnection,m_nCurrentFetchState,m_aStatementHandle,SQL_HANDLE_STMT,*this);
    sal_Bool bRet = m_nCurrentFetchState == SQL_SUCCESS || m_nCurrentFetchState == SQL_SUCCESS_WITH_INFO;
    if(bRet)
        --m_nRowPos;
    return bRet;
}



Alex

It might also be worth looking at OFunctions.cxx and oservices.cxx to see if anything has changed there from previous (i.e. older than 3.3.x release) OOo code.


Alex

Hmmm,

perhaps this :

return (m_nCurrentFetchState == SQL_SUCCESS


which is used in determining the end position of the resultset should be like the construct used for the other scroll position tests :


sal_Bool bRet = ( m_nCurrentFetchState == SQL_SUCCESS


but if that is the case, then it is missing this bit :

if( bRet )

        m_nRowPos = "method used to determine the number of row sets, returned as BOOL, whatever that might be";

    return bRet;



So I guess that is why it fails, because it is impossible to determine the end point of the resultset ??


Alex

In OResultSet.cxx, the test used for the endpoint is :

m_bEOF = sal_True

could that not be used as the test ?


Alex

Hi Lionel,

Do you think you could take a look ?

Thnx,

Alex

Cannot reproduce on Debian GNU/Linux with MyODBC nor with FreeTDS connecting to MS SQL Server, not with 3.4.3 and not with master (3.5).

Cannot reproduce with MyODBC 5.01.06 on Windows 7 with LO 3.4.4, but I get a different problem: The tables are not shown (not listed), but queries and reports still work. So access to data works, it is just that the list of tables.

Cannot reproduce with SQL Server ODBC driver from Microsoft: there all works well.

(In reply to comment #19)

> Cannot reproduce with MyODBC 5.01.06 on Windows 7 with LO 3.4.4, but I get a
> different problem: The tables are not shown (not listed), but queries and
> reports still work. So access to data works, it is just that the list of
> tables.

The same with LibO-dev master daily build.

MySQL ODBC driver has/had quite some bugs around listing tables (including latest version 5.1.9), so it is very well possible the problem *I* get is a driver bug. See e.g.
http://bugs.mysql.com/bug.php?id=39957
http://bugs.mysql.com/bug.php?id=39561

As to the Sage driver, this is almost certainly another problem. If only we knew *which* feature it is the driver does not support and that we have to avoid...

At least in fdo#43274 we know.

All people having trouble with error messages like "not supported" with ODBC: Please try going to menu
Edit / Databae / Advanced Settings
Then, in tab "Special Settings", check (activate) "Respect the result set type from the database driver".
Test again. If it still does not work, please save the database document, close it, and open it again. Check that the setting is still active, and try again. Report your results here.

For some reason I don't fathom for the moment, this setting defaults to "false", which seems rather... suspicious.

In particular, this request applies to:

 PéPé (Pascal Peronnet)
 rogjames@btinternet.com
 mervyn

(In reply to comment #16)

> perhaps this :

> return (m_nCurrentFetchState == SQL_SUCCESS

> should be like the construct used for the other scroll position tests :

> sal_Bool bRet = ( m_nCurrentFetchState == SQL_SUCCESS

> but if that is the case, then it is missing this bit :

> if( bRet )
> 
>         m_nRowPos = "method used to determine the number of row sets, returned
> as BOOL, whatever that might be";
> 
>     return bRet;

Taking example from OResultSet.cxx, m_nRowPos is not updated on ::last() (the value just becomes stale and unreliable...). That in itself is probably a bug :(

Anyway, I synchronised the behaviour of OResultSet.cxx and ODatabaseMetaDataResultSet.cxx on this, which makes the following bug disappear. This macro

Sub Main
	Dim IntrctHndl as Object
	Dim conn as Object
	Dim stmt as Object

	IntrctHndl = createUnoService("com.sun.star.task.InteractionHandler")
	conn = ThisDatabaseDocument.DataSource.connectWithCompletion(IntrctHndl)
	stmt = conn.createStatement()

	Dim md as Object
	md = conn.getMetaData()

	Dim rs as Object
	rs = md.getCatalogs()

	MsgBox rs.isLast()
	rs.last()
	MsgBox rs.isLast()
		
End Sub

should say "false" then "true", but says "false" then "false", and similar bugs next() instead of last(). Testing needs an ODBC driver that actually supports last() on a DatabaseMetaData ResultSet. I managed with the SQLite3 ODBC driver.

That's fixed with commit fcf4f2c9c01cbcfc24a2a49b1461670026ba2bfa in master, which means the fix will be part of 3.5.0.beta1 (which we might rename to alpha1 in the meantime).

> So I guess that is why it fails, because it is impossible to determine the end
> point of the resultset ??

That's not impossible, but there is no hint showing it is not yet another problem that gives this bug (bug 32171: cannot read from ODBC database). My money is more on the "Respect the result set type from the database driver" thing.  <shrug>

(In reply to comment #22)
> All people having trouble with error messages like "not supported" with ODBC:
> Please try going to menu
> Edit / Databae / Advanced Settings
> Then, in tab "Special Settings", check (activate) "Respect the result set type
> from the database driver".
Hello Lionel, I saw this option on my LO compiled from master (so 3.5) but not on 3.4.4. (Debian x86-32 packages). Have I miss a Debian LO package to install ?

(In reply to comment #24)
> (In reply to comment #22)
>> All people having trouble with error messages like "not supported" with ODBC:
>> Please try going to menu
>> Edit / Databae / Advanced Settings
>> Then, in tab "Special Settings", check (activate) "Respect the result set type
>> from the database driver".

> Hello Lionel, I saw this option on my LO compiled from master (so 3.5) but not
> on 3.4.4. (Debian x86-32 packages). Have I miss a Debian LO package to install?

Ah, you are right. As far as I can see, the setting exists in 3.4, but there is no UI for it. Execute the following Basic macro to enable that setting:

Sub Foo
  Dim settings as Object
  settings = ThisDatabaseDocument.DataSource.Settings
  settings.RespectDriverResultSetType = TRUE
  ThisDatabaseDocument.store
End Sub

If that macro does not work, see https://issues.apache.org/ooo/show_bug.cgi?id=75442#c12 .

(In reply to comment #22)
> Edit / Databae / Advanced Settings
> Then, in tab "Special Settings", check (activate) "Respect the result set type
> from the database driver".

> For some reason I don't fathom for the moment, this setting defaults to
> "false", which seems rather... suspicious.

Ah, I found http://permalink.gmane.org/gmane.comp.openoffice.dba.user/7815 that says: 

 However, also note that there are known drivers where this setting
 doesn't work, simply because the driver itself lies about its supported
 types.

Rah! So as far as the default value goes, one has to choose between one set of bugs or another set of bugs :-( At first thought, I'd say that drivers that lie about their supported types are buggy, so I'd prefer to be "incompatible" with them rather than incompatible with perfectly fine (but maybe overly strict) drivers.

Since all new unconfirmed bugs start in state UNCONFIRMED now and old unconfirmed bugs were moved to NEEDINFO with a explanatory comment, all bugs promoted above those bug states to NEW and later are automatically confirmed making the CONFIRMED whiteboard status redundant. Thus it will be removed.

No news from reporter. Assuming it was fixed by

 commit fca4891942b319dfe59d1a225d0ab01601a96205
 Author: Ocke.Janssen <Ocke.Janssen@oracle.com>
 Date:   Wed Jan 26 12:26:48 2011 +0100

    dba34d: #i108967# introduce new settings into UI and driver config: RespectDriverResultSetType

which makes  RespectDriverResultSetType=TRUE the default for ODBC, JDBC and ADO.

Any person reproducing this bug:

 - check that Database Advanced Setting "Respect Driver ResultSet Type" is enabled
 - if it is not, then enable and restart LibreOffice.
 - it it is and it still does not work, reopen this bug (or file a new one if not about Sage ODBC driver)