Possible bug with MySQL and OOo ResultSet

[Sergio Corato]

I have this code:

oStmt = oConn.createStatement()
oStmt.setPropertyValue("ResultSetType",
com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE)
oStmt.setPropertyValue("ResultSetConcurrency",
com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY)
oRs = oStmt.executeQuery(sql_string)
oRs.next
c = oRs.getRow
print c '= 1
oRs.next
print oRs.getRow '= 2
oRs.absolute(c) 'give False with MySQL only
print oRs.getRow '= 2 ***error*** with MySQL only

If I connect to HSQL, it works giving 1 at the end, while if I connect
to MySQL, it return 2 at the end.
Do I make an error?

MySQL 5.1.40 - Connector 3.51.27 - OOo 3.1.1

p.s. If I connect with extension native driver for OOo, it works, so
I'll use it, but it would be however a bug. And in Ubuntu, wich use
MySQL 5.0.75, native connector is not available.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Sergio,

> oStmt = oConn.createStatement()
> oStmt.setPropertyValue("ResultSetType",
> com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE)
> oStmt.setPropertyValue("ResultSetConcurrency",
> com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY)
> oRs = oStmt.executeQuery(sql_string)
> oRs.next
> c = oRs.getRow
> print c '= 1
> oRs.next
> print oRs.getRow '= 2
> oRs.absolute(c) 'give False with MySQL only
> print oRs.getRow '= 2 ***error*** with MySQL only
>
> If I connect to HSQL, it works giving 1 at the end, while if I connect
> to MySQL, it return 2 at the end.
> Do I make an error?
>
> MySQL 5.1.40 - Connector 3.51.27 - OOo 3.1.1

In general, the result set type you specify (SCROLL_INSENSITIVE, in your
example) is a request which might not necessarily be fulfilled by the
database driver. That is, the driver is free to "downgrade" it, if it
doesn't support a particular type. Whether this downgrade happens
silently, or with a warning (or not at all, but execution would fail),
is at the discretion of the driver.

That said, it might be (do not know this out of my head) that the MySQL
Connector decided to downgrade your result set type to FORWARD_ONLY. In
this case, "absolute" is not expected to work, only "next" can be used then.

Provided that the driver provides proper information, you can check this
with
  Print oRs.ResultSetType ' should be 1004 [1]
, or, if you need the information before actually executing the statement:
  Print oConn.MetaData.supportsResultSetConcurrency( _
    com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE, _
    com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY )
(However, the latter method is known to be improperly implemented by
some drivers, though I think the MySQL Connector is not amongst those.)


If you find that indeed the SCROLL_INSENSITIVE type is not supported by
the driver, then you might consider upgrading the Connector/ODBC -
finally, the 3.51 series is *really* old meanwhile.

Ciao
Frank

[1]http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/ResultSetType.html

>
> p.s. If I connect with extension native driver for OOo, it works, so
> I'll use it, but it would be however a bug. And in Ubuntu, wich use
> MySQL 5.0.75, native connector is not available.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@...
> For additional commands, e-mail: dev-help@...
>


--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Alex Thurgood]

Hi Frank, Sergio,

It would appear that the 3.51 ODBC driver only supports dynamic cursors in FORWARD_ONLY and STATIC modes if this page is correct :


http://www.verbose.fr/mysql_5.0/mysql-connectors.html

25.1.14.2. Est-ce que MyODBC accepte les curseurs dynamiques?

Oui. MyODBC 3.51 supporte les curseurs dynamiques avec les modes Forward-only et static.

A cause des problèmes de performances, le pilote ne supporte pas cette fonctionnalité par défaut. Vous pouvez l'activer en spécifiant l'option de connexion OPTION=32 ou en cliquant dans l'option Enable Dynamic Cursor dans le panneau de configuration DSN.

For those who don't speak French :
Does MyODBC accept dynamic cursors?
Yes. MyODBC 3.51 supports dynamic cursors in Forward-Only and Static modes
For performance reasons, the driver does not enable this functionality by default. It can be activated by specifying the connection option OPTION=32 or by clicking on the Enable Dynamic Cursor tickbox in the DSN configuration panel.


Alex



----- "Frank Schoenheit, Sun Microsystems Germany" <Frank.Schoenheit@...> a écrit :

> Hi Sergio,
>
> > oStmt = oConn.createStatement()
> > oStmt.setPropertyValue("ResultSetType",
> > com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE)
> > oStmt.setPropertyValue("ResultSetConcurrency",
> > com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY)
> > oRs = oStmt.executeQuery(sql_string)
> > oRs.next
> > c = oRs.getRow
> > print c '= 1
> > oRs.next
> > print oRs.getRow '= 2
> > oRs.absolute(c) 'give False with MySQL only
> > print oRs.getRow '= 2 ***error*** with MySQL only
> >
> > If I connect to HSQL, it works giving 1 at the end, while if I
> connect
> > to MySQL, it return 2 at the end.
> > Do I make an error?
> >
> > MySQL 5.1.40 - Connector 3.51.27 - OOo 3.1.1
>
> In general, the result set type you specify (SCROLL_INSENSITIVE, in
> your
> example) is a request which might not necessarily be fulfilled by the
> database driver. That is, the driver is free to "downgrade" it, if it
> doesn't support a particular type. Whether this downgrade happens
> silently, or with a warning (or not at all, but execution would
> fail),
> is at the discretion of the driver.
>
> That said, it might be (do not know this out of my head) that the
> MySQL
> Connector decided to downgrade your result set type to FORWARD_ONLY.
> In
> this case, "absolute" is not expected to work, only "next" can be used
> then.
>
> Provided that the driver provides proper information, you can check
> this
> with
>   Print oRs.ResultSetType ' should be 1004 [1]
> , or, if you need the information before actually executing the
> statement:
>   Print oConn.MetaData.supportsResultSetConcurrency( _
>     com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE, _
>     com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY )
> (However, the latter method is known to be improperly implemented by
> some drivers, though I think the MySQL Connector is not amongst
> those.)
>
>
> If you find that indeed the SCROLL_INSENSITIVE type is not supported
> by
> the driver, then you might consider upgrading the Connector/ODBC -
> finally, the 3.51 series is *really* old meanwhile.
>
> Ciao
> Frank
>
> [1]http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/ResultSetType.html
>
> >
> > p.s. If I connect with extension native driver for OOo, it works, so
>
> > I'll use it, but it would be however a bug. And in Ubuntu, wich use
>
> > MySQL 5.0.75, native connector is not available.
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: dev-unsubscribe@...
> > For additional commands, e-mail: dev-help@...
> >
>
>
> --
> - Frank Schönheit, Software Engineer         frank.schoenheit@...
> -
> - Sun Microsystems                      http://www.sun.com/staroffice
> -
> - OpenOffice.org Base                       http://dba.openoffice.org
> -
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> -
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@...
> For additional commands, e-mail: dev-help@...

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Sergio Corato]

Alex Thurgood ha scritto:

> Hi Frank, Sergio,
>
> It would appear that the 3.51 ODBC driver only supports dynamic cursors in FORWARD_ONLY and STATIC modes if this page is correct :
>
>
> http://www.verbose.fr/mysql_5.0/mysql-connectors.html
>
> 25.1.14.2. Est-ce que MyODBC accepte les curseurs dynamiques?
>
> Oui. MyODBC 3.51 supporte les curseurs dynamiques avec les modes Forward-only et static.
>
> A cause des problèmes de performances, le pilote ne supporte pas cette fonctionnalité par défaut. Vous pouvez l'activer en spécifiant l'option de connexion OPTION=32 ou en cliquant dans l'option Enable Dynamic Cursor dans le panneau de configuration DSN.
>
> For those who don't speak French :
> Does MyODBC accept dynamic cursors?
> Yes. MyODBC 3.51 supports dynamic cursors in Forward-Only and Static modes
> For performance reasons, the driver does not enable this functionality by default. It can be activated by specifying the connection option OPTION=32 or by clicking on the Enable Dynamic Cursor tickbox in the DSN configuration panel.
>
>
> Alex
>
>
>  

I tried it, but probably don't work with MySQL 5.1.
It can be useful for Ubuntu, so I will try with it.

> ----- "Frank Schoenheit, Sun Microsystems Germany" <Frank.Schoenheit@...> a écrit :
>
>  
>> Hi Sergio,
>>
>>    
>>> oStmt = oConn.createStatement()
>>> oStmt.setPropertyValue("ResultSetType",
>>> com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE)
>>> oStmt.setPropertyValue("ResultSetConcurrency",
>>> com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY)
>>> oRs = oStmt.executeQuery(sql_string)
>>> oRs.next
>>> c = oRs.getRow
>>> print c '= 1
>>> oRs.next
>>> print oRs.getRow '= 2
>>> oRs.absolute(c) 'give False with MySQL only
>>> print oRs.getRow '= 2 ***error*** with MySQL only
>>>
>>> If I connect to HSQL, it works giving 1 at the end, while if I
>>>      
>> connect
>>    
>>> to MySQL, it return 2 at the end.
>>> Do I make an error?
>>>
>>> MySQL 5.1.40 - Connector 3.51.27 - OOo 3.1.1
>>>      
>> In general, the result set type you specify (SCROLL_INSENSITIVE, in
>> your
>> example) is a request which might not necessarily be fulfilled by the
>> database driver. That is, the driver is free to "downgrade" it, if it
>> doesn't support a particular type. Whether this downgrade happens
>> silently, or with a warning (or not at all, but execution would
>> fail),
>> is at the discretion of the driver.
>>
>> That said, it might be (do not know this out of my head) that the
>> MySQL
>> Connector decided to downgrade your result set type to FORWARD_ONLY.
>> In
>> this case, "absolute" is not expected to work, only "next" can be used
>> then.
>>
>> Provided that the driver provides proper information, you can check
>> this
>> with
>>   Print oRs.ResultSetType ' should be 1004 [1]
>> , or, if you need the information before actually executing the
>> statement:
>>   Print oConn.MetaData.supportsResultSetConcurrency( _
>>     com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE, _
>>     com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY )
>> (However, the latter method is known to be improperly implemented by
>> some drivers, though I think the MySQL Connector is not amongst
>> those.)
>>
>>
>> If you find that indeed the SCROLL_INSENSITIVE type is not supported
>> by
>> the driver, then you might consider upgrading the Connector/ODBC -
>> finally, the 3.51 series is *really* old meanwhile.
>>
>> Ciao
>> Frank
>>
>> [1]http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/ResultSetType.html
>>    

I will update to 5.1 ASAP.
Ciao
Sergio

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...