Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Make Jaybird behave same az Oracle JDBC driver [JDBC559] #592

Closed
firebird-automations opened this issue Oct 30, 2018 · 7 comments
Closed

Comments

@firebird-automations
Copy link

Submitted by: Attila Molnár (e_pluribus_unum)

Is related to JDBC562

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!

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Commented by: Attila Molnár (e_pluribus_unum)

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.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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 JDBC562 for this.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC562 [ JDBC562 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Attila, I have implemented JDBC562. 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?

@firebird-automations
Copy link
Author

Commented by: Attila Molnár (e_pluribus_unum)

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants