[Libreoffice-bugs] [Bug 118094] New: Firebird: Support more XResultSet functions

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

Bug ID: 118094
Summary: Firebird: Support more XResultSet functions
Product: LibreOffice
Version: 6.1.0.0.beta1+
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Base
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ***@gmx.at

Please support Basic functions such as isLast and isFirst for the XResultSet
with Firebird.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #1 from Gerhard Schaber <***@gmx.at> ---
Currently one gets error messages like 'isLast is not supported by firebird'.
Tested with 6.0.5.1 and 6.1.0 beta 1.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

Drew Jensen <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #2 from Drew Jensen <***@gmail.com> ---
Created attachment 142637
--> https://bugs.documentfoundation.org/attachment.cgi?id=142637&action=edit
example firebird odb w/rowset object

This works for me with 6.1Beta1 (didn't try with 6.0.4)

1 set your options to allow macro execution

2 download the attached ODB w/firebird and open it

3 if asked enable macro execution

4 run the macro Main in the Standard library attached to the file

Sub Main

oRS = createUnoService("com.sun.star.sdb.RowSet")
oRS.DataSourceName = ThisComponent.Location
oRS.CommandType = com.sun.star.sdb.CommandType.TABLE
oRS.Command = "Business Contacts"
oRS.Execute
oRS.First
msgbox "isFirst= " + oRS.isFirst

oRS.Last

msgbox "isFirst= " + oRS.isFirst

oRS.Dispose()

End Sub

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #3 from Drew Jensen <***@gmail.com> ---
sorry for the extra post;

Change the second message box in that macro to

msgbox "isLast= " + oRS.islast

and that works as expected also.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #4 from Gerhard Schaber <***@gmx.at> ---
Created attachment 142639
--> https://bugs.documentfoundation.org/attachment.cgi?id=142639&action=edit
Failing example

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #5 from Gerhard Schaber <***@gmx.at> ---
Yes, that works. The one I attached does not (reports an exception). MAybe I am
doing something wrong. It used to work with HSQLDB.

I made the example from scratch.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #6 from Gerhard Schaber <***@gmx.at> ---
32bit LO 6.0.5.1 and 6.1.0 beta1, Java 8 Update 171, Windows 7.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #7 from Drew Jensen <***@gmail.com> ---
Created attachment 142640
--> https://bugs.documentfoundation.org/attachment.cgi?id=142640&action=edit
family tree - XResultSet Interface Reference

Ok - so, looking at this page in the SDK IDL docs
sdk/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdbc_1_1XResultSet.html

attached the image if you don't have the SDK installed.

I would think both basic sub procedures should work.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #8 from Drew Jensen <***@gmail.com> ---
(In reply to Gerhard Schaber from comment #5)

Post by b***@bugs.documentfoundation.org
Yes, that works. The one I attached does not (reports an exception). MAybe I
am doing something wrong. It used to work with HSQLDB.
I made the example from scratch.

and yes your right, it gives the same error here. So it is an issue, I don't
know for sure actually what was coded here and what wasn't, but since the
rowset does work and I take it the rowset should of been exposed by the call to
islast on the resultSet (which has a rowSet component)

so, I think the right thing is to change the summary to "The resultSet does not
properly pass the call to the rowSet object". What do you think?

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #9 from Drew Jensen <***@gmail.com> ---
further reading in the SDK docs, about rowsets and how the relate to resultsets
I came across this line:
"On the other hand, a row set can be used to implement capabilities for a
result set, which are not supported by a driver result set, like caching
strategies or update capabilities."

Which I suppose means that this may be within that description and the fact
that the firebird resultset does not automatically create a rowset, for
instance when the isFirst or isLast is invoked, would not be a bug but a design
decision.

That would make this an enhancement request.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #10 from Gerhard Schaber <***@gmx.at> ---
Yes, I also consider that an enhancement request.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

Drew Jensen <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Severity|normal |enhancement

--- Comment #11 from Drew Jensen <***@gmail.com> ---
Great. Changed the setting on the issue.

Allowing xRowSet function calls against Statement (XResultSet) would be an
example, yes.

One thing that crosses my mind, just as an observer of late.

As I do some comparison between Base files with identical structures (hsql vs
firebird) is how much more responsive the forms (using queries) are not only in
the firebird files but against the large hsql files. Some of the other
components in Base do not seem to have changed (still dog slow in the HSQL
file) but in forms displaying relatively short result sets (with multiple
joins) HSQL is actually just a tad faster IT FEELS LIKE then the firebird
engine. (maybe me smile and laugh a bit) [then there is startup and shutdown of
the engine and that's where the HSQL file is a major problem and firebird is
plenty snappy]

Which is all to say that if having to use one more line of basic, to explicitly
get a xrowset from a result, so as to use some of the positioning functions of
a rowset vs resultset is the price of making the form/sub_form functioning
faster, that would be a worthwhile trade off in my mind.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

Drew Jensen <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Ever confirmed|0 |1
Status|UNCONFIRMED |NEW

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

Roman Kuznetsov <***@mail.ru> changed:

What |Removed |Added
----------------------------------------------------------------------------
Blocks| |116968


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=116968
[Bug 116968] [META] Migrating existing embedded HSQLDB databases to Firebird

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #12 from Tamas Bunth <***@gmail.com> ---
The Firebird API supports moving the cursor only forward. That's why things
like "previous" is not supported, and "relative" and "absolute" are only
partially supported (you cannot move backwards).

They could be implemented though by reopening the result set each time we'd go
backwards, and seek out the requested row. It would be much slower though (user
should be aware of the implementation details).

"isFirst" is already implemented afais.

To implement "isLast", we need to know somehow the total number of rows. After
looking around in the documentation of the C API it seems to me that it cannot
be done, unless we calculate it with a "Select COUNT(...) from ..." kind of
query. That could be expensive though.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118094

--- Comment #13 from Lionel Elie Mamane <***@mamane.lu> ---
(In reply to Tamas Bunth from comment #12)

Post by b***@bugs.documentfoundation.org
The Firebird API supports moving the cursor only forward.

${EXPLETIVE}

Now I feel guilty for selecting Firebird as embedded database for LibreOffice.

Post by b***@bugs.documentfoundation.org
They could be implemented though by reopening the result set each time we'd
go backwards, and seek out the requested row.

I usually consider that is beyond the role of the SDBC driver. An alternative
implementation idea would be to cache the whole resultset...

Post by b***@bugs.documentfoundation.org
After looking around in the documentation of the C API it seems to me that
it cannot be done, unless we calculate it with a "Select COUNT(...) from
..." kind of query. That could be expensive though.

Again, that kind of thing is beyond the role of the SDBC driver.

For the specific thing of isLast, we can fetch one additional row (and cache
it), this would allow to know if the current row is the last...


I looked at the JDBC driver, it seems they cache (at least in some situations)
the resultset in order to be able to implement going backward.