Your Take on Oracle JDBC Drivers

Home

News: Your Take on Oracle JDBC Drivers

  1. Your Take on Oracle JDBC Drivers (39 messages)

    The Oracle JDBC development team would like to hear your experience and expectation; please take a few minutes and tell them what works and what does not work for you. What do you like and dislike about their JDBC products? What expectations do you have now that the Oracle + Sun merger has been approved by the stock holders?

    Threaded Messages (39)

  2. If there's an invalid column name, then fracking tell me which one! Similar for invalid table name, etc. I still can't comprehend why we need to pay thousands of dollars for a product that can't even provide sane error messages. My rant on this subject triggered other suggestions as well, please read the comments.
  3. Put the version number in the file name so we don't have a bunch of ojdbc14.jar files sitting around with different sizes and no way of knowing which version they are.
  4. 1 cent[ Go to top ]

    LOL I agree with Martijn. This includes almost every product I use, but Oracle errors in particular are horrificially bad. On the other side though, MySQL and SQL Server errors are pretty good. Maybe we can get everyone to use those products instead. :)
  5. Java[ Go to top ]

    My concern is they do something similar to java
  6. We had built an application using SQL server - had an effective system for managing the connections and connection pool. When we "ported" to Oracle, the JDBC implementation was like a step back into the stone age of C. Each use of a connection, and the transaction model needed to be reworked to deal with the explosion of open cursors in the Oracle environment. Thanks Oracle.
  7. Connection pooling is not a database engine's responsibility. This task corresponds to the application server, usually.
  8. batching retardedness[ Go to top ]

    Last I checked (couple months ago) Oracle jdbc drivers didn't support the ability to tell you how many rows were updated with each of your batch statements. From Oracle: "For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown." My response: "not possible to know" my ass. Not being able to get this information makes Oracle batching useless in many places I would like to use it.
  9. Re: batching retardedness[ Go to top ]

    This is a known and long standing issue; manifestly, the JDBC development team has not been very successful is making this into the must-have list for the RDBMS team. Direct/detailed community inputs like this, are very for us helpful to weight-in heavily in the face of thousands of enhancement requests that the RDBMS team receive. This issue will definitely be part of our dev priorities that we promise to post in the future. Kuassi Mensah JDBC Produdt Management, Oracle
  10. Re: batching retardedness[ Go to top ]

    This issue will definitely be part of our dev priorities that we promise to post in the future.
    Kuassi, This is good to hear. I personally know many developers that would be very happy to see this feature. As things become more asynchronous, batching is one of the single biggest things one can do to improve performance when the DB is involved. Other then not being able to get row counts I'm quite happy with Oracle's support for batching.
  11. Re: batching retardedness[ Go to top ]

    It's a very good notice to know that Oracle team could work on this issue. Oracle JDBC supports two distinct models for update batching: - the standard model, implementing the Sun Microsystems JDBC 2.0 Specification, which is referred to as standard update batching - the Oracle-specific model, independent of the Sun Microsystems JDBC 2.0 Specification, which is referred to as Oracle update batching. We have this problem using hibernate, and we've developed an extension that allow us to use the oracle-specific model. We´ve posted this approach long time ago in hibernate forum. https://forum.hibernate.org/viewtopic.php?f=1&t=929931&p=2401306#p2401306 https://forums.hibernate.org/viewtopic.php?t=978756 But we still can't identify which statement failed in the batch. Regards. Hernán.
  12. Re: batching retardedness[ Go to top ]

    Batching in JDBC is all hosed anyway, imho. The fact that only homogeneous statements can be batched together is the really frustrating part. Given that the Oracle DB server does not have this capability leaves the JDBC driver folks high-and-dry unfortunately.
  13. Why don't their JDBC driver writers just go and have a look at the code for the various open source ORM tools and work to remove the need to have custom SQL just to get decent performance on basic things like retrieving the list of columns for a table (instead using a SELECT on "ALL_TAB_COLUMNS" rather than DatabaseMetadata). Was like that the last time I looked.
  14. Oracle Alternatives[ Go to top ]

    Oracle appears to be a terrible waste of money. What alternatives have you had success with?
  15. Re: Oracle Alternatives[ Go to top ]

    Oracle appears to be a terrible waste of money. What alternatives have you had success with?
    PostgreSQL is a good choice for OLTP and one of the few good Open Source databases left. I worked with Oracle for about 10 years and you always ran into issues with their implementation of the JDBC specification. You are quit correct, they are really a rip off once you start using enterprise licenses. The other annoying aspect is using vendor based systems they rely on a specific version of Oracle. At my old company we had 10.2.0.2 RAC, 10.2 non-RAC, 10.1 and 9. What a cluster @#$%.
  16. Oracle never got JDBC right, amongst everything else regarding Java. Did anyone figure out what the "g" was in Oracle 10g? We installed Oracle 10g database on a grid, but nothing happened, perhaps missed an installation option somewhere. And now, expectations of Oracle now in charge of Java, yikes...
  17. Re: Oracle Alternatives[ Go to top ]

    On the "never got JDBC right" part; we have hundreds of thousands of customers running their day to day and mission critical business using Oracle JDBC drivers; most of them are happy about it. Not arguing that there aren't issues but can you be more specific (the form gives you the opportunity to do so)? Kuassi Mensah JDBC Product Management, Oracle
  18. Re: Oracle Alternatives[ Go to top ]

    Using a CHAR(n) column in the where clause is another example. In SQL*Plus, you only need to feed the search condition with the leading chars, the engine will append the white space to the right side for you. But in Oracle JDBC, you'll have to append the white space before you set the parameter to the prepared statement.
  19. It's a very good notice to know that Oracle team could work on this issue. Oracle JDBC supports two distinct models for update batching: - the standard model, implementing the Sun Microsystems JDBC 2.0 Specification, which is referred to as standard update batching - the Oracle-specific model, independent of the Sun Microsystems JDBC 2.0 Specification, which is referred to as Oracle update batching. We have this problem using hibernate, and we've developed an extension that allow us to use the oracle-specific model. We´ve posted this approach long time ago in hibernate forum. https://forum.hibernate.org/viewtopic.php?f=1&t=929931&p=2401306#p2401306 https://forums.hibernate.org/viewtopic.php?t=978756 But we still can't identify which statement failed in the batch. Regards. Hernán.
  20. I recently tried to make use of parameter type metadata though the most recent JDBC thin client and received an error message stating that it was an unsupported feature. As the new owner of Java, Oracle could make show it's support by implementing all useful features of the JDBC spec.
  21. I recently tried to make use of parameter type metadata though the most recent JDBC thin client and received an error message stating that it was an unsupported feature. As the new owner of Java, Oracle could make show it's support by implementing all useful features of the JDBC spec.
    I had the same issue on a recent project using Oracle's JDBC driver. We downloaded an evaluation of the DataDirect Oracle JDBC driver because they simply provided better JDBC 3.0 support such as parameter metadata queries.
  22. Introspection (which return colummn names) has "limitations" when it comes to public synonyms. Perhaps it has been fixed in the meantime (BUG-3320782)
  23. Metadata and batching support. You get a ton of information from the DB if you use anonymous PL/SQL blocks. Not sure why you can't get the same info from the JDBC driver. This looks more like a driver deficiency than a lack of support from the RDBMS team.
  24. Exceptions + CLOB / BLOB[ Go to top ]

    Exceptions top of the list. I've also been bitten by truncation of CLOB / BLOG in the light driver.
  25. The JMX MBean for the Oracle 11g driver we're using (11.1.0.7.0) is clearly broken. It's a small and not exceptionally interesting MBean to start with -- having only one attribute, LoggingEnabled. This attribute is read/write, yet any attempt to set the attribute's value is imply ignored without any exception. Yet that's supposed to be the meaningful action on this MBean -- setting this attribute. The operations on the MBean simply return false currently -- and the "impact" of each is currently denoted as "unknown". It would seem these should both be attributes or operations with an impact of "info". I was interested to see an MBean associated with the JDBC driver, but overall this is just a set of bugs with no usable functionality whatsoever.
  26. Why the boolean type is not supported in CallableStatement? Is it so hard to support it?
  27. I do not think so. Maybe Oracle has outsourced their JDBC driver development work?
  28. oracle jdbc driver[ Go to top ]

    I just recently discovered that the oracle jdbc driver has a nice undocumented "feature". Seems that if you use the "setTimestamp(int, Timestamp, Calendar)" method on a PreparedStatement, the driver deems it appropriate to actually call the "setTimeInMillis" method on the passed in Calendar object. How nice of it to alter an INPUT parameter. Not only that, it set it to some date in the year 1970. Now, the documentation on that method says it's supposed to use the Calendar object to help figure out the time zone. Nothing in the javadocs says that the method is destructive to the Calendar object. Took me quite a while to figure this one out. I had to use CGLIB and put a proxy around the Calendar object to track the calls to the setter methods to finally be able to truly blame the oracle jdbc driver for changing the value. Postgres had the same problem and fixed it: http://opensource.atlassian.com/projects/hibernate/browse/HHH-405 setTimeInMillis([18000000]) java.lang.Throwable at MyInterceptor.intercept(AbstractLicenseRaterWrapper.java:152) at $java.util.GregorianCalendar$$EnhancerByCGLIB$$628148db.setTimeInMillis() at java.util.Calendar.setTime(Calendar.java:1032) at oracle.jdbc.driver.OraclePreparedStatement.setTimestampInternal(OraclePreparedStatement.java:10319) at oracle.jdbc.driver.OraclePreparedStatement.setTimestamp(OraclePreparedStatement.java:10268) at org.apache.commons.dbcp.DelegatingPreparedStatement.setTimestamp(DelegatingPreparedStatement.java:198) at org.hibernate.type.CalendarType.set(CalendarType.java:50) at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136) at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:116) at org.hibernate.engine.query.NativeSQLQueryPlan.bindNamedParameters(NativeSQLQueryPlan.java:119) at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:163) at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1163) at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:334)
  29. Re: oracle jdbc driver[ Go to top ]

    This reminds me: The 9i version of the JDBC driver had problems with timezones (it would sometimes display a time that was off by an hour). Wonder if that got fixed in the new drivers?
  30. Re: oracle jdbc driver[ Go to top ]

    First I have to say kudos to them for *finally* providing proper LOB support since 10g drivers. For years, their awful LOB support and requirement for users to cast to Oracle-specific interfaces to perform even basic LOB functionality (when standard JDBC APIs would have sufficed in every cases) was by far their biggest issue. So thank you for that. Perhaps its just perception (though given some of the remarks here I do not think so) but a big issue seems to be general stability and reliability from one driver version to the next. I have had difficulty getting stuff to work from one minor version to the next. I remember specifically issues with getting Statement#getGeneratedKeys() to work on various minor releases of Oracle 10g JDBC drivers. On one release it would work, but then no longer work on the very next minor release. And then only if one did not use comments in the INSERT statement which would cause some JDBC driver validation routine to think the statement was not an INSERT (because it did not begin with the INSERT keyword).
  31. Meaningful error messages[ Go to top ]

    To understand the exact error message I'm searching ORA-XXX code. It will be good to have some codes for DatabaseNotAvailable, ForeignKeyError etc.
  32. Recent drivers are better but here are my main complaints, I also appreciate the opportunity to vent. 1. Driver (in)compatibility with older versions of Oracle. I realize Oracle only support back X number of versions but I ran into the case where where version X of the database was technically still supported, but only by version X+2 of the driver, not the latest version of the driver, e.g. X+3. So it wasn't possible to have one driver that could access all the currently supported Oracle versions. This was especially a problem with database links from a recent Oracle version back to older Oracle instances. Ideally the drivers would support all versions, even if they aren't under support, and of course if there's an issue it just wouldn't be supported. But my experience was they just don't work at all or not well enough to use. Another option would be to allow multiple versions of the Oracle driver to exist in the same VM. If this is possible now I would love to know how to do it. Then I could set up different JBoss datasources each with the appropriate version of the driver and would be a lot happier. We had to use a 3rd party Oracle driver (Oranxo) for this reason, as well as poor support for BLOB / CLOB (at the time) in the Oracle driver. 2. Absolutely asinine behavior with binding Dates and Timestamps. Older versions got the binding plain wrong. Now there are v8compatible parameters, etc. that don't do anything useful. The long and short of it is there isn't any way to get the Oracle database to use an index on a Date column, unless you use some of the flags. But then it would bind everything as a Date, and won't use an index on a Timestamp column. And you can't really cast() your way out of the mess. cast(? as date) appeared to work for me but then after the JDBC driver ran a while would fail with an incomprehensible error message. One way that might work but I refused to do was to treat all Dates / Timestamps as Strings. Why the Oracle database can't just do the cast internally and use the !@#$nig index regardless is incomprehensible to me.
  33. 2. Binding problem with Date/Timestamp not using indexes
    +1 I encountered also this issue and we had to cast PreparedStatement to internal representation then use driver datatypes tricks. LLE ps: however worst is not the word I would use
  34. Dear All, Funny that they should ask for suggestions on improving the JDBC drivers. Oracle can look no further than findbugs to start with. :-) I used their drivers in a little article I wrote about using findbugs on closed-source software. http://java-monitor.com/forum/showthread.php?t=200 Kees Jan
  35. +1 on useful error messages with name of offending object/column as most needed feature. +1 on including specific version information in the filename. Will save us having to look in the MANIFEST.MF and rename it everytime we download one and add it to our repo. Is not really JDBC specific but my next highest gripe with Oracle is the limit of 30 characters for an identifier (object/column name).
  36. Driver version coexistence[ Go to top ]

    Make it possible to use different versions of Oracle drivers in the same jvm. One application server instance can host multiple applications. Each of them could possibly connect to different versions of Oracle databases or otherwise have different driver requirements. But at present, collisions in class loading can prevent this scenario. This might boil down to modularity issues handled by specs like OSGi but that does not obviate the need to consider this from JDBC driver perspective. The issue should not be pushed aside even if it would require work outside JDBC spec and maybe internals of jre. Pontus
  37. Java EE Apps servers such as Weblogic and OC4J deal with that through their custom class loaders. But that might not be the use case you are looking for Kuassi Mensah Oracle JDBC Product Management
  38. Maybe I live in the past but there was a time when the drivers had to be in the system class path. If you had two versions of the driver with the same driver class name then the problem was evident. Pontus
  39. I glanced at the WebLogic 10 documents and noticed that the driver class is still required to be in the system class path. Quote from the WebLogic 10 docs: "Driver Class Name The full package name of JDBC driver class used to create the physical database connections in the connection pool. (Note that this driver class must be in the classpath of any server to which it is deployed.)" If different versions of the driver have the same driver class name and the driver class has to be in the system class path then the class loader will pick the first one in the class path, doesn't it?. I don't see how separate class loaders would help a bit. Pontus
  40. How about a command line SQL client (SQL*Plus Lite) within the jar? Something very simple to allow you to quickly test your connection parameters, username/passwords, permissions, etc. from the command line.