On Wed, 12 Dec 2012 16:56:54 +0400, Dmitry Yemanov <[email protected]> wrote: > All, > > Firebird v3 supports scrollable cursors in PSQL and I'm going to surface
> them at the DSQL level as well. However, there's a question about how > they should be declared. In PSQL, this is done in the cursor > declaration, as per SQL spec: > > DECLARE MY_CUR [SCROLL | NO SCROLL] CURSOR FOR (<select expr>) > > However, it doesn't fit DSQL queries and the SQL spec doesn't help here, > because it expects all result sets being opened via explicit cursors. As far as I can see SQL CLI spec does say something about statement descriptors and exposing a GetStmtAttr and SetStmtAttr where one of the attributes is CURSOR_SCROLLABLE (and SQL 2003 CLI section 5.4 talks about implicit cursor creation which would apply here). A quick scan of the spec seems to indicate that scrollability is a property of the statement handle and not of the query itself, so maybe the API(s) for creation of a statementhandle would need to be extended to set additional properties. > Initially, I was going to add an optional [WITH SCROLL] clause to the > select expression syntax for this purpose. At the conference, Mark said > that he would hate to parse the SQL text to find whether it's scrollable > or not, in order to use the proper API calls. So some other solution is > needed. The one that was raised there - add statement level parameter > blocks to the new API and use one of the tags to indicate scrollability. > I don't have much against this idea, but I'd like to mention that SQL > parsing can be easily avoided with the initial idea as well - just call > isc_request_info() to ask whether the prepared query is scrollable or not. The issue I have isn't so much with parsing the SQL to find out if a query is scrollable, but with the fact that I would need to parse the SQL statement and insert a WITH SCROLL in the right position in that query text (and yes, potentially deal with cases where the user did specify WITH SCROLL himself). In JDBC, scrollability is defined by specifying the resultset type at statement creation with a parameter, and it should not be specified by an option in the query text by the user. JDBC dictates that if an option is exposed in the JDBC API, then a user should not specify that option through query text; a driver implementation itself is free to use query text to access/enable etc that option (but I would really prefer not to): http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#createStatement(int,%20int) http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareCall(java.lang.String,%20int,%20int) http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int,%20int) Where the resultset type is one of: TYPE_FORWARD_ONLY The constant indicating the type for a ResultSet object whose cursor may move only forward. TYPE_SCROLL_INSENSITIVE The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet. TYPE_SCROLL_SENSITIVE The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet. Because of this, having an API that configures the scrollability gives a lot more flexibility to a driver implementer without resorting to complex string parsing and manipulation, add to that the potential issue if somebody specifies WITH SCROLL and the driver does not actually support it. I will answer in more detail this weekend. Mark ------------------------------------------------------------------------------ LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial Remotely access PCs and mobile devices and provide instant support Improve your efficiency, and focus on delivering more value-add services Discover what IT Professionals Know. Rescue delivers http://p.sf.net/sfu/logmein_12329d2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
