You are viewing a plain text version of this content. The canonical link for it is here.
Posted to java-user@axis.apache.org by Martin Gülich <ma...@foi.se> on 2005/05/27 10:21:11 UTC
Cannot query database from web service.
Hello!
I have a tricky problem that I cannot solve on my own. I want to use JDBC
from within my web service implementation to query an Access 2000 database
via ODBC (System DSN) and return the answer. That is, the java method
implementing the web service uses JDBC. For some reason this doesn't work.
The program containing the method runs just fine when executed directly from
the command prompt, but when it is invoked by calling the web service from
an Axis client I get a createSQLException with a trace back to
Method.invoke() among others (see below). I have tried many things and it
seems to me that my database cannot be queried from within the Axis-process
that is executing the web service. Why is that?
Have anybody else encountered a similar problem, and how did you solve it?
Please help!
PS. How can I open a text file from within a web service and use it? The
default path/user.dir property is c:\winnt\system32 from the perspective of
the running web service so the text file is never found. I have tried to set
the user.dir property to directory ..\axis\WEB-INF\classes without success.
DS.
Here is the code implementing my web service:
public synchronized String getCarsByOwner(String ownerSSN) {
String sqlResult;
try {
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:carsdatabase";
// Load driver and connect to database
Class.forName(driver).newInstance();
Connection connection =
DriverManager.getConnection(dbURL);
PreparedStatement stmnt =
connection.prepareStatement("SELECT * "+
"FROM Cars "+
"WHERE Owner=3D"+ownerSSN);
ResultSet res = stmnt.executeQuery();
sqlResult = convertQueryResultToString();
} catch(Exception e) {
StackTraceElement[] ste = e.getStackTrace();
StringBuffer buf = new StringBuffer();
for(int i=0; i<ste.length; i++) {
buf.append(ste[i].toString());
buf.append(endl);
}
sqlResult = buf.toString();
}
return sqlResult;
}
And here is the error received:
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3074)
sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
java.sql.DriverManager.getConnection(DriverManager.java:512)
java.sql.DriverManager.getConnection(DriverManager.java:193)
datasources.cars.CarsDBClient$1.run(CarsDBClient.java:117)
datasources.cars.CarsDBClient.getCarsByOwner(CarsDBClient.java:138)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java
:39
)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorI
mpl.java:25)
java.lang.reflect.Method.invoke(Method.java:324)
org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397
)
org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.jav
a:304)
org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:329)
org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:
71)
org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:157)
org.apache.axis.SimpleChain.invoke(SimpleChain.java:122)
org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:517)
org.apache.axis.server.AxisServer.invoke(AxisServer.java:324)
org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:639)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:
339)
........
Best regards
Martin
SV: Cannot query database from web service.
Posted by Martin Gülich <ma...@foi.se>.
Hi!
Thank you for your answer, but I am afraid I have already done most of that.
I am using Windows ;) and the ODBC is defined both as a System DSN, and as a
User DSN. The User DSN is used when I execute the java-program containing
the WS-method directly, and the System DSN when I execute the WS via a
SOAP-klient. Also, I dont understand what you mean with mapped drives. The
only reference to the database is through the JDBC connection url:
jdbc:odbc:carsdatabase. I will try MySQL instead of Access as suggested
and see if it helps.
Thank you! If you or anyone else comes up with new ideas, please dont
hesitate to mail me.
Best regards
Martin
-----Ursprungligt meddelande-----
Från: Don Albertson [mailto:don.albertson@gmail.com]
Skickat: den 27 maj 2005 14:15
Till: axis-user@ws.apache.org
Ämne: Re: Cannot query database from web service.
I can't say for sure that this is your problem and if my assumption that
your service is running on Windows is wrong then it almost certainly isn't
but....
I have had problems with ODBC connections when they are not System
connections. If the ODBC connection is defined at user level, the web
service may not be able to find it.
Another oddity I have encountered has to do with mapped drives. If the
Access db is referenced as a mapped drive and the web service container is
running as a service, it may not be able to reference the mapped drive. In
this case I have used the complete \\server_name\... method of referencing
the drive with the db on it successfully. As a test you might try replacing
the Access db with MySql, MSDE or other db engine just to see if the service
can connect there successfully.
On 5/27/05, Martin Gülich <ma...@foi.se> wrote:
Hello!
I have a tricky problem that I cannot solve on my own. I want to use JDBC
from within my web service implementation to query an Access 2000 database
via ODBC (System DSN) and return the answer. That is, the java method
implementing the web service uses JDBC. For some reason this doesn't work.
The program containing the method runs just fine when executed directly from
the command prompt, but when it is invoked by calling the web service from
an Axis client I get a createSQLException with a trace back to
Method.invoke() among others (see below). I have tried many things and it
seems to me that my database cannot be queried from within the Axis-process
that is executing the web service. Why is that?
Have anybody else encountered a similar problem, and how did you solve it?
Please help!
PS. How can I open a text file from within a web service and use it? The
default path/user.dir property is c:\winnt\system32 from the perspective of
the running web service so the text file is never found. I have tried to set
the user.dir property to directory ..\axis\WEB-INF\classes without success.
DS.
Here is the code implementing my web service:
public synchronized String getCarsByOwner(String ownerSSN) {
String sqlResult;
try {
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:carsdatabase";
// Load driver and connect to database
Class.forName(driver).newInstance();
Connection connection =
DriverManager.getConnection(dbURL);
PreparedStatement stmnt =
connection.prepareStatement("SELECT * "+
"FROM Cars "+
"WHERE Owner=3D"+ownerSSN);
ResultSet res = stmnt.executeQuery();
sqlResult = convertQueryResultToString();
} catch(Exception e) {
StackTraceElement[] ste = e.getStackTrace();
StringBuffer buf = new StringBuffer();
for(int i=0; i<ste.length; i++) {
buf.append(ste[i].toString());
buf.append(endl);
}
sqlResult = buf.toString();
}
return sqlResult;
}
And here is the error received:
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect (JdbcOdbc.java:3074)
sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
java.sql.DriverManager.getConnection(DriverManager.java :512)
java.sql.DriverManager.getConnection(DriverManager.java:193)
datasources.cars.CarsDBClient$1.run(CarsDBClient.java:117)
datasources.cars.CarsDBClient.getCarsByOwner(CarsDBClient.java:138)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java
:39
)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorI
mpl.java:25)
java.lang.reflect.Method.invoke(Method.java:324)
org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397
)
org.apache.axis.providers.java.RPCProvider.processMessage (RPCProvider.jav
a:304)
org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:329)
org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:
71)
org.apache.axis.SimpleChain.doVisiting (SimpleChain.java:157)
org.apache.axis.SimpleChain.invoke(SimpleChain.java:122)
org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:517)
org.apache.axis.server.AxisServer.invoke(AxisServer.java :324)
org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:639)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java
:
339)
........
Best regards
Martin
--
It used to be that machines automated work, giving us more time to do other
things. But now machines automate the production of attention-consuming
information, which takes our time. For example, if one person sends the
same e-mail message to 10 people, then 10 people have to respond.
Esther Dyson
Re: Cannot query database from web service.
Posted by Don Albertson <do...@gmail.com>.
I can't say for sure that this is your problem and if my assumption that
your service is running on Windows is wrong then it almost certainly isn't
but....
I have had problems with ODBC connections when they are not System
connections. If the ODBC connection is defined at user level, the web
service may not be able to find it.
Another oddity I have encountered has to do with mapped drives. If the
Access db is referenced as a mapped drive and the web service container is
running as a service, it may not be able to reference the mapped drive. In
this case I have used the complete \\server_name\... method of referencing
the drive with the db on it successfully. As a test you might try replacing
the Access db with MySql, MSDE or other db engine just to see if the service
can connect there successfully.
On 5/27/05, Martin Gülich <ma...@foi.se> wrote:
>
> Hello!
>
> I have a tricky problem that I cannot solve on my own. I want to use JDBC
> from within my web service implementation to query an Access 2000 database
> via ODBC (System DSN) and return the answer. That is, the java method
> implementing the web service uses JDBC. For some reason this doesn't work.
>
> The program containing the method runs just fine when executed directly
> from
> the command prompt, but when it is invoked by calling the web service from
> an Axis client I get a createSQLException with a trace back to
> Method.invoke() among others (see below). I have tried many things and it
> seems to me that my database cannot be queried from within the
> Axis-process
> that is executing the web service. Why is that?
>
> Have anybody else encountered a similar problem, and how did you solve it?
> Please help!
>
> PS. How can I open a text file from within a web service and use it? The
> default path/user.dir property is c:\winnt\system32 from the perspective
> of
> the running web service so the text file is never found. I have tried to
> set
> the user.dir property to directory ..\axis\WEB-INF\classes without
> success.
> DS.
>
>
> Here is the code implementing my web service:
>
> public synchronized String getCarsByOwner(String ownerSSN) {
>
> String sqlResult;
>
> try {
>
> String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
>
> String dbURL = "jdbc:odbc:carsdatabase";
>
> // Load driver and connect to database
>
> Class.forName(driver).newInstance();
>
> Connection connection =
> DriverManager.getConnection(dbURL);
>
>
> PreparedStatement stmnt =
> connection.prepareStatement("SELECT * "+
> "FROM Cars "+
> "WHERE Owner=3D"+ownerSSN);
>
> ResultSet res = stmnt.executeQuery();
>
> sqlResult = convertQueryResultToString();
>
> } catch(Exception e) {
>
> StackTraceElement[] ste = e.getStackTrace();
>
> StringBuffer buf = new StringBuffer();
>
> for(int i=0; i<ste.length; i++) {
>
> buf.append(ste[i].toString());
> buf.append(endl);
> }
>
> sqlResult = buf.toString();
> }
> return sqlResult;
> }
>
> And here is the error received:
>
> sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
>
> sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
>
> sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3074)
>
> sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
>
> sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
>
> java.sql.DriverManager.getConnection(DriverManager.java:512)
>
> java.sql.DriverManager.getConnection(DriverManager.java:193)
>
> datasources.cars.CarsDBClient$1.run(CarsDBClient.java:117)
>
> datasources.cars.CarsDBClient.getCarsByOwner(CarsDBClient.java:138)
>
> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java
> :39
> )
>
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorI
> mpl.java:25)
>
> java.lang.reflect.Method.invoke(Method.java:324)
>
> org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java
> :397
> )
>
> org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.jav
> a:304)
>
> org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:329)
>
> org.apache.axis.strategies.InvocationStrategy.visit(
> InvocationStrategy.java:
> 71)
>
> org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:157)
>
> org.apache.axis.SimpleChain.invoke(SimpleChain.java:122)
>
> org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:517)
>
> org.apache.axis.server.AxisServer.invoke(AxisServer.java:324)
>
> org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:639)
>
> javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
>
> org.apache.axis.transport.http.AxisServletBase.service(
> AxisServletBase.java:
> 339)
> ........
>
>
> Best regards
>
> Martin
>
>
>
>
--
It used to be that machines automated work, giving us more time to do other
things. But now machines automate the production of attention-consuming
information, which takes our time. For example, if one person sends the same
e-mail message to 10 people, then 10 people have to respond.
Esther Dyson