I notice that on the Postgres Web site, PG supports the following:
[quote]MOVE [ direction [ FROM | IN ] ] cursor_name
where direction can be empty or one of:
So, does this mean that, contrary to what Xojo documentation says online, the RecordSet class supports all these for Postgres? I only use SQLite for now, but am curious.
According to the LR, PostgreSQL in Xojo only supports MoveNext
It’s under “Record Navigation”.
I was aware of that when I posted “contrary to what Xojo documentation says online.” I was just wondering if the Xojo documentation is behind the times, since Postgres clearly supports this sort of navigation natively.
The Xojo plugin does not support it. If you have a need for that you might have better luck with the SQL MBS plugin (not used it but it seems to have a lot of functionality).
The PostgreSQL server does support these kind of navigation for server side cursors which can be declared, moved and fetched. The Xojo PostgreSQLDatabase plugin does not declare such for SQLSelect nor PreparedStatement (which in contrast are server side prepared statements, not parametrized queries which may be more appropriate see <https://xojo.com/issue/37805>) but retrieves the full result set and convert this to a Recordset. On this, there is only the MoveNext implemented (FR to change this is <https://xojo.com/issue/22656>) Forward-only processing of query results may be implemented more efficiently by using PQsetSingleRowMode - FR is <https://xojo.com/issue/26396> but I assume this would require quite some structural changes to the plugin.
However, if you have a need for a server side cursor, you should be able to send the DECLARE and optionally MOVE SQL commands through SQLExecute and retrieve rows by sending FETCH commands through a SQLSelect.