[#JDBC-223] Revise and extend Escape syntax support for JDBC 4.1

Jaybird JCA/JDBC Driver

Revise and extend Escape syntax support for JDBC 4.1

Created: 03/Jan/12 08:30 PM Updated: 07/May/17 12:32 PM

Component/s:

JDBC driver

Affects Version/s:

None

Fix Version/s:

Jaybird 3.0.0

Sub-Tasks:

All

Open

~~Backport fix for JDBC LIKE escape character escape to 2.2~~

Closed

Mark Rotteveel

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:

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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).

[ Permalink | « Hide ]

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).

[ Permalink | « Hide ]

Mark Rotteveel added a comment - 23/Dec/12 02:07 PM

Now most functions are correctly supported.

[ Permalink | « Hide ]

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

[ Permalink | « Hide ]

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.