Issue Details (XML | Word | Printable)

Key: JDBC-223
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Mark Rotteveel
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

Revise and extend Escape syntax support for JDBC 4.1

Created: 03/Jan/12 08:30 PM   Updated: 24/Dec/12 04:18 PM
Component/s: JDBC driver
Affects Version/s: None
Fix Version/s: Jaybird 3.0.0

Sub-Tasks  All   Open   

 Description  « Hide
The current support for the JDBC escape syntax (section 13.4 JDBC 4.1 spec) is missing some features:
* Limiting Returned Rows Escape (13.4.6)
* Function escape for function names listed in Appendix D need to be doublechecked for completeness (this includes updating the metadata for getXXXFunctions() if necessary)
* Double check support for LIKE escaping (13.4.5)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 22/Dec/12 12:20 PM
Current implementation will forward any function call in the escape to Firebird, without considering whether the function is allowed according to appendix D of the JDBC spec.

Mark Rotteveel added a comment - 22/Dec/12 04:38 PM
Added tests for functions defined in appendix D.1, D.2, D.3 (partially) and D.4; a lot of them are failing right now as Jaybird doesn't support them (yet).

Mark Rotteveel added a comment - 23/Dec/12 02:05 PM
Updated the DatabaseMetaData.getXXXFunctions() method to return those functions that will work on Firebird 2.1 and higher, or with equivalent UDFs installed; no version dependencies are checked, and some functions not included in the list might work if a proper UDF is installed with matching name (and parameter order).

Calling the JDBC function escape with a function name that is not defined in appendix D of JDBC 4.1 will now result in a FBSQLParseException (NOTE: exception type might change before final 2.3 release).

Mark Rotteveel added a comment - 23/Dec/12 02:07 PM
Now most functions are correctly supported.

Mark Rotteveel added a comment - 24/Dec/12 02:00 PM
Added test for outer join escape; fixed JDBC LIKE Escape character escape + added tests

Mark Rotteveel added a comment - 24/Dec/12 04:12 PM
Added support for Limiting Returned Rows Escape (section 13.4.6 of JDBC 4.1) {limit <rows> [offset <offset_rows>]}. It has the following limitations:
* Position should match a position that works for the Firebird ROWS clause
* The <rows> parameter can be parametrized (eg {limit ?} or {limit ? offset 10}
* The <offset_rows> parameter can only be a literal value (eg {limit 10 offset 20} or {limit ? offset 20}, a parameter here will result in a FBSQLParseException

The reasons for the first limitation is that the escape is translated in place to a ROWS <m> [TO <n>] clause because having Jaybird decide on the right position would be rather complex. The reason for last limitation is that {limit <rows> offset <offset_rows>} is translated to ROWS <offset_rows> TO <offset_rows> + <rows>, and the repeating parameter for <offset_rows> and reversal of parameter order would be a problem.