Bug 118094 - Firebird: Support more XResultSet functions
Summary: Firebird: Support more XResultSet functions
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.beta1+
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-06-10 10:44 UTC by Gerhard Schaber
Modified: 2018-12-11 23:45 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
example firebird odb w/rowset object (38.56 KB, application/vnd.oasis.opendocument.database)
2018-06-10 18:34 UTC, Drew Jensen
Details
Failing example (4.07 KB, application/vnd.sun.xml.base)
2018-06-10 18:49 UTC, Gerhard Schaber
Details
family tree - XResultSet Interface Reference (6.49 KB, image/png)
2018-06-10 19:20 UTC, Drew Jensen
Details
added a line in macro for each xResultSet function and some properties (4.25 KB, application/vnd.oasis.opendocument.database)
2018-12-11 23:45 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerhard Schaber 2018-06-10 10:44:19 UTC
Please support Basic functions such as isLast and isFirst for the XResultSet with Firebird.
Comment 1 Gerhard Schaber 2018-06-10 10:45:26 UTC
Currently one gets error messages like 'isLast is not supported by firebird'. Tested with 6.0.5.1 and 6.1.0 beta 1.
Comment 2 Drew Jensen 2018-06-10 18:34:43 UTC
Created attachment 142637 [details]
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
Comment 3 Drew Jensen 2018-06-10 18:37:00 UTC
sorry for the extra post;

Change the second message box in that macro to

	msgbox "isLast= " + oRS.islast

and that works as expected also.
Comment 4 Gerhard Schaber 2018-06-10 18:49:39 UTC
Created attachment 142639 [details]
Failing example
Comment 5 Gerhard Schaber 2018-06-10 18:51:15 UTC
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.
Comment 6 Gerhard Schaber 2018-06-10 19:01:06 UTC
32bit LO 6.0.5.1 and 6.1.0 beta1, Java 8 Update 171, Windows 7.
Comment 7 Drew Jensen 2018-06-10 19:20:26 UTC
Created attachment 142640 [details]
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.
Comment 8 Drew Jensen 2018-06-10 19:24:11 UTC
(In reply to Gerhard Schaber from comment #5)
> 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?
Comment 9 Drew Jensen 2018-06-10 20:21:49 UTC
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.
Comment 10 Gerhard Schaber 2018-06-10 20:27:39 UTC
Yes, I also consider that an enhancement request.
Comment 11 Drew Jensen 2018-06-10 21:27:42 UTC
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.
Comment 12 Tamas Bunth 2018-12-10 16:28:09 UTC
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.
Comment 13 Lionel Elie Mamane 2018-12-10 16:41:35 UTC
(In reply to Tamas Bunth from comment #12)
> The Firebird API supports moving the cursor only forward.

${EXPLETIVE}

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

> 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...

> 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.
Comment 14 Drew Jensen 2018-12-11 23:45:56 UTC
Created attachment 147452 [details]
added a line in macro for each xResultSet function and some properties

So, using LO 6.2Beta1 these functions and properties of XResultSet are working with firebird:
isBeforeFirst
isAfterLast
isFirst
first
getRow
relative( long > 0 ) error with negative numbers
FetchDirection
FetchSize
ResultSetConcurrency
ResultSetType

While these are not:
CursorName 
isLast 
beforeFirst
afterLast
last
absolute( long )
previous
refreshRow
rowUpdated
rowInserted
rowDeleted

Of those refreshRow, and these three, rowUpdate, rowInserted, and rowDeleted surprised me. 

I think, for the others, there are other open items which should get a higher priority. For now I think it is important to clearly document, for users, what is and isn't supported with regards to those functions not implemented because the fb engine doesn't implement them.