True, with the approach, DAS can use Metadata API and treat Derby specially (as Derby Embedded Driver API returns FALSE, set it to TRUE)
1) If provided in Config - it will be used, no Metadata API check 2) If not provided in Config - Metadata API check - In this for Derby ALWAYS SET TO TRUE, and for anything else, set based on API return value. In case of exception set to FALSE. 3) Also for existing test cases - default TRUE will be assumed as they run using Derby and will not fail 4) For PostgreSQL - this approach will work as Metadata API returns FALSE for JDBC 3 compliant driver 5) In case there is any driver used which is not JDBC 3 compliant and the API is absent, then it will again be caught as exception and value set to FALSE. Please see if there are any further places for modification in the above. Regards, Amita On 7/24/07, Ron Gavlin <[EMAIL PROTECTED]> wrote:
Hi Amita, Since DAS has JDK 1.4 as a requirement and the JDBC 3.0 APIs are built into JDK 1.4, isn't it sufficient to interpret a JDBC 2.0 driver throwing a exception from supportsGetGeneratedKeys() as a false. Also, since the DAS is currently a pre-1.0 release, I don't think our solution needs to be driven by backwards-compatibility or whether test cases get broken or not. From my perspective, the default case (the absence of the attribute) should be driven by the JDBC driver's DatabaseMetadata.supportsGetGeneratedKeys() value. The useGetGeneratedKeys attribute could be explicitly set to true for cases like Derby where the driver's partial support for this feature is sufficient for the needs of the DAS. In the case of Oracle, they just started supporting getGeneratedKeys() in Oracle 10g R2. It is not supported in earlier versions of the database or drivers. So, using the DatabaseMetadata-driven approach, the DAS should be able to support all Oracle versions out of the box with no special config attribute. In the future, hopefully Derby will implement full getGeneratedKeys() support and thus would not require special configuration within the DAS. My two cents... - Ron ----- Original Message ---- From: Amita Vadhavkar <[EMAIL PROTECTED]> To: [EMAIL PROTECTED]; [email protected] Sent: Tuesday, July 24, 2007 8:56:36 AM Subject: Re: Flexibility in supporting JDBC's Statement.RETURN_GENERATED_KEYS in RDB DAS (JIRA-1417) Hi, Below are some details about the solution for JIRA-1353. Please review the patch. http://issues.apache.org/jira/browse/DERBY-242 - indicates that for 10.1.1.0, DatabaseMetadata.supportsGetGeneratedKeys() returns false. Also, checked that for the current Maven Repo's Derby version (10.1.2.1) same is happening. DatabaseMetadata.supportsGetGeneratedKeys() is not available in JDBC 2.0. (We can catch exception if it is thrown in the supports...() , but we can not detect cases like above - Derby) So, using DatabaseMetadata.supportsGetGeneratedKeys() (when config attribute is not set) may not be reliable in all cases. To keep the fix simple and also not to break existing test cases (which assume default TRUE), the following is changed in patch 1) New <Config> attribute <xsd:attribute name="useGetGeneratedKeys" type="xsd:boolean" default="true"/> 2) Default to TRUE - so old test cases and old configs continue to work 3) Remove vendor name hardcoding logic to set flag useGetGeneratedKeys So, in effect, with this patch (JIRA-1353) user will get an option to pass FALSE, when it is sure that the dbms driver in use does not support this feature. Thus, instead of hardcoding vendor names (without driver versions), the responsibility is given to user to pass FALSE when needed. Have tested this fix on Derby, DB2, MySQL and PostgreSQL. Also, new testcases (6) added - CheckSupportGeneratedKeys example Config XML using the above attribute (say for PostgreSQL), the XML will look as below-------------------------------------------------------------------------------------------- <Config xmlns="http:///org.apache.tuscany.das.rdb/config.xsd";; useGetGeneratedKeys="false"> </Config> -------------------------------------------------------------------------------------------------- User will need to pass the Config during creation of DAS instance. DAS.FACTORY.createDAS(config, getConnection()) or DAS.FACTORY.createDAS(config) or DAS.FACTORY.createDAS(InputStream configStream) The value of the attrib can be true/false. And Driver may/may not support GeneratedKeys. Based on this, following situations can arrive- A> Driver supports GeneratedKeys 1]Table is created with one column having GENERATED ALWAYS AS IDENTITY clause, Irrespective of value of useGetGeneratedKeys flag, insert command will succeed true flag value - insert.getGeneratedKey() will return key value false flag value - insert.getGeneratedKey() will throw RuntimeException - "Could not obtain generated key!" 2]Table is created with no column having GENERATED ALWAYS AS IDENTITY clause, Irrespective of value of useGetGeneratedKeys flag, insert command will succeed true flag value - insert.getGeneratedKey() - how should it behave? In case of Derby it is returning wrong results. false flag value - insert.getGeneratedKey() will throw RuntimeException - "Could not obtain generated key!" B> Driver does not support GeneratedKeys (say PostgreSQL) - tested with a test client - 1]Table can be created with no column having GENERATED ALWAYS AS IDENTITY clause, When value of useGetGeneratedKeys flag is false, insert command will succeed, insert.getGeneratedKey() will throw RuntimeException - "Could not obtain generated key!" When value of useGetGeneratedKeys flag is true, insert command will fail C> setConnection(java.sql.Connection) is called (and not setConnection( java.sql.Connection, Config)), default TRUE is assumed. When DBMS Driver does not support useGetGeneratedKeys, user needs to pass Config with useGetGeneratedKeys FALSE. After resolution of JIRA-1353, can we link JIRA-1417 to it? Regards, Amita On 7/10/07, Luciano Resende <[EMAIL PROTECTED]> wrote: > > It would be great if you could force a different exception in your > investigation (e.g jdbc driver supports returning the generated keys, > but the call gives back a different exception), and see what is the > resulted behavior of your proposal. > > > On 7/10/07, Amita Vadhavkar <[EMAIL PROTECTED]> wrote: > > That is right, I will also give it a try with some known rdbms dirvers, > > versions and list the results in JIRA-1417, and we will also analyze > further > > for alternatives. > > > > I also, saw some relevant links related to JIRA-1416 > (PostgreSQL...generated > > keys). > > [1] http://gborg.postgresql.org/project/pgjdbc/bugs/bugupdate.php?984 > > [2] http://archives.postgresql.org/pgsql-jdbc/2007-02/msg00074.php > > Based on [2], it looks like , server >=8.2 , has some support for auto > gen > > keys > > in PostgreSQL. > > > > So, validity of JIRA-1416 will be based on the exact version of > PostgreSQL, > > > > Regards, > > Amita > > > > On 7/10/07, Luciano Resende <[EMAIL PROTECTED]> wrote: > > > > > > Hi Amita > > > > > > Indeed we need a better way to handle this, my only concern with > > > this approach are the unknown side effects we can get if the exception > > > returned when you first pass the Statement.RETURN_GENERATED_KEYS is > > > not related to the JDBC driver supporting or not generated keys. > > > > > > On 7/9/07, Amita Vadhavkar <[EMAIL PROTECTED]> wrote: > > > > Hi, > > > > > > > > We are at present hardcoding some vendors (e.g. Oracle) in > > > ConnectionImpl to > > > > decide whether to > > > > use autogenerated key feature in preparedStatement. This logic is > > > subject to > > > > change > > > > as and when new features are supported by different databases and > > > different > > > > vendors. > > > > Instead, if we have a flexibility to check in the very first attempt > of > > > > connection.prepareStatement(queryString, > Statement.RETURN_GENERATED_KEYS > > > ); > > > > throws exception and based on that, if we can tweak the decision > making > > > > flag useGetGeneratedKeys to true/false, then, at any runtime of > DAS, > > > > "maximum once", there is > > > > a chance that exception will be thrown / caught and later all > attempts > > > will > > > > follow > > > > correct syntax of connection.prepareStatement() as supported by the > > > current > > > > rdbms > > > > driver being used. Based on JDBC specs, the only exception possible > > > from > > > > connection.prepareStatement() > > > > is SQLException. > > > > > > > > This check can be introduced in ConnectionImpl.prepareStatement(). > > > > Thoughts? > > > > > > > > Regards, > > > > Amita > > > > > > > > > > > > > -- > > > Luciano Resende > > > Apache Tuscany Committer > > > http://people.apache.org/~lresende > > > http://lresende.blogspot.com/ > > > > > > --------------------------------------------------------------------- > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > -- > Luciano Resende > Apache Tuscany Committer > http://people.apache.org/~lresende > http://lresende.blogspot.com/ > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
