Please support Basic functions such as isLast and isFirst for the XResultSet with Firebird.
Currently one gets error messages like 'isLast is not supported by firebird'. Tested with 6.0.5.1 and 6.1.0 beta 1.
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
sorry for the extra post; Change the second message box in that macro to msgbox "isLast= " + oRS.islast and that works as expected also.
Created attachment 142639 [details] Failing example
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.
32bit LO 6.0.5.1 and 6.1.0 beta1, Java 8 Update 171, Windows 7.
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.
(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?
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.
Yes, I also consider that an enhancement request.
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.
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.
(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.
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.