Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
28.04.2022 14:31, Mark Rotteveel wrote: I notice that if I get the INF_RECORD_COUNT with op_info_cursor before the first fetch, I will get the record count, but the subsequent fetch will fail with a Dynamic SQL Error; SQLDA error; Data type unknown; at SQLVAR index 0 [SQLState:07002, ISC error code:335544583]. If I get the record count after the first fetch, everything is OK. This seems to indicate that retrieving the record count somehow sets an empty SQLDA or something like that, and ignores the one from the first fetch. Is this a bug? Should retrieving cursor info before the first fetch be disallowed by the server? Or maybe something else? This is likely to be a bug, I will take a look. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 22-04-2022 17:51, Dmitry Yemanov wrote: 22.04.2022 14:49, Mark Rotteveel wrote: I was wondering if this - fetching rowcount of a scrollable cursor - was already implemented. If so, how do I get it? And if not, when can I expect it to be implemented? PR #7083, was left for review but not merged yet. I see it has conflicts now, I will address them tomorrow and then merge the PR. I notice that if I get the INF_RECORD_COUNT with op_info_cursor before the first fetch, I will get the record count, but the subsequent fetch will fail with a Dynamic SQL Error; SQLDA error; Data type unknown; at SQLVAR index 0 [SQLState:07002, ISC error code:335544583]. If I get the record count after the first fetch, everything is OK. This seems to indicate that retrieving the record count somehow sets an empty SQLDA or something like that, and ignores the one from the first fetch. Is this a bug? Should retrieving cursor info before the first fetch be disallowed by the server? Or maybe something else? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
22.04.2022 14:49, Mark Rotteveel wrote: 28.11.2021 14:45, Mark Rotteveel wrote: 3) "row count" makes it possible to know the position after fetchLast() and everything else could be calculated locally by the client library, thus making the server-supported "current position" totally unnecessary. Do I miss anything? Could we agree on having only "row count" returned via op_info_cursor and leaving "cursor position" (getRow() in Java API) up the connectivity library developers? I was wondering if this - fetching rowcount of a scrollable cursor - was already implemented. If so, how do I get it? And if not, when can I expect it to be implemented? PR #7083, was left for review but not merged yet. I see it has conflicts now, I will address them tomorrow and then merge the PR. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 08-12-2021 10:13, Dmitry Yemanov wrote: 28.11.2021 14:45, Mark Rotteveel wrote: 3) "row count" makes it possible to know the position after fetchLast() and everything else could be calculated locally by the client library, thus making the server-supported "current position" totally unnecessary. Do I miss anything? Could we agree on having only "row count" returned via op_info_cursor and leaving "cursor position" (getRow() in Java API) up the connectivity library developers? I was wondering if this - fetching rowcount of a scrollable cursor - was already implemented. If so, how do I get it? And if not, when can I expect it to be implemented? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 2021-12-20 11:51, Dmitry Yemanov wrote: Mark et al, Looked at it again, and being able to get the total row count will work for me. Is this information already available, or does this still need to be implemented? What would you expect from the "row count" requested for a non-scrollable cursor? It cannot return the true count, as already explained here. Should it return some magic number (0? 1? -1?) or would error be appropriate? If the latter, should it be isc_infinap (information type inappropriate for object specified) or isc_infona (no information of this type available for object specified)? I'd say -1 as a magic value to signal "unknown". Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
20.12.2021 13:58, Dimitry Sibiryakov wrote: Even non-scrollable cursors can know total number of records if plan SORT is used Sort may be hidden inside other execution nodes, so it's not always as easy to know. I'd rather avoid returning (or not) info depending on the query plan. or they are fetched to the end This is surely possible, but is it really needed? The cursor is likely to be closed soon, who would need a row count at this point? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 20.12.2021 11:51: What would you expect from the "row count" requested for a non-scrollable cursor? Even non-scrollable cursors can know total number of records if plan SORT is used or they are fetched to the end but I think that isc_infona is the right answer in all other cases. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Mark et al, > Looked at it again, and being able to get the total row count will work > for me. Is this information already available, or does this still need > to be implemented? What would you expect from the "row count" requested for a non-scrollable cursor? It cannot return the true count, as already explained here. Should it return some magic number (0? 1? -1?) or would error be appropriate? If the latter, should it be isc_infinap (information type inappropriate for object specified) or isc_infona (no information of this type available for object specified)? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
19.12.2021 16:04, Mark Rotteveel wrote: Looked at it again, and being able to get the total row count will work for me. Is this information already available, or does this still need to be implemented? Implemented but not yet committed. I will post a pull request tomorrow. One last thing I was wondering about (but haven't had time to verify): what happens with positional updates or deletes when scrolling? Specifically: - After an update: does the cursor have the original row content or the updated content? Original. - After a delete: does the row disappear from the cursor, does the cursor retain the original content of the row, or does an 'empty' (e.g. all NULL) row exist in the cursor? The original content is retained. Basically, it should work the same way as for SELECT ... ORDER BY non-indexed-field (where records are cached inside the sort). Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 08-12-2021 10:13, Dmitry Yemanov wrote: 28.11.2021 14:45, Mark Rotteveel wrote: We don't have anything like this. Theoretically, we could extend IRecordSet with something similar (although it would also require a protocol change), but the question is whether it's really needed. Personally, I don't see it useful per se. If users want to know a number of rows, then perhaps an explicit getRowCount() method would be more useful (*). But AFAIU the Java API does not mention it. (*) not applicable to uni-directional cursors? The intent of the method is to report the position of the current row. The example it is abused by some to get the total size was just an illustration of why people would expect a value after requesting the last row. But the same would be a problem if people went to last, scrolled around and then want to know the current row position. I was thinking about providing both "current position" and "row count" information values for a cursor. Both are doable, however the former has some issues: [..] 3) "row count" makes it possible to know the position after fetchLast() and everything else could be calculated locally by the client library, thus making the server-supported "current position" totally unnecessary. Do I miss anything? Could we agree on having only "row count" returned via op_info_cursor and leaving "cursor position" (getRow() in Java API) up the connectivity library developers? Looked at it again, and being able to get the total row count will work for me. Is this information already available, or does this still need to be implemented? One last thing I was wondering about (but haven't had time to verify): what happens with positional updates or deletes when scrolling? Specifically: - After an update: does the cursor have the original row content or the updated content? - After a delete: does the row disappear from the cursor, does the cursor retain the original content of the row, or does an 'empty' (e.g. all NULL) row exist in the cursor? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 2021-12-08 10:13, Dmitry Yemanov wrote: 28.11.2021 14:45, Mark Rotteveel wrote: We don't have anything like this. Theoretically, we could extend IRecordSet with something similar (although it would also require a protocol change), but the question is whether it's really needed. Personally, I don't see it useful per se. If users want to know a number of rows, then perhaps an explicit getRowCount() method would be more useful (*). But AFAIU the Java API does not mention it. (*) not applicable to uni-directional cursors? The intent of the method is to report the position of the current row. The example it is abused by some to get the total size was just an illustration of why people would expect a value after requesting the last row. But the same would be a problem if people went to last, scrolled around and then want to know the current row position. I was thinking about providing both "current position" and "row count" information values for a cursor. Both are doable, however the former has some issues: 1) Adjustments due to prefetch should be taken into account at both client and server sides, thus complicating the code (information response buffer must be parsed, value received from the engine/server must be replaced with the adjusted one). 2) If used "with purpose", it may be requested by the client after every fetch, thus multiplying the round-trips and killing the prefetch benefits. So it could make sense to request the position (send op_info_cursor) internally after every op_fetch_scroll packet. As every fetch request may return multiple rows, sending many positions is unnecessary and only position of the first row could be returned. This puts additional requirements to the client-side protocol implementations. 3) "row count" makes it possible to know the position after fetchLast() and everything else could be calculated locally by the client library, thus making the server-supported "current position" totally unnecessary. Do I miss anything? Could we agree on having only "row count" returned via op_info_cursor and leaving "cursor position" (getRow() in Java API) up the connectivity library developers? That sounds doable, but I won't have time to really look and think about this until the weekend. I'll reply then if I have more concerns. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
08.12.2021 13:49, Dimitry Sibiryakov wrote: Storing of fetched rows is unavoidable indeed but prefetch?.. Is it done in background or before returning of the first row to client even in embedded mode? The latter. But prefetch is done in small chunks, usually it does not hurt. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 08.12.2021 10:54: For scrollable cursors, rows are prefetched and cached by the engine and thus the count can be easily returned. It has its cost, but generally it's unavoidable anyway for scrollable cursors. Storing of fetched rows is unavoidable indeed but prefetch?.. Is it done in background or before returning of the first row to client even in embedded mode? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
08.12.2021 12:37, Tony Whyman wrote: It would also be very useful to get rowcount returned for unidirectional cursors if that was readily possible. At present, it is only possible to get an accurate count of the number of rows in a cursor after you have fetched all of them. It cannot be calculated without fetching all rows, even inside the engine. For scrollable cursors, rows are prefetched and cached by the engine and thus the count can be easily returned. It has its cost, but generally it's unavoidable anyway for scrollable cursors. However, prefetching uni-directional cursors in advance would be a huge overkill which nobody usually needs. So "row count" is likely to return zero or error for uni-directional cursors. However, if you desperately needs "row count" and ready to pay the price, then just open the cursor as scrollable and fetch only forward. The "row count" will be available automagically. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 08/12/2021 09:13, Dmitry Yemanov wrote: 3) "row count" makes it possible to know the position after fetchLast() and everything else could be calculated locally by the client library, thus making the server-supported "current position" totally unnecessary. Do I miss anything? Could we agree on having only "row count" returned via op_info_cursor and leaving "cursor position" (getRow() in Java API) up the connectivity library developers? As long as row count can be returned this would work for IBX. In all other cases, it looks like it should be possible to compute the current row number client side. It would also be very useful to get rowcount returned for unidirectional cursors if that was readily possible. At present, it is only possible to get an accurate count of the number of rows in a cursor after you have fetched all of them. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
28.11.2021 14:45, Mark Rotteveel wrote: We don't have anything like this. Theoretically, we could extend IRecordSet with something similar (although it would also require a protocol change), but the question is whether it's really needed. Personally, I don't see it useful per se. If users want to know a number of rows, then perhaps an explicit getRowCount() method would be more useful (*). But AFAIU the Java API does not mention it. (*) not applicable to uni-directional cursors? The intent of the method is to report the position of the current row. The example it is abused by some to get the total size was just an illustration of why people would expect a value after requesting the last row. But the same would be a problem if people went to last, scrolled around and then want to know the current row position. I was thinking about providing both "current position" and "row count" information values for a cursor. Both are doable, however the former has some issues: 1) Adjustments due to prefetch should be taken into account at both client and server sides, thus complicating the code (information response buffer must be parsed, value received from the engine/server must be replaced with the adjusted one). 2) If used "with purpose", it may be requested by the client after every fetch, thus multiplying the round-trips and killing the prefetch benefits. So it could make sense to request the position (send op_info_cursor) internally after every op_fetch_scroll packet. As every fetch request may return multiple rows, sending many positions is unnecessary and only position of the first row could be returned. This puts additional requirements to the client-side protocol implementations. 3) "row count" makes it possible to know the position after fetchLast() and everything else could be calculated locally by the client library, thus making the server-supported "current position" totally unnecessary. Do I miss anything? Could we agree on having only "row count" returned via op_info_cursor and leaving "cursor position" (getRow() in Java API) up the connectivity library developers? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Mark Rotteveel wrote 01.12.2021 18:02: You need to set the cursor name before you open it, IIRC. Actually it doesn't matter. It is enough to set cursor name any time before using of positional DML AFAIU. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 01-12-2021 16:06, Dmitry Yemanov wrote: 01.12.2021 16:07, Dimitry Sibiryakov wrote: Also if IResultSet is returned from IAttachment::openCursor() there is no (visible) IStatement at all. And I see the same problem for setCursorName() which is available only through IStatement. An oversight? You need to set the cursor name before you open it, IIRC. So - assuming I'm not mistaken in this - adding it to IResultSet doesn't make sense, as that will only return once the cursor has been opened. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 01.12.2021 16:06: And I see the same problem for setCursorName() which is available only through IStatement. An oversight? IAttachment::openCursor() has a parameter for cursor name. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
01.12.2021 16:07, Dimitry Sibiryakov wrote: Also if IResultSet is returned from IAttachment::openCursor() there is no (visible) IStatement at all. And I see the same problem for setCursorName() which is available only through IStatement. An oversight? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 01.12.2021 9:10: Given that any DSQL statement cannot have multiple result sets, I doubt IResultSet::getInfo() is really required, IStatement::getInfo() could be used instead. Or if we go for consistency, IResultSet::getInfo() could be added, but internally it calls IStatement::getInfo() (and they share the same subset of info codes). Opinions? Because these interfaces are separated a function foo(IResultSet*) cannot access IStatement::getInfo() so IResultSet::getInfo() is required. Also if IResultSet is returned from IAttachment::openCursor() there is no (visible) IStatement at all. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
28.11.2021 14:37, Dimitry Sibiryakov wrote: And it doesn't need to be a dedicated method of IResultSet. Something generic and easily extendable like IResultSet::getInfo() would be enough. Given that any DSQL statement cannot have multiple result sets, I doubt IResultSet::getInfo() is really required, IStatement::getInfo() could be used instead. Or if we go for consistency, IResultSet::getInfo() could be added, but internally it calls IStatement::getInfo() (and they share the same subset of info codes). Opinions? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 28-11-2021 12:55, Dimitry Sibiryakov wrote: Dmitry Yemanov wrote 28.11.2021 12:51: 28.11.2021 14:47, Dimitry Sibiryakov wrote: RDB$DB_KEY for the current record as well. It's not a property of the cursor. Consider joins, unions, procedures, views, etc. It is a property of a current record the same as position. It is ok to return wide concatenated DB_KEY (up to 255 bytes due limitation of info buffer format). The statement info uses 16-bit size integers, so generally it would 65535, but AFAIK, it's contextually sized (e.g. isc_info_sql_select and isc_info_sql_bind don't have their own size). Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 28.11.2021 12:51: 28.11.2021 14:47, Dimitry Sibiryakov wrote: RDB$DB_KEY for the current record as well. It's not a property of the cursor. Consider joins, unions, procedures, views, etc. It is a property of a current record the same as position. It is ok to return wide concatenated DB_KEY (up to 255 bytes due limitation of info buffer format). -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 28.11.2021 12:45: That would be useful in some cases. And if statements with "WHERE CURRENT OF" condition also were somehow marked client libraries could force cursor position synchronization before executing them. I'd expect FOR UPDATE to still disable batching even without that NO_BATCH flag (as now), just because I can hardly see the need to prefetch cursor (and later reposition) if a positioned UPDATE/DELETE is expected. Statements with explicit FOR UPDATE is not a problem. IIRC using of positional UPDATE/DELETE is allowed even without them (resulting in a mess). Knowing that a (unknown user-provided) statement does use positional DML (without complete parsing) could allow drivers to workaround that mess without performance penalty of NO_BATH option. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
28.11.2021 14:47, Dimitry Sibiryakov wrote: RDB$DB_KEY for the current record as well. It's not a property of the cursor. Consider joins, unions, procedures, views, etc. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 28.11.2021 12:42: Then we may support both "current position" and "total row count" in getInfo(), RDB$DB_KEY for the current record as well. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 28-11-2021 12:22, Dmitry Yemanov wrote: We don't have anything like this. Theoretically, we could extend IRecordSet with something similar (although it would also require a protocol change), but the question is whether it's really needed. Personally, I don't see it useful per se. If users want to know a number of rows, then perhaps an explicit getRowCount() method would be more useful (*). But AFAIU the Java API does not mention it. (*) not applicable to uni-directional cursors? The intent of the method is to report the position of the current row. The example it is abused by some to get the total size was just an illustration of why people would expect a value after requesting the last row. But the same would be a problem if people went to last, scrolled around and then want to know the current row position. I don't think a solution would necessitate a protocol change. For example, a statement info request item that reports the current row position on the server could be an option as well for me. Then I can just request it if and when I do need to know the row position if I'm in an indeterminate row position. That said, if this isn't an option, I can simply make it throw an exception, return -1, or something similar, and accept the violation of the JDBC spec. If people then really need it, I could offer them a fallback option to use the existing 'cache result set entirely in memory' form of scrollable result sets (e.g. through a connection property). Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
27.11.2021 15:04, Dimitry Sibiryakov wrote: I would consider adding a NO_BATCH flag (which is currently triggered using FOR UPDATE syntax) to cursorFlags. That would be useful in some cases. And if statements with "WHERE CURRENT OF" condition also were somehow marked client libraries could force cursor position synchronization before executing them. I'd expect FOR UPDATE to still disable batching even without that NO_BATCH flag (as now), just because I can hardly see the need to prefetch cursor (and later reposition) if a positioned UPDATE/DELETE is expected. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
28.11.2021 14:37, Dimitry Sibiryakov wrote: And it doesn't need to be a dedicated method of IResultSet. Something generic and easily extendable like IResultSet::getInfo() would be enough. Then we may support both "current position" and "total row count" in getInfo(), letting the client wrapper to use whatever it needs. But please let's cover this by a separate tracker ticket. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 28.11.2021 12:22: Theoretically, we could extend IRecordSet with something similar (although it would also require a protocol change), but the question is whether it's really needed. Personally, I don't see it useful per se. It could be useful for client-side caching dataset implementation. Imagine that you fetched first 10 records and cached them, then user has requested "last" seek and you fetch 10 records backward to show them in grid. Here is a good idea to identify every record somehow to prevent fetching/caching a record twice if result set is less than 20 records. And it doesn't need to be a dedicated method of IResultSet. Something generic and easily extendable like IResultSet::getInfo() would be enough. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
28.11.2021 14:02, Mark Rotteveel wrote: Is there a way to determine at which row the cursor is currently positioned? JDBC has the ResultSet.getRow()[1] method which is documented as: "Retrieves the current row number. The first row is number 1, the second number 2, and so on." This is not problematic when using first, next, prior, relative or absolute, but once you do fetch_last, you don't know the row number until you do a first or absolute. The usefulness of this method is limited, but sometimes it is abused by people wanting to know how many rows there are in a result set (by requesting the last row), and if I can't fulfill this requirement, I need to at least document this. We don't have anything like this. Theoretically, we could extend IRecordSet with something similar (although it would also require a protocol change), but the question is whether it's really needed. Personally, I don't see it useful per se. If users want to know a number of rows, then perhaps an explicit getRowCount() method would be more useful (*). But AFAIU the Java API does not mention it. (*) not applicable to uni-directional cursors? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 26-11-2021 10:10, Dmitry Yemanov wrote: Mark et al, Yes, I think that is perfectly acceptable for an initial version. Scrollable cursors are a bit of an oddity anyway, but having scrollable cursors in embedded access, but not in remote access is IMHO less acceptable than bad performance. As long as the performance behaviour is clearly documented, people can make the decision if the bad performance is worth the utility of scrollable cursors for themselves. I've just committed network support for scrollable cursors to master. Prefetch logic is supported for NEXT/PRIOR navigations. Protocol changes are documented here: Is there a way to determine at which row the cursor is currently positioned? JDBC has the ResultSet.getRow()[1] method which is documented as: "Retrieves the current row number. The first row is number 1, the second number 2, and so on." This is not problematic when using first, next, prior, relative or absolute, but once you do fetch_last, you don't know the row number until you do a first or absolute. The usefulness of this method is limited, but sometimes it is abused by people wanting to know how many rows there are in a result set (by requesting the last row), and if I can't fulfill this requirement, I need to at least document this. [1]: https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/ResultSet.html#getRow() -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
28.11.2021 13:11, Mark Rotteveel wrote: In other words, it looks as if Firebird when asked for 4 rows, will return 4 rows and *also* buffer 4 more *on the server*, and return those unconditionally on the next fetch. Looks right, although weird I didn't notice that during the testing. To be fixed. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 28-11-2021 08:22, Dmitry Yemanov wrote: 27.11.2021 17:28, Mark Rotteveel wrote: Correction: it behaves as fetch_next (or fetch_prior) for the amount of rows that was fetched in the last fetch (or at least some number of rows buffered on the server). I can understand needing to take into account the client-side buffer, but I don't think that I should take into account any rows that the server has buffered. If you requested N rows from the server but now switching to the different fetch operation before getting all those rows, I expect the client to receive (and discard) the remaining part of the requested rows before sending the new opcode. This is what fbclient does currently. I agree that the client should take into account the rows it received (and still has buffered), and the rows it asynchronously requested, but not yet received (or processed), but that isn't the case here. I requested 4 rows, and already received those 4 rows (request/response-style), but the subsequent fetch(es) with a different opcode return rows as if I used the initial (prior/next) opcode for the next four rows. In other words, it looks as if Firebird when asked for 4 rows, will return 4 rows and *also* buffer 4 more *on the server*, and return those unconditionally on the next fetch. For example, if I have a table with a lot of rows with a value from 1 .. N, ABSOLUTE, position 10, returns [10] NEXT, 5 rows, returns [11, 12, 13, 14, 15] PRIOR 10 rows, returns [16, 17, 18, 19, 20, 19, 18, 17, 16, 15] (expected: [14, 13, 12, 11, 10, 9, 8, 7, 6, 5]) or FIRST, returns [1] NEXT, 4 rows returns [2, 3, 4, 5] ABSOLUTE, position 20, returns [6] (expected: [20]) ABSOLUTE, position 20, returns [7] (expected: [20]) ABSOLUTE, position 20, returns [8] (expected: [20]) ABSOLUTE, position 20, returns [9] (expected: [20]) ABSOLUTE, position 20, returns [20] or ABSOLUTE, position 10, returns [10] NEXT, 4 rows, returns [11, 12, 13, 14] RELATIVE, position -2, returns [15] (expected: [12]) RELATIVE, position -1, returns [16] (expected: [11]) RELATIVE, position -1, returns [17] (expected: [10]) RELATIVE, position -1, returns [18] (expected: [9]) RELATIVE, position -1, returns [17] (expected: [8]) I think the client cannot be expected to also take into account some server buffer as well, especially because its size is unknowable. Even if it would normally be equal to the size of previous fetch, that is no guarantee: what if the previous fetch retrieved all but the last row? It would also make the server-side buffering behaviour part of the server's public API, which I think is undesirable. What if in some cases it doesn't make sense to buffer, or what if it makes sense to buffer more rows, etc. You could no longer do that because the client has certain expectations. BTW: As far as I can tell, the current code in interface.cpp doesn't work either: it swallows local buffered rows and the batches it requested, but not yet processed, then it tries to reposition the cursor using RELATIVE for that number of rows. This will not work, because the server will instead return the first buffered row instead of repositioning. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
27.11.2021 17:28, Mark Rotteveel wrote: I'm running into some odd behaviour. As soon as I do a fetch_next or fetch_prior, any subsequent fetch ignores the fetch direction, and applies fetch_next (or fetch_prior). Correction: it behaves as fetch_next (or fetch_prior) for the amount of rows that was fetched in the last fetch (or at least some number of rows buffered on the server). I can understand needing to take into account the client-side buffer, but I don't think that I should take into account any rows that the server has buffered. If you requested N rows from the server but now switching to the different fetch operation before getting all those rows, I expect the client to receive (and discard) the remaining part of the requested rows before sending the new opcode. This is what fbclient does currently. However, I can see the point in letting the server to cleanup the queue instead of the client. I will give it a try. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 27-11-2021 14:10, Mark Rotteveel wrote: On 26-11-2021 10:10, Dmitry Yemanov wrote: I'd appreciate if Mark could test scrollability from the Jaybird side too (i.e. without fbclient involved), but the new protocol should be supported for that. I'm running into some odd behaviour. As soon as I do a fetch_next or fetch_prior, any subsequent fetch ignores the fetch direction, and applies fetch_next (or fetch_prior). Correction: it behaves as fetch_next (or fetch_prior) for the amount of rows that was fetched in the last fetch (or at least some number of rows buffered on the server). I can understand needing to take into account the client-side buffer, but I don't think that I should take into account any rows that the server has buffered. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 26-11-2021 10:10, Dmitry Yemanov wrote: I'd appreciate if Mark could test scrollability from the Jaybird side too (i.e. without fbclient involved), but the new protocol should be supported for that. I'm running into some odd behaviour. As soon as I do a fetch_next or fetch_prior, any subsequent fetch ignores the fetch direction, and applies fetch_next (or fetch_prior). Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dmitry Yemanov wrote 27.11.2021 12:23: I would consider adding a NO_BATCH flag (which is currently triggered using FOR UPDATE syntax) to cursorFlags. That would be useful in some cases. And if statements with "WHERE CURRENT OF" condition also were somehow marked client libraries could force cursor position synchronization before executing them. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
27.11.2021 12:42, Mark Rotteveel wrote: Yes, I think that is perfectly acceptable for an initial version. Scrollable cursors are a bit of an oddity anyway, but having scrollable cursors in embedded access, but not in remote access is IMHO less acceptable than bad performance. As long as the performance behaviour is clearly documented, people can make the decision if the bad performance is worth the utility of scrollable cursors for themselves. I've just committed network support for scrollable cursors to master. Prefetch logic is supported for NEXT/PRIOR navigations. Protocol changes are documented here: https://github.com/FirebirdSQL/firebird/issues/7051 Are other values than 0 or 1 (CURSOR_TYPE_SCROLLABLE) currently possible for cursorFlags (flags)? Nope (so far). Although remembering our old discussion re. whether scrollability should be represented via a syntax keyword or an API flag, I would consider adding a NO_BATCH flag (which is currently triggered using FOR UPDATE syntax) to cursorFlags. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 26-11-2021 10:10, Dmitry Yemanov wrote: Mark et al, Yes, I think that is perfectly acceptable for an initial version. Scrollable cursors are a bit of an oddity anyway, but having scrollable cursors in embedded access, but not in remote access is IMHO less acceptable than bad performance. As long as the performance behaviour is clearly documented, people can make the decision if the bad performance is worth the utility of scrollable cursors for themselves. I've just committed network support for scrollable cursors to master. Prefetch logic is supported for NEXT/PRIOR navigations. Protocol changes are documented here: https://github.com/FirebirdSQL/firebird/issues/7051 Are other values than 0 or 1 (CURSOR_TYPE_SCROLLABLE) currently possible for cursorFlags (flags)? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 26-11-2021 10:10, Dmitry Yemanov wrote: Mark et al, Yes, I think that is perfectly acceptable for an initial version. Scrollable cursors are a bit of an oddity anyway, but having scrollable cursors in embedded access, but not in remote access is IMHO less acceptable than bad performance. As long as the performance behaviour is clearly documented, people can make the decision if the bad performance is worth the utility of scrollable cursors for themselves. I've just committed network support for scrollable cursors to master. Prefetch logic is supported for NEXT/PRIOR navigations. Protocol changes are documented here: https://github.com/FirebirdSQL/firebird/issues/7051 The test set to cover different navigation scenarios is being created, once it's ready we'll validate the results between PSQL cursors, embedded access, network access w/prefetch and network access wo/prefetch (FOR UPDATE added to SELECTs). Obviously, we expect them to match ;-) I'd appreciate if Mark could test scrollability from the Jaybird side too (i.e. without fbclient involved), but the new protocol should be supported for that. I will have to implement scrollability (Jaybird currently emulates it by materializing the entire result set in memory) before I can test it. The committed version is linked to protocol 17 which was introduced for 4.0.1. Thus technically, this improvement can be backported and released together with v4.0.1. However, I'm not sure this risk is acceptable for a point release and I'm ready to introduce protocol 18 in master for v5. Opinions, please. Personally, I think this is better to surface in Firebird 5, than rush it into Firebird 4.0.1. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
26.11.2021 14:51, Tony Whyman wrote: Just compiled the updated master branch and tested with IBX. All looks good with the test suite returning the same set of results for bot remote and local databases. Many thanks for the good work. And my thanks to you for testing ;-) I've noticed that the ODS is now 13.1 compared with 13.0 for Firebird 4.0.0. Is this due to your patch or have there been other changes that have needed this? Other changes (some are still in progress). My patch does not affect the engine at all. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Dimtry, Just compiled the updated master branch and tested with IBX. All looks good with the test suite returning the same set of results for bot remote and local databases. Many thanks for the good work. I've noticed that the ODS is now 13.1 compared with 13.0 for Firebird 4.0.0. Is this due to your patch or have there been other changes that have needed this? Regards Tony Whyman On 26/11/2021 09:10, Dmitry Yemanov wrote: Mark et al, Yes, I think that is perfectly acceptable for an initial version. Scrollable cursors are a bit of an oddity anyway, but having scrollable cursors in embedded access, but not in remote access is IMHO less acceptable than bad performance. As long as the performance behaviour is clearly documented, people can make the decision if the bad performance is worth the utility of scrollable cursors for themselves. I've just committed network support for scrollable cursors to master. Prefetch logic is supported for NEXT/PRIOR navigations. Protocol changes are documented here: https://github.com/FirebirdSQL/firebird/issues/7051 The test set to cover different navigation scenarios is being created, once it's ready we'll validate the results between PSQL cursors, embedded access, network access w/prefetch and network access wo/prefetch (FOR UPDATE added to SELECTs). Obviously, we expect them to match ;-) I'd appreciate if Mark could test scrollability from the Jaybird side too (i.e. without fbclient involved), but the new protocol should be supported for that. The committed version is linked to protocol 17 which was introduced for 4.0.1. Thus technically, this improvement can be backported and released together with v4.0.1. However, I'm not sure this risk is acceptable for a point release and I'm ready to introduce protocol 18 in master for v5. Opinions, please. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Mark et al, Yes, I think that is perfectly acceptable for an initial version. Scrollable cursors are a bit of an oddity anyway, but having scrollable cursors in embedded access, but not in remote access is IMHO less acceptable than bad performance. As long as the performance behaviour is clearly documented, people can make the decision if the bad performance is worth the utility of scrollable cursors for themselves. I've just committed network support for scrollable cursors to master. Prefetch logic is supported for NEXT/PRIOR navigations. Protocol changes are documented here: https://github.com/FirebirdSQL/firebird/issues/7051 The test set to cover different navigation scenarios is being created, once it's ready we'll validate the results between PSQL cursors, embedded access, network access w/prefetch and network access wo/prefetch (FOR UPDATE added to SELECTs). Obviously, we expect them to match ;-) I'd appreciate if Mark could test scrollability from the Jaybird side too (i.e. without fbclient involved), but the new protocol should be supported for that. The committed version is linked to protocol 17 which was introduced for 4.0.1. Thus technically, this improvement can be backported and released together with v4.0.1. However, I'm not sure this risk is acceptable for a point release and I'm ready to introduce protocol 18 in master for v5. Opinions, please. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 2021-10-27 09:55, Dmitry Yemanov wrote: 23.10.2021 17:13, Mark Rotteveel wrote: If record buffering is hard, it could have been done **without** it. Then it would have been the choice of the user whether it is worth the performance implications or not. So you consider acceptable that forward-only usage of scrollable cursors is 10x slower than for regular cursors? Personally, I consider it hard to explain. Yes, I think that is perfectly acceptable for an initial version. Scrollable cursors are a bit of an oddity anyway, but having scrollable cursors in embedded access, but not in remote access is IMHO less acceptable than bad performance. As long as the performance behaviour is clearly documented, people can make the decision if the bad performance is worth the utility of scrollable cursors for themselves. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
23.10.2021 17:13, Mark Rotteveel wrote: If record buffering is hard, it could have been done **without** it. Then it would have been the choice of the user whether it is worth the performance implications or not. So you consider acceptable that forward-only usage of scrollable cursors is 10x slower than for regular cursors? Personally, I consider it hard to explain. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 23-10-2021 15:14, Dimitry Sibiryakov wrote: Mark Rotteveel wrote 23.10.2021 14:31: I'm not sure why not. Because of record buffering it is too hard for a feature with so little usage. I disagree, either it should have been implemented fully, or not at all. If record buffering is hard, it could have been done **without** it. Then it would have been the choice of the user whether it is worth the performance implications or not. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Thanks guys for responding. The only issue I have is that the release notes don't seem to tell you that DSQL scrollable cursors are only available for local databases. On 23/10/2021 14:14, Dimitry Sibiryakov wrote: Mark Rotteveel wrote 23.10.2021 14:31: I'm not sure why not. Because of record buffering it is too hard for a feature with so little usage. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Mark Rotteveel wrote 23.10.2021 14:31: I'm not sure why not. Because of record buffering it is too hard for a feature with so little usage. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
On 23-10-2021 14:04, Tony Whyman wrote: I have been testing out the scrollable cursors API and working with the example employee database. What I am seeing is that scrollable cursors only seem to work with local databases and not remote databases. Is this true? There seems to be nothing in the release notes to suggest this. I am doing a simple test, using 'Select * from EMPLOYEE order by EMP_NO' as the query string and calling OpenCursor with CURSOR_TYPE_SCROLLABLE. If I open the database with the connect string "employee" then FetchPrior etc all seem to work OK. If, instead, I use the connect string inet://localhost/employee, then I get a "Feature not supported" error message with calling FetchPrior (FetchNExt still works). I see the same behaviour with Firebird 3.0.5 and 4.0.0. Yes, scrollable cursor have not been implemented in the wire protocol. I'm not sure why not. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird and DSQL Scrollable Cursors
Tony Whyman wrote 23.10.2021 14:04: Is this true? Yes. This feature is not implemented in standard network plugin and so far nobody created a custom one. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Firebird and DSQL Scrollable Cursors
I have been testing out the scrollable cursors API and working with the example employee database. What I am seeing is that scrollable cursors only seem to work with local databases and not remote databases. Is this true? There seems to be nothing in the release notes to suggest this. I am doing a simple test, using 'Select * from EMPLOYEE order by EMP_NO' as the query string and calling OpenCursor with CURSOR_TYPE_SCROLLABLE. If I open the database with the connect string "employee" then FetchPrior etc all seem to work OK. If, instead, I use the connect string inet://localhost/employee, then I get a "Feature not supported" error message with calling FetchPrior (FetchNExt still works). I see the same behaviour with Firebird 3.0.5 and 4.0.0. Tony Whyman MWA Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
