SQLException on trying to connect to ms-access using dbControl in Workshop
baiju
I get "java.sql.SQLException: Null keys not supported" exception when I try
to connect to ms-access db (MS-ACCESS 2000) on workshop using a database
control.
Here are the steps I followed....
I created a connection pool using sun's "sun.jdbc.odbc.JdbcOdbcDriver",
url = "jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb);DBQ=c:\temp\mydb.db"
and properties =
"DefaultDir=c:
DriverId=281
user=
FIL=MS Access
emp=oxpro"
I also entered the test table name and the admin console gave a successful
connection message. But in the workshop when I created a simple database
control of fetching 20 rows, it throws SQLException as shown at the end of
this message.
/*
* @jc:sql max-rows="20" statement="select name from mytable"
*/
public String[] getNames() throws SQLException;
It is worth noting that when I try to access the same data source/table
using the traditional Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:DRIVER......" etc, it works.
Thanks for your help,
Baiju.
The exception from the log file follows
:----------------------------------------------------
03 May 2004 17:27:51,495 WARN WebServiceTutorial: Id=myAccess;
Method=com.bea.wlw.runtime.core.control.DatabaseControlImpl.context_onAcquir
e(); Failure=java.sql.SQLException: Null keys not supported.
Nested Exception: java.lang.IllegalArgumentException: Null keys not
supported
at
weblogic.utils.collections.WeakConcurrentHashMap.get(WeakConcurrentHashMap.j
ava:189)
at
weblogic.utils.wrapper.WrapperFactory.getCachedWrapperClass(WrapperFactory.j
ava:51)
at
weblogic.utils.wrapper.WrapperFactory.getWrapperClass(WrapperFactory.java:18
3)
at
weblogic.utils.wrapper.WrapperFactory.getWrapperClass(WrapperFactory.java:17
1)
at
weblogic.jdbc.wrapper.JDBCWrapperFactory.getWrapper(JDBCWrapperFactory.java:
146)
at weblogic.jdbc.jts.Driver.newConnection(Driver.java:674)
at weblogic.jdbc.jts.Driver.createLocalConnection(Driver.java:196)
at weblogic.jdbc.jts.Driver.connect(Driver.java:154)
at
weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java
:305)
at
com.bea.wlw.runtime.core.control.DatabaseControlImpl.getConnection(DatabaseC
ontrolImpl.jcs:1430)
at
com.bea.wlw.runtime.core.control.DatabaseControlImpl.context_onAcquire(Datab
aseControlImpl.jcs:1322)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
com.bea.wlw.runtime.core.dispatcher.DispMethod.invoke(DispMethod.java:367)
at com.bea.wlw.runtime.core.container.Invocable.invoke(Invocable.java:423)
at com.bea.wlw.runtime.core.container.Invocable.invoke(Invocable.java:396)
at
com.bea.wlw.runtime.core.container.Invocable.fireEvent(Invocable.java:612)
at
com.bea.wlw.runtime.core.context.WlwThreadContext.sendEvent(WlwThreadContext
.java:980)
at
com.bea.wlw.runtime.core.context.WlwThreadContext.raiseEvent(WlwThreadContex
t.java:910)
at
com.bea.wlw.runtime.core.container.Container.raiseContextEvent(Container.jav
a:567)
at
com.bea.wlw.runtime.jcs.container.JcsContainer.onAcquire(JcsContainer.java:5
29)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
com.bea.wlw.runtime.core.dispatcher.DispMethod.invoke(DispMethod.java:367)
at com.bea.wlw.runtime.core.container.Invocable.invoke(Invocable.java:423)
at
com.bea.wlw.runtime.core.container.Invocable.sendContextEvent(Invocable.java
:533)
at
com.bea.wlw.runtime.jcs.container.JcsContainer.sendContextEvent(JcsContainer
.java:480)
at
com.bea.wlw.runtime.core.context.WlwThreadContext.acquireResources(WlwThread
Context.java:676)
at com.bea.wlw.runtime.jcs.container.JcsProxy.invoke(JcsProxy.java:309)
at $Proxy8.getMyco(Unknown Source)
at investigateJWS.Investigate.tbdemoinfo(Investigate.jws:49)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
com.bea.wlw.runtime.core.dispatcher.DispMethod.invoke(DispMethod.java:367)
at com.bea.wlw.runtime.core.container.Invocable.invoke(Invocable.java:423)
at com.bea.wlw.runtime.core.container.Invocable.invoke(Invocable.java:396)
at com.bea.wlw.runtime.core.container.Invocable.invoke(Invocable.java:248)
at
com.bea.wlw.runtime.core.bean.BaseContainerBean.invokeBase(BaseContainerBean
.java:198)
at
com.bea.wlw.runtime.core.bean.SLSBContainerBean.invoke(SLSBContainerBean.jav
a:103)
at
com.bea.wlwgen.StatelessContainer_ly05hg_ELOImpl.invoke(StatelessContainer_l
y05hg_ELOImpl.java:153)
at
com.bea.wlwgen.GenericStatelessSLSBContAdpt.invokeOnBean(GenericStatelessSLS
BContAdpt.java:62)
at
com.bea.wlw.runtime.core.bean.BaseDispatcherBean.runAsInvoke(BaseDispatcherB
ean.java:153)
at
com.bea.wlw.runtime.core.bean.BaseDispatcherBean.invoke(BaseDispatcherBean.j
ava:54)
at
com.bea.wlw.runtime.core.bean.SyncDispatcherBean.invoke(SyncDispatcherBean.j
ava:160)
at
com.bea.wlw.runtime.core.bean.SyncDispatcher_k1mrl8_EOImpl.invoke(SyncDispat
cher_k1mrl8_EOImpl.java:100)
at
com.bea.wlw.runtime.core.dispatcher.Dispatcher.remoteDispatch(Dispatcher.jav
a:161)
at
com.bea.wlw.runtime.core.dispatcher.Dispatcher.dispatch(Dispatcher.java:49)
at
com.bea.wlw.runtime.core.dispatcher.HttpServerHelper.exploreExec(HttpServerH
elper.java:275)
at
com.bea.wlw.runtime.core.dispatcher.HttpServerHelper.executeGetRequest(HttpS
erverHelper.java:593)
at com.bea.wlw.runtime.core.dispatcher.HttpServer.doGet(HttpServer.java:81)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(Servle
tStubImpl.java:971)
at
weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java
:402)
at
weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java
:305)
at
weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(W
ebAppServletContext.java:6354)
at
weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubjec
t.java:317)
at
weblogic.security.service.SecurityManager.runAs(SecurityManager.java:118)
at
weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletCo
ntext.java:3635)
at
weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java
:2585)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
[ServiceException]
Joe Weinstein
baiju wrote:
> Platform : Windows XP, Weblogic Platform 8.1 sp2
>
> I get "java.sql.SQLException: Null keys not supported" exception when I try
> to connect to ms-access db (MS-ACCESS 2000) on workshop using a database
> control.
> Here are the steps I followed....
> I created a connection pool using sun's "sun.jdbc.odbc.JdbcOdbcDriver",
> url = "jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb);DBQ=c:\temp\mydb.db"
>
Hi. I know it may be difficult to change things, but we don't support the
jdbc-odbc bridge driver because it is not well written. It has many bugs that
they won't fix, and it is not thread-safe, so it can hurt the weblogic server
process.
As to the current symptom, the actual failure is in jdbc at the server, so if
you will turn on jdbc logging at the server, and show us the full stacktrace of
the exception as it occurs in the server jdbc log, we can come closer to a
solution. However, if you can switch to a more enterprise-ready jdbc driver,
which may involve switching to a more enterprise-ready DBMS, you will avoid
many problems that will arise when you try to push access and jdbc-odbc to the
higher requirements of enterprise applications.
Joe
Joe Weinstein
baiju wrote:
> Hi Joe,
> Thanks for your prompt reply.
Sure. I looked at the log. I see a problem during the pool's trying to connect:
SQLException: SQLState(S1096) vendor code(0)
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Information type out of range
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6879)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7036)
at sun.jdbc.odbc.JdbcOdbc.SQLGetInfo(JdbcOdbc.java:4248)
at sun.jdbc.odbc.JdbcOdbcConnection.getOdbcCursorAttr2(JdbcOdbcConnection.java:1449)
at sun.jdbc.odbc.JdbcOdbcConnection.checkScrollCursorSupport(JdbcOdbcConnection.java:1335)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:383)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
I bet this has to do with the properties and/or URL defined in the pool definition.
If you will show me the standalone JDBC code you'd write to make a jdbc-odbc connection,
and show me the way you defined the pool (it's in the config.xml file), I may see
the problem there.
After that, I don't see any other jdbc problem! I think the "Null keys not supported"
is a *Warning*, attached to the connection by the jdbc-odbc driver during connect.
Joe
>
>
>>Hi. I know it may be difficult to change things, but we don't support the
>>jdbc-odbc bridge driver because it is not well written. It has many bugs
>
> that
>
>>they won't fix, and it is not thread-safe, so it can hurt the weblogic
>
> server
>
>>process.
>> As to the current symptom, the actual failure is in jdbc at the
>
> server, so if
>
>>you will turn on jdbc logging at the server, and show us the full
>
> stacktrace of
>
>>the exception as it occurs in the server jdbc log, we can come closer to a
>>solution.
>
>
> I enabled the jdbc logging and am attaching the log file for your reference.
>
>
>>However, if you can switch to a more enterprise-ready jdbc driver,
>>which may involve switching to a more enterprise-ready DBMS, you will
>
> avoid
>
>>many problems that will arise when you try to push access and jdbc-odbc to
>
> the
>
>>higher requirements of enterprise applications.
>
>
> In the past we have been using WLS with Oracle (WLS 6.1) but somehow for the
> present development we are stuck with the visual foxpro db due to clients
> requirement and have to deal with it. You will notice in the log file that I
> have created a connection pool for foxpro db and was successful in
> establishing connection in admin console. However, it gave the same error,
> so I switched to ms-access and linking the tables to foxpro tables.
>
> The case at hand is "jdbc:odbc:DRIVER=Microsoft Access Driver
> (*.mdb);DBQ=c:\temp\foxpro\myco.mdb" and the last transaction log lines
> shows the attempt to connect to this datasource using database control from
> workshop.
>
> Baiju.
>
> "Joe Weinstein" <joeN...@bea.com> wrote in message
> news:4097BBD4...@bea.com...
>
>>
>>baiju wrote:
>>
>>
>>>Platform : Windows XP, Weblogic Platform 8.1 sp2
>>>
>>>I get "java.sql.SQLException: Null keys not supported" exception when I
>
> try
>
>>>to connect to ms-access db (MS-ACCESS 2000) on workshop using a database
>>>control.
>>>Here are the steps I followed....
>>>I created a connection pool using sun's "sun.jdbc.odbc.JdbcOdbcDriver",
>>>url = "jdbc:odbc:DRIVER=Microsoft Access Driver
>
> (*.mdb);DBQ=c:\temp\mydb.db"
>
Joe Weinstein
the jdbc-odbc driver is in the server's boot claspath, as opposed to (or as well as)
it's standard classpath. I am filing a bug for our code that should print out a
more informative exception. In the meantime, can you do these things:
remove the jdbc-odbc bridge from where it is in the classpath, and add it
to the classpath in the same way as you added the diagnostic patch. Also, it
seems that the URL you give in the standalone case is different than the
one you give the pool. I think you should try to use the same URL, and don't
define any properties for the pool, because you don't do it in the standalone.
The only properties you need are user and password.
Joe
baiju wrote:
>>Ok. This does look like a server issue... Would you please take the
>
> attached
>
>>small diagnostic jar file, and then edit your start-weblogic script so
>
> that
>
>>this jar file comes before the weblogic.jar file in the classpath argument
>
> for
>
>>the server startup line. Then restart weblgoic and reproduce the jdbc log
>
> file
>
>>again and show it to me.
>>thanks,
>>Joe
>
>
> I did as you said and I am attaching the log file for your reference.
> Just to make sure about what you wanted, I am narrating the steps I took to
> produce this file :-
> 0) I changed the name of the jdbc log file to create a-fresh and I removed
> the foxpro datasource from the server to make things look clear.
> 1) I copied 81sp2wrapfact.jar @ %WL_HOME%\server\lib (where weblogic.jar is
> located).
> 2) I modified commEnv.cmd @ %WL_HOME%\common\bin to place the above before
> weblogic.jar
> Here is what the classpath looked like when I re-started WLS ....
> === Debugging ===
> This window is necessary for debugging code using WebLogic Workshop
> .
> .
> CLASSPATH=C:\bea\WEBLOG~1\server\lib\weblogic_knex_patch.jar;C:\bea\WEBLOG~1
> \com
> mon\lib\log4j.jar;C:\bea\WEBLOG~1\server\lib\debugging.jar;C:\bea\WEBLOG~1\s
> erve
> r\lib\knex.jar;C:\bea\WEBLOG~1\javelin\lib\javelin.jar;C:\bea\WEBLOG~1\serve
> r\li
> b\wlw-lang.jar;C:\bea\JDK141~1\lib\tools.jar;C:\bea\WEBLOG~1\server\lib\webl
> ogic
> _sp.jar;C:\bea\WEBLOG~1\server\lib\81sp2wrapfact.jar;C:\bea\WEBLOG~1\server\
> lib\
> weblogic.jar;C:\bea\WEBLOG~1\server\lib\ojdbc14.jar;C:\bea\WEBLOG~1\server\l
> ib\a
> nt\ant.jar;C:\bea\JDK141~1\jre\lib\rt.jar;;C:\bea\WEBLOG~1\common\eval\point
> base
> \lib\pbserver44.jar;C:\bea\WEBLOG~1\common\eval\pointbase\lib\pbclient44.jar
> ;C:\
> bea\WEBLOG~1\server\lib\webserviceclient.jar;C:\bea\WEBLOG~1\server\lib\webs
> ervi
> ceclient+ssl.jar;C:\bea\WEBLOG~1\server\lib\xbean.jar;C:\bea\WEBLOG~1\server
> \lib
> \wlxbean.jar;C:\bea\WEBLOG~1\server\lib\xqrl.jar;C:\bea\WEBLOG~1\server\lib\
> netu
> i\netui-compiler.jar;C:\bea\WEBLOG~1\server\lib\wli.jar;C:\bea\WEBLOG~1\serv
> er\l
> ib\fop.jar;C:\bea\WEBLOG~1\integration\adapters\sample\lib\sample-eis.jar;
> .
>
> ----------------------------------------------------------------------------
> -----
> If you will show me the standalone JDBC code you'd write to make a jdbc-odbc
> connection, and show me the way you defined the pool (it's in the config.xml
> file), I may see the problem there.
>
> Definition of the JDBC conn pool for ms-access =
> <JDBCConnectionPool DriverName="sun.jdbc.odbc.JdbcOdbcDriver"
> Name="mycoAccess"
> Properties="DefaultDir=c:;DriverId=281;user=;FIL=MS
> Access;emp=oxpro"
> Targets="cgServer" TestConnectionsOnCreate="true"
> TestConnectionsOnRelease="true" TestConnectionsOnReserve="true"
> TestTableName="myco" URL="jdbc:odbc:DRIVER=Microsoft Access Driver
> (*.mdb);DBQ=c:\temp\foxpro\myco.mdb"/>
>
> Also I would like to re-iterate that I was able to successful connection
> when I was configuring this in weblogic admin console.
>
> Standalone JDBC code by which I was able to access the data and the metadata
> info follows :-------------------
>
> -------------------------------code ends --------------------------
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
> String url = "jdbc:odbc:;DRIVER=Microsoft Access Driver
> (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeou
> t=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS
> Access;DriverId=281;DefaultDir=c:\\temp\\foxpro;DBQ=c:\\temp\\foxpro\\myco.m
> db";
> Connection con = DriverManager.getConnection(url);
> Statement statement = con.createStatement();
> ResultSet resultSet = statement.executeQuery("SELECT * FROM myco");
> ResultSetMetaData rsmd = resultSet.getMetaData();
> int numberOfColumns = rsmd.getColumnCount();
> for (int i=1; i<numberOfColumns+1; ++i) {
> System.out.println("col name " + i + "= " +
> rsmd.getColumnName(i));
> switch(rsmd.getColumnType(i))
> {
> case Types.VARCHAR : System.out.println("col name " + i + "=
> VARCHAR"); break;
> case Types.INTEGER : System.out.println("col name " + i + "=
> INTEGER"); break;
> default : System.out.println("col name " + i + "= UNAVAILABLE");
> break;
> }
> }
>
> int i=0;
> while( resultSet.next() && i++ < 10 )
> System.out.println( resultSet.getString(1));
>
> resultSet.close();
> statement.close();
> con.close();
>
> -------------------------------code ends --------------------------
>
> Hope this Helps,
> Thanks,
> Baiju.
> "Joe Weinstein" <joeN...@bea.com> wrote in message
> news:4097D09B...@bea.com...
>
>>
>>baiju wrote:
>>
>>
>>>Joe,
>>>I am attaching the latest jdbc.log00001.log as it apparently took some
>
> time
>
>>>to write the complete error logs.
>>>
>>>Baiju.
>>
>>
>>>"Joe Weinstein" <joeN...@bea.com> wrote in message
>>>news:4097BBD4...@bea.com...
>>>
>>>
baiju
---- you wrote ---------------
Also, it
seems that the URL you give in the standalone case is different than the
one you give the pool. I think you should try to use the same URL, and don't
define any properties for the pool, because you don't do it in the
standalone.
The only properties you need are user and password.
----------------------------------------------------------
They are different, as in the standalone case I wanted most of the
parameters to take effect, however while switching to creation of the pool
most of them were understandably removed and also couple of them
"DefaultDir=c:;emp=oxpro" were added by the admin console after re-start.
I have removed all of the properties parameters.
Will this patch be available in a future release of a service pack, and if
so when is the tentative date for it ?
Thanks for your help,
Baiju.
"Joe Weinstein" <joeN...@bea.com> wrote in message
news:4097FCCF...@bea.com...
> Hi. I just found out that this is a recently-fixed bug. Here is
> the official temporary patch, which you should get to the front of the
> server classpath as the others. This is the only patch you need for this
> issue, and you can remove my diagnostic one.
> > Properties="DefaultDir=c:;DriverId=281;user=;FIL=MS
> > Access;emp=oxpro"
> > Targets="cgServer" TestConnectionsOnCreate="true"
> > TestConnectionsOnRelease="true" TestConnectionsOnReserve="true"
> > TestTableName="myco" URL="jdbc:odbc:DRIVER=Microsoft Access
Driver
> > (*.mdb);DBQ=c:\temp\foxpro\myco.mdb"/>
> >
> > Also I would like to re-iterate that I was able to successful connection
> > when I was configuring this in weblogic admin console.
> >
> > Standalone JDBC code by which I was able to access the data and the
metadata
> > info follows :-------------------
> >
> > -------------------------------code ends --------------------------
> > Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Joe Weinstein
baiju wrote:
> It finally seems to be working well ..phew ...
>
> ---- you wrote ---------------
> Also, it
> seems that the URL you give in the standalone case is different than the
> one you give the pool. I think you should try to use the same URL, and don't
> define any properties for the pool, because you don't do it in the
> standalone.
> The only properties you need are user and password.
> ----------------------------------------------------------
> They are different, as in the standalone case I wanted most of the
> parameters to take effect, however while switching to creation of the pool
> most of them were understandably removed and also couple of them
> "DefaultDir=c:;emp=oxpro" were added by the admin console after re-start.
> I have removed all of the properties parameters.
>
> Will this patch be available in a future release of a service pack, and if
> so when is the tentative date for it
> Thanks for your help,
> Baiju.
Yes,
this fix will be in the next version of 8.1, 8.1sp3, which is going to be
available soon. You'd have to ask support for an official estimate.
Joe