Bug 32171 - Cannot read data from a database with an Odbc driver
Summary: Cannot read data from a database with an Odbc driver
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: medium major
Assignee: Lionel Elie Mamane
URL:
Whiteboard:
Keywords: regression
: 34650 43274 (view as bug list)
Depends on:
Blocks:
 
Reported: 2010-12-07 02:08 UTC by PéPé
Modified: 2012-09-14 10:02 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
screen capture of database connection (88.88 KB, application/vnd.oasis.opendocument.text)
2010-12-07 02:08 UTC, PéPé
Details

Note You need to log in before you can comment on or make changes to this bug.
Description PéPé 2010-12-07 02:08:51 UTC
Created attachment 40861 [details]
screen capture of database connection

I was using databases created with a previous OO version (3.2.1)
The driver is coming from Sage (software fort rading and accounting).
Dsn has been set up, working on Windows XP  2002 (SP3).
Once I installed LibO 3.3.0, I can't read data from my previous database.
My problem is the same after creating a new databse using the odbc driver. I can see tables, fields but I can't read data in it.
When I look on properties of my databse, I can successfully connect on it, but using or creating a request I always get the following message (in French) :

Impossible de charger le contenu des données
[Microsoft][Gestionnaire de pilote OBDC] le pilote ne prend pas cette fonction en charge.
Statut SQL: IM001

Both products (OO and LibO) are working on the same environment (Operating system, drivers, Dsn).

Regards.

Pascal
Comment 1 Alex Thurgood 2010-12-20 00:43:37 UTC
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
Comment 2 PéPé 2010-12-20 08:12:35 UTC
(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
Comment 3 Alex Thurgood 2011-02-03 06:25:56 UTC
@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
Comment 4 Alex Thurgood 2011-02-04 05:59:28 UTC
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
Comment 5 Alex Thurgood 2011-02-04 06:00:17 UTC
changing version to final
Comment 6 Alex Thurgood 2011-02-04 06:01:12 UTC
changing platform to all
Comment 7 sasha.libreoffice 2011-02-18 03:13:25 UTC
Resembles bug 34432
Comment 8 PéPé 2011-02-22 03:06:23 UTC
(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
Comment 9 Alex Thurgood 2011-05-24 02:57:27 UTC
Setting CONFIRMED status in Whiteboard
Comment 10 Julien Nabet 2011-11-26 05:00:33 UTC
Just to give an update, is the pb still there on 3.4.4 (current version) or master (future 3.5) ?
Comment 11 Alex Thurgood 2011-11-27 12:01:04 UTC
*** Bug 43274 has been marked as a duplicate of this bug. ***
Comment 12 Alex Thurgood 2011-11-27 12:03:04 UTC
*** Bug 34650 has been marked as a duplicate of this bug. ***
Comment 13 PéPé 2011-11-28 01:07:06 UTC
(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
Comment 14 Alex Thurgood 2011-11-28 01:33:34 UTC
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
Comment 15 Alex Thurgood 2011-11-28 01:39:17 UTC
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
Comment 16 Alex Thurgood 2011-11-28 02:03:10 UTC
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
Comment 17 Alex Thurgood 2011-11-28 02:12:04 UTC
In OResultSet.cxx, the test used for the endpoint is :

m_bEOF = sal_True

could that not be used as the test ?


Alex
Comment 18 Alex Thurgood 2011-11-28 02:16:19 UTC
Hi Lionel,

Do you think you could take a look ?

Thnx,

Alex
Comment 19 Lionel Elie Mamane 2011-12-01 06:09:55 UTC
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.
Comment 20 Lionel Elie Mamane 2011-12-01 06:24:20 UTC
(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.
Comment 21 Lionel Elie Mamane 2011-12-01 06:57:24 UTC
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.
Comment 22 Lionel Elie Mamane 2011-12-04 09:53:59 UTC
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
Comment 23 Lionel Elie Mamane 2011-12-04 11:11:14 UTC
(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>
Comment 24 Julien Nabet 2011-12-04 12:40:55 UTC
(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 ?
Comment 25 Lionel Elie Mamane 2011-12-04 12:58:46 UTC
(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 .
Comment 26 Lionel Elie Mamane 2011-12-04 13:02:03 UTC
(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.
Comment 27 Björn Michaelsen 2011-12-23 13:22:52 UTC
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.
Comment 28 Lionel Elie Mamane 2012-09-14 10:02:46 UTC
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)