Issue Details (XML | Word | Printable)

Key: JDBC-559
Type: New Feature New Feature
Status: Closed Closed
Resolution: Won't Fix
Priority: Minor Minor
Assignee: Mark Rotteveel
Reporter: Attila Molnár
Votes: 0
Watchers: 0
Operations

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

Make Jaybird behave same az Oracle JDBC driver

Created: 30/Oct/18 07:14 AM   Updated: 08/Dec/18 09:27 AM
Component/s: None
Affects Version/s: None
Fix Version/s: None

Issue Links:
Relate
 


 Description  « Hide
Hi Mark!

There are minor behavoiur differences between Jaybird and oracle.jdbc.driver.OracleDriver (jdbc:oracle:thin protocol).
I'd like to see a System property (e.g. "org.firebirdsql.jdbc.behaviour.oracle") which makes these differences disappear.

For now I've got these differences :
- getGeneratedKeys() returns null in Jaybird, but OracleDriver throws SQLException
- DatabaseMetaData object identifier character escapeing is different (getTables, getColumns, getIndexInfo, getProcedures, getProcedureColumns, etc)

Thank You!

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 30/Oct/18 10:02 AM
Can you create individual tickets for each problem?

Regarding `getGeneratedKeys()`, please be specific: under what conditions does it return null? The current implementation in 2.2, 3.0 and HEAD can't return null. It either throws an exception if the statement is closed, or it returns a result set (which will be empty if no generated keys query was executed, as required by the JDBC specification).

I will also need explicit details of the observed differences regarding character escaping, and please reference the JDBC specification as the primary source, not the Oracle driver.

Attila Molnár added a comment - 30/Oct/18 01:14 PM
Strange, now I could not reproduce the getGeneratedKeys() null problem. (3.0.5)
I see that the getGeneratedKeys() throwing SQLException instead of returning empty RS is the bug of OracleDriver, but I don't think I can push trough Oracle to change that. I can accept if you don't want to

OracleDriver has no escaping for identifiers, and specification also not mentions escape support.

Mark Rotteveel added a comment - 30/Oct/18 03:56 PM - edited
Regarding reporting to Oracle: you can always try :). I'd prefer not to emulate Oracle bugs in this regard.

With regard to escapes in patterns, JDBC does specify it, but not very clearly (that is, not together with the definition of the pattern itself), but it is linked with an @see from each method using patterns: see DatabaseMetaData.getSearchStringEscape():

"""
Retrieves the string that can be used to escape wildcard characters. This is the string that can be used to escape '_' or '%' in the catalog search parameters that are a pattern (and therefore use one of the wildcard characters).

The '_' character represents any single character; the '%' character represents any sequence of zero or more characters.
"""

And the JDBC 1.20 specification also says "A number of metadata methods take String search patterns as arguments. These search patterns are the same as for ODBC, where a '_' iimplies a match of any single character and a '%' implies a match of zero or more characters."

And ODBC defines (see also https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/pattern-value-arguments?view=sql-server-2017)

"""
The search pattern characters are:

 * An underscore (_), which represents any single character.

 * A percent sign (%), which represents any sequence of zero or more characters.

 * An escape character, which is driver-specific and is used to include underscores, percent signs, and the escape character as literals. If the escape character precedes a non-special character, the escape character has no special meaning. If the escape character precedes a special character, it escapes the special character. For example, "\a" would be treated as two characters, "\" and "a", but "\%" would be treated as the non-special single character "%".

    The escape character is retrieved with the SQL_SEARCH_PATTERN_ESCAPE option in SQLGetInfo. It must precede any underscore, percent sign, or escape character in an argument that accepts search patterns to include that character as a literal. Examples are shown in the following table.
"""

The DatabaseMetaData.getSearchStringEscape() is JDBC's equivalent of "SQL_SEARCH_PATTERN_ESCAPE option in SQLGetInfo".

I notice however that the ODBC requirement "If the escape character precedes a non-special character, the escape character has no special meaning." is currently not met by the implementation and this would need to be fixed. I have created JDBC-562 for this.

Mark Rotteveel added a comment - 31/Oct/18 04:52 PM - edited
Attila, I have implemented JDBC-562. Do you want me to build a snapshot of Jaybird 4 so you can check if the resulting behavior of the patterns fixes the observed difference with Oracle?

Attila Molnár added a comment - 05/Nov/18 08:55 AM - edited
I made a workaround in my code for the escapeing problem, no need for the snapshot.

Thank You for the clarification and for the fix.