DSN name not found error with TOMCAT5.5.7

[Preity crazy]

Hi Friends,

I am using Tomcat5.5.7 version and oracle as my back end connectivity.And my jdk is jdk1.5. Everything is fine till servlets. But when iam running servlets in tomcat with JDBC i got the following error:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

These are the JDBC connectivity statements i used:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc dbc:S2Tech","scott","tiger");
Statement stmt = con.createStatement();

My DSN is configured as S2Tech.I dont know why i am getting this error. Should i do any modifications in server.xml.Could someone please help me in this issue.

Thanks in advance,
Preity

PS:When i ran same program in webserver it's working fine.

[sandip mense]

Preity,

Check out the documentation of tomcat on creating the DSN. U can find it
over here.
Also do not forget to place the classes12.jar file into your tomcat lib.

Sandip

[Preity crazy]

Hi Sandip,

Thanx for the quick reply. Actually i went thru that document before itself,but i got confused with JNDI.Please tell me if my understanding is correct:

Should i modify server.Xml in the following place:

<GlobalNamingResources>
- <!-- Test entry for demonstration purposes
-->
<Environment name="simpleValue" type="java.lang.Integer" value="30" />
- <!--
Editable user database that can also be used by
UserDatabaseRealm to authenticate users

-->
<Resource name="UserDatabase" auth="Container" type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" />
</GlobalNamingResources

I am new to this tomcat server.So can you please explain me in detail sandip.

And there are so many lib folders in Tomcat ,where exactly i should keep classes12.jar file

[Rajendar Goud]

Preeti,
For database connections in tomcat,you had to place the jar files in TOMCAT_HOME/common/lib folder.

If you are making use of database connection pooling provided by tomcat ,here is an example

<GlobalNamingResources>
<Environment value="30" type="java.lang.Integer" override="true" name="simpleValue"/>
<Resource type="org.apache.catalina.UserDatabase" scope="Shareable" description="User database that can be updated and saved" auth="Container" name="UserDatabase"/>
<Resource type="javax.sql.DataSource" scope="Shareable" auth="Container" name="jdbc/db2XXX"/>
<ResourceParams name="UserDatabase">
<parameter>
<name>factory</name>
<value>org.apache.catalina.users.MemoryUserDatabaseFactory</value>
</parameter>
<parameter>
<name>pathname</name>
<value>conf/tomcat-users.xml</value>
</parameter>
</ResourceParams>
<ResourceParams name="jdbc/db2XXX">
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>password</name>
<value>xxxx</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc b2://T00007.xxx.eur.xx.com:7777/T00007</value>;
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.ibm.db2.jcc.DB2Driver</value>
</parameter>
<parameter>
<name>initialSize</name>
<value>0</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>300</value>
</parameter>
<parameter>
<name>username</name>
<value>yyyy</value>
</parameter>
</ResourceParams>
</GlobalNamingResources>

The above is provided globally in server.xml(ie can be used by different contexts) and if you need to use these database connections for your context,then you had to create a ResourceLink and append it to your context.
Or if you want to have the Jndi look up only for ur context then can add the following to your context.
<Resource type="javax.sql.DataSource" scope="Shareable" auth="Container" name="jdbc/db2XXX"/>
<ResourceParams name="UserDatabase">
<parameter>
<name>factory</name>
<value>org.apache.catalina.users.MemoryUserDatabaseFactory</value>
</parameter>
<parameter>
<name>pathname</name>
<value>conf/tomcat-users.xml</value>
</parameter>
</ResourceParams>
<ResourceParams name="jdbc/db2XXX">
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>password</name>
<value>xxxx</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc b2://T00007.xxx.yyy.xx.com:7777/T00007</value>;
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.ibm.db2.jcc.DB2Driver</value>
</parameter>
<parameter>
<name>initialSize</name>
<value>0</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>300</value>
</parameter>
<parameter>
<name>username</name>
<value>yyyy</value>
</parameter>
</ResourceParams>


Hope this will be of some help to you.

Raj

[Preity crazy]

Thanks Rajendar.

So i added classes12.jar file in tomcat common/lib.
Then i added the following code to the context:::

<Resource name="jdbc/S2Tech" auth="Container" type="javax.sql.DataSource" username="scott" password="tiger" driverClassName="org.hsql.jdbcDriver" url="jdbc:HypersonicSQL atabase"
maxActive="8" maxIdle="4"/>

S2Tech is my DSN name,but i am not sure about the driverclassname and URL.I have to check that.ours is not hypersonic Sql

Will this be enough rajendar or i need to do something else also.And in the servlet program instead of the below code i think i need to change my code right!

[CODE}
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc :-o dbc:S2Tech","scott","tiger");
Statement stmt = con.createStatement();
[/CODE]

[Rahul Singh Khokhar]

Hi Preity,
Pls make sure about the compatiblity of classes12 with Jdk1.5

I believe you should not be using classes12.jar/zip with jdk1.4 and above. Try using ojdbc14.jar/zip instead.

And yes, the class goes in Jakarta_Home_directory/common/lib

regards,
Rahul..

[sandip mense]

Preity,

Which is the dataabse that you are using? Also if you are using the tomcat resource for connection, you need not make the connection again explicitly, rather just get the conenction object from teh context and start with it.
If you let me know the data base you are unsing, I would give you the excat syntax of configuring the xml .

Sandip

[Preity crazy]

Hi Sandip,

My database is oracle 9i client.I am using sun.jdbc.odbc driver.I have a dsn configured thru control panel. Still it is saying DSn name not found.

Thanks in advance.

[sandip mense]

Preity,
There are two ways you can have the connection to the database,
One you do the insert all the details of the database in your code:
The below code returns you a connection object which you can use it.

   private Connection getConnection()
 {
String dbDriver = "oracle.jdbc.OracleDriver";
String dbURL = "192.168.1.1:1521:S2Tech"; // ip address of the system that has database server installed.
// S2Tech is the oracle instance name.

String dbUserName ="scott" ;
String dbPassword = "tiger";
try{
 Class.forName(dbDriver);
 conn = DriverManager.getConnection("jdbc  :-o	  racle:thin:@"+ dbURL, dbUserName , dbPassword );
 return conn;
}catch(SQLException sql)
{
System.out.println("SQLEXCEPTION: "+ sql);
return conn;
}
 }
 

The above way of doing a connection is more a rudimentary and only can be recommended for starters.
By doing so u stick to one database. If you need to change teh database, you need to change the code and recomplie it.


The second way is using the JNDI way, i.e the server does all work fo connection and you just get the reference.

The InitialContext is configured as a web application is initially deployed, and is made available to web application components (for read-only access). All configured entries and resources are placed in the java:comp/env portion of the JNDI namespace, so a typical access to a resource - in this case, to a JDBC DataSource - would look something like this

 
 public Connection getConnection(){
 try {
 Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");

// Look up our data source
DataSource ds = (DataSource)
  envCtx.lookup("jdbc/DevelopmentDS");

// Allocate and use a connection from the pool
return ds.getConnection();
 
 
     }catch(Exception exe){
System.out.println ("The exception is " + exe);
return null;
}
 
 
 }
 

where java:/DevelopmentDS is the Datasource Name configured in your xml file.

The following configuration need to be done in your erver.xml file

 
 <Context ...>
   ...
   <Resource name="jdbc/EmployeeDB" auth="Container"
             type="javax.sql.DataSource" username="scott" password="tiger"
             driverClassName="oracle.jdbc.OracleDriver" url="jdbc  :-o	  racle:thin:@192.168.1.1:1521:S2Tech"
             />
   ...
 </Context>
 

Then modify the applications web.xml file to define the JNDI name that you have referred in the code:
web.xml file:

 
 <resource-ref>
  <description>
    Resource reference.
  </description>
  <res-ref-name>
    jdbc/DevelopmentDS
  </res-ref-name>
  <res-type>
    javax.sql.DataSource
  </res-type>
  <res-auth>
    Container
  </res-auth>
</resource-ref>

And then that should be done.

I would suggested you go by the first way of hard coding in the java file and check the connectivity.
[ February 23, 2005: Message edited by: sandip mense ]

[Preity crazy]

Thankyou Sandip,

I already used the first way you suggested but in different way.It didn't work.Just see the code :

 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc :-o	 dbc:S2Tech","scott","tiger");

So now i am trying with the second way which you have given.I will let you know the result.

[Rahul Singh Khokhar]

Hi Preity,
Do the following and it should work
1. Use the correct machine name,or IP address or DSN where your oracle is running
2. Use the correct SID for Oracle
3. Use the correct port number. The default port for listener is 1521
4. Use OJDBC14 instead of classes12 (classes12 dont work for JDK1.5 & Oracle 9i)

5. Once u are done with the above, try this code:

Connection conn=null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection("jdbc racle:thin:@DNSNAME:1521:SIDNAME", "username", "password");

conn.setAutoCommit(false);
if ( conn != null ) {

// Whatever
// your code here

}

} catch (Exception e) {
out.println("ERROR ERROR !!");
System.err.println(e.getMessage());
}
finally {
try {
if ( conn != null ) {
// Close the connection no matter what
conn.close();
}
}catch (SQLException sqle) {
System.err.println(sqle.getMessage());
}
}



regards,

Rahul..

[Rajendar Goud]

Preity
Whats the exception you are facing when tried with the first method?
by the way are you able to access the database instance s2Tech using sql window with the username/password as scott/tiger ?


Raj

[Preity crazy]

Hi friends,

Still i couldn't solve my problem.Now my error message is changed to:

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

Rahul-What is that you are saying about OJDBC14.I couldn't find it.Can you please tell me where it is.

Rajendar-For the first method i got the following message:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

But after doing the following changes i got the below message:

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

Sandip- I followed your second method.But still getiing error as i mentioned. :-(


Friends,

I have JDK1.5,Tomcat5.5.7,Oracle 9i and on windows 2000. Can someone solve my problem please.Actually i can run my programs without any changes on webserver.But I need the same on tomcat.So please i require all your help.

I did the following things.Correct me if i did any mistake anywhere.

1.I copied classes12.jar file into TOMCAT_HOME/commom/lib folder

2.I configured my DSN as "S2Tech" thru control panel

3.I included the following in my TOMCAT_HOME/conf/server.xml

<Context>
<Resource name="jdbc/S2Tech" auth="Container"
type="javax.sql.DataSource" description="User database that can be updated and saved"
username="scott" password="tiger" driverClassName="oracle.jdbc.OracleDriver" url="jdbc racle:thin@localhost:1521:S2Tech" />
</Context>

4.I included the following in my TOMCAT_HOME/webapps/servlets-examples/WEB-INF/web.XML

<resource-ref>
<description> Resource Reference </description>
<res-ref-name> jdbc/S2Tech </res-ref-name>
<res-type> javax.sql.DataSource </res-type>
<res-auth> Container </res-auth>
</resource-ref>


5.The following code i used in my servlet program:

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");

// Look up our data source
DataSource ds = (DataSource) envCtx.lookup("jdbc/S2Tech");

// Allocate and use a connection from the pool
Connection con = ds.getConnection("scott","tiger");


Thanks a lot for all your help in advance.

[Rajendar Goud]

Priety,
Its leading you into confusion.
Better you get it running using the first method suggested by sandip.

Also
As you said that you are using jdk1.5. I hadnt worked on jdk1.5 and so not aware of the driver it supports. but as rahul suggested, please add that driver instead of classes12.zip. You can download driver related to oracle from oracle site. www.oracle.com\downlaod

For the first method u faced the problem like DSN not found & no default driver specified.

Its because the driver is not being supported and also the DSN you created is not correct.
Are you sure about the SID and the port number used by your oracle database. This SID may contain different port or username/password.

conn = DriverManager.getConnection("jdbc racle:thin:@DNSNAME:1521:SIDNAME", "username", "password");

please verify those values and i suggest you to get it working with the first method first ,as the second method is leading you into confusion.so leave it aside for sometime.



Raj

[Rahul Singh Khokhar]

Hi Preity,
I'll simplify things for you:

* Infrastructure required:
1. Oracle database
2. Servlet container (e.g. Tomcat)
3. JDBC driver (OJDBC14.jar which can be downloaded from OTN.com. put the driver in tomcatDirectory/common/lib/)

* Oracle database sitting on a PC can be accessed by your JDBC calls using the following information:
1. PC name or IP address
2. SID name
3. port number

Once u are sure of the above, proceed with the code I posted in my last response. U r definitely missing something from the above.

Also, as Rajender point out: 'are you able to access the database instance s2Tech using sql plus window with the username/password as scott/tiger?' If the answer is Yes, then it means your DataBase is up & running and the username-password combo is correct, in which case try using your PC name(or localhost if its on your local PC) instead of the DNS u created in the control panel.

Give it a try .. Good Luck!

regards,
Rahul..

[Preity crazy]

Thanx a lot for all your co-operation friends. Now i found the problem finally.I have a thin driver for JDk1.2 and JDK 1.3 which is not compatible with JDK1.5.. So it seems because of this after all the modifications iam getting Driver not found.

Can anyone of you help me in getting Oracle thin driver for JDK1.5.Pleaseeeeeeee

Thanx in advance:-)

[sandip mense]

Preity,

If you feel driver is the problem, download the latest drivers for 9i from here .
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc9201.html

Sandip

[Preity crazy]

Hi Frenz,

Sorry for troubling you all.What to do,No other way for me.Inspite of my hard trials for Tomcat5.5.7 i couldn't get the desired results.

My DSN,driver etc are working fine with normal JDBC programs,Web server but i dunno y its not working with Tomcat alone.

I am using the following 3 statements with the normal JDBC programs(not servlets)

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection("jdbc dbc:S2Tech","scott","tiger");

Statement stmt = con.createStatement();

I tried even with jndi lookup as i mentioned in my earlier posts.Then i thought may be driver is the problem. I downloaded OJDBC14.jar and tried again. But of no use. Now i feel like uninstalling Tomcat 5.5.7 and go for Tomcat4.x. But before going for that can sumone find or fix my problem.

I will be very thankful to you all. Thanks in advance.