Actually I'm not sure that the problem is limited to SQLServer only.
Anyway this fix can be moved to AbstractSQLServerDictonary.setNull() I
think.
Here is the patch (tested, works fine for me):
--- AbstractSQLServerDictionary.java.orig 2007-09-17 11:11:
59.000000000 +0400
+++ AbstractSQLServerDictionary.java 2007-09-17 12:15:04.000000000 +0400
@@ -104,7 +104,11 @@
stmnt.setString(idx, null);
else if (colType == Types.BLOB)
stmnt.setBytes(idx, null);
- else
+ else if (colType == Types.OTHER) {
+ // Some buggy drivers cannot properly set null values for type
Types.OTHER,
+ // use Types.VARCHAR to make setNull work
+ super.setNull(stmnt, idx, Types.VARCHAR, col);
+ } else
super.setNull(stmnt, idx, colType, col);
}
2007/9/17, Patrick Linskey <[EMAIL PROTECTED]>:
>
> Hi,
>
> What about if you override the setNull() method in SQLServerDictionary
> to convert OTHER to VARCHAR?
>
> I generally prefer that approach, since it isolates things to
> SQLServer a bit more, but on the other hand, setNull() is called from
> a number of places. I wonder if it's ever called with Types.OTHER
> except from that bit of code, or if this bug manifests itself in those
> situations as well.
>
> -Patrick
>
> On 9/16/07, Sergey Elin <[EMAIL PROTECTED]> wrote:
> > Here is a patch to solve my problem (OpenJPA 0.9.7):
> >
> > --- DBDictionary.java.orig 2007-04-20 08:44:36.000000000 +0400
> > +++ DBDictionary.java 2007-09-17 08:46:26.000000000 +0400
> > @@ -1075,7 +1075,9 @@
> > Column col, int type, JDBCStore store)
> > throws SQLException {
> > if (val == null) {
> > - setNull(stmnt, idx, (col == null) ? Types.OTHER :
> col.getType
> > (),
> > + // Some buggy drivers don't know how to set null with type
> > + // Types.OTHER,use Types.VARCHAR to make it happy
> > + setNull(stmnt, idx, (col == null) ? Types.VARCHAR :
> col.getType
> > (),
> > col);
> > return;
> > }
> > @@ -1219,7 +1221,9 @@
> > }
> >
> > if (val == null)
> > - setNull(stmnt, idx, (col == null) ? Types.OTHER :
> col.getType
> > (),
> > + // Some buggy drivers don't know how to set null with type
> > + // Types.OTHER,use Types.VARCHAR to make it happy
> > + setNull(stmnt, idx, (col == null) ? Types.VARCHAR :
> col.getType
> > (),
> > col);
> > else if (val instanceof String)
> > setString(stmnt, idx, val.toString(), col);
> >
> >
> > 2007/9/14, Sergey Elin <[EMAIL PROTECTED]>:
> > >
> > > It seems toplink uses java.sql.Types.VARCHAR for null values while
> OpenJPA
> > > uses java.sql.Types.OTHER.
> > > I can't check this for now, but I'll try it asap.
> > >
> > > 2007/9/14, Sergey Elin < [EMAIL PROTECTED]>:
> > > >
> > > > Hi,
> > > >
> > > > Yes it's still correct. But... In toplink this code works fine. I
> don't
> > > > know toplink internals, but I started to think there can be a
> solution for
> > > > my problem. It seems toplink use more smooth or more universal type
> > > > detection or has a solution for such kind of problem.
> > > >
> > > > 2007/9/13, Kevin Sutter <[EMAIL PROTECTED]>:
> > > > >
> > > > > Hi Sergey,
> > > > > I see that you have found the OpenJPA forum as well as the IBM
> Feature
> > > > > Pack
> > > > > forum... :-)
> > > > >
> > > > >
> > > > >
> http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=1054&thread=175344&cat=9
> > > > >
> > > > > From the discussion on the IBM forum, it sounds like you have
> narrowed
> > > > > down
> > > > > this problem to the JDBC driver in use. Is that still
> correct? Or,
> > > > > have
> > > > > your symptoms changed and now you think there's a problem in
> OpenJPA?
> > > > >
> > > > > Thanks,
> > > > > Kevin
> > > > >
> > > > > On 9/13/07, Sergey Elin < [EMAIL PROTECTED]> wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > then i try to call store procedure with null values via embedded
> > > > > websphere
> > > > > > jdbc driver on SQLServer I get an exception.
> > > > > >
> > > > > > [IBM][SQLServer JDBC Driver]Unable to determine the type of the
> > > > > specified
> > > > > > object.
> > > > > >
> > > > > > Here is the code:
> > > > > >
> > > > > > Query q=JpaManager.getEntityManager().createNativeQuery(
> > > > > > "Execute spTree_getSpecs_v2 @p_PARTY_ID=?1," +
> > > > > > " @p_GRANT=?2, @p_GROUP_ID=?3, @p_BRAND_ID=?4, @p_VENDOR_ID=?5,
> " +
> > > > > > "@p_ORDER=?6, @p_BEGIN=?7, @p_END=?8", "ItemPaging");
> > > > > >
> > > > > > q.setParameter(1, party_id);
> > > > > > q.setParameter(2, permission);
> > > > > > q.setParameter(3, group_id);
> > > > > > q.setParameter (4, brand_id);
> > > > > > q.setParameter(5, vendor_id);
> > > > > > q.setParameter(6, order);
> > > > > > q.setParameter(7, begin);
> > > > > > q.setParameter(8, end);
> > > > > > q.getResultList();
> > > > > >
> > > > > > stack trace:
> > > > > >
> > > > > > Caused by: < 0.0.0 nonfatal general error>
> > > > > > org.apache.openjpa.persistence.PersistenceException:
> [IBM][SQLServer
> > > > > JDBC
> > > > > > Driver]Unable to determine the type of the specified object.
> > > > > > at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException (
> > > > > > DBDictionary.java:3786)
> > > > > > at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(
> > > > > SQLExceptions.java
> > > > > > :97)
> > > > > > at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(
> > > > > SQLExceptions.java
> > > > > > :83)
> > > > > > at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(
> > > > > SQLExceptions.java
> > > > > > :59)
> > > > > > at
> > > > >
> org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeQuery(
> > > > > > SQLStoreQuery.java:301)
> > > > > > at org.apache.openjpa.kernel.QueryImpl.execute (QueryImpl.java
> :985)
> > > > > > at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java
> :796)
> > > > > > at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java
> :766)
> > > > > > at org.apache.openjpa.kernel.DelegatingQuery.execute (
> > > > > DelegatingQuery.java
> > > > > > :533)
> > > > > > at org.apache.openjpa.persistence.QueryImpl.execute(
> QueryImpl.java
> > > > > :227)
> > > > > > at org.apache.openjpa.persistence.QueryImpl.getResultList(
> > > > > QueryImpl.java
> > > > > > :269)
> > > > > > at ru.topsbi.teko.beans.ui.news.NewsDetailsItemsBean.fetchNews(
> > > > > > NewsDetailsItemsBean.java:134)
> > > > > > at ru.topsbi.teko.beans.ui.news.NewsDetailsItemsBean.getNews(
> > > > > > NewsDetailsItemsBean.java:212)
> > > > > > at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method)
> > > > > > at sun.reflect.NativeMethodAccessorImpl.invoke(
> > > > > > NativeMethodAccessorImpl.java
> > > > > > :64)
> > > > > > at sun.reflect.DelegatingMethodAccessorImpl.invoke(
> > > > > > DelegatingMethodAccessorImpl.java :43)
> > > > > > at java.lang.reflect.Method.invoke(Method.java:615)
> > > > > > at com.sun.faces.el.PropertyResolverImpl.getValue(
> > > > > > PropertyResolverImpl.java
> > > > > > :79)
> > > > > > ... 55 more
> > > > > >
> > > > > > The problem is then I call stored procedure with null, then
> null's
> > > > > type
> > > > > > treated as javax.sql.Types.OTHER. Then it passes to websphere's
> > > > > embedded
> > > > > > driver and it throws an exception because it doesn't know what
> to do
> > > > > with
> > > > > > it. If I change value's type to java.sql.Types.NUMERIC in
> runtime
> > > > > (in
> > > > > > debug
> > > > > > mode) then everything works.
> > > > > >
> > > > > > I don't actually know is this a bug or a feature and don't know
> > > > > where to
> > > > > > report it. =(
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
>
>
> --
> Patrick Linskey
> 202 669 5907
>
