JDBC and SQLServer question

[wduty]

I am having trouble reading from an SQL Server database in a servlet. I get the following exception message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Categories'.

The table exists and I can connect to it OK from an asp page. I usually use the following textbook code to connect to Access databases and have had no problems. Is there something else in JDBC that is required for SQL?

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"
Connection conn = DriverManager.getConnection("jdbcdbc:testdb"
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Categories"

--Will Duty
wduty@radicalfringe.com

 

[palbano]

Dear Will,

First is your DSN 'testdb' assigned to start in the catalog that contains the table 'Categories'?

Next check out the Knowledgebase article #ID: Q179854
on msdn.microsoft.com... here is an excerpt:

In general, the package name has changed to com.ms.jdbc.odbc with Internet Explorer 4.0x and SDK for Java 2.0 or later virtual machine. For example the following lines should be changed:


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"
or

Class.forName("com.ms.sql.CDriver"
to:

Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver"

Good luck
-pete

 

[wduty]

Thanks Pete,

Yeah it looks like it was a DSN problem although I don't see the term catalog appearing anywhere in the DSN configuration setup.

There's a dropdown list I didn't notice in the ODBC configuration windows which allows you to set the "default database". It is automatically set to "master". Changing it to "testdb" fixed the problem. I was also able to solve the problem by creating a new login account in SQL Server with testdb as the default and using that login in the servlet.


There is some info in the Weblogic docs regarding a driver of theirs called JDriver which seems to plug into their connection pooling mechanism. Their example driver code is:

Driver myDriver = (Driver)Class.forName("weblogic.jdbc.mssqlserver4.Driver&quot.newInstance();

But I haven't tried it.

--Will Duty
wduty@radicalfringe.com

 

[carlosv]

I tried using that weblogic stuff but it's not working for me!
Can you please put in the correct code for the url, ie:

Connection c = DriverManager.getConnection(?????????);

I am having a heck of a time trying to connect to my MS Sql Server with a Java servlet.

Do I need a dsn on the server or the client?
Do I have to connect to the dsn on the server rather that the database itself?
Can I have to the ms driver or can i use the javadbc driver?

Please HEEEEEELLLP!!!!!

 

[wushutwist]

I would be easier if you included the version of WebLogic you are using.

Also I would suggest setting up a Connection Pool in WebLogic and handling your Connections from there. No, you don't need a DSN to use WebLogic's SQL driver. You could use the JDBC-ODBC bridge (in which case you would need a DSN) but it is extremely slow and definitely not recommended.

Also I would suggest if you have anymore questions about WebLogic, that you move them to the J2EE forum. You are more likely to get a better response. Wushutwist

 

[carlosv]

thank you for the response. I guess the question was really: How to connect to my database. I only used the weblogic driver because I thought I had to have it. My problem was that I couldn't connect to my MS SQL Server database *at all*! Well, as it turns out, I was able to connect once I realized a few things:
In my case,
1) had to have a dsn on the server.
2) that dsn had to be set up to connect to the sql database on the local machine. (MS SQL Server 6.5, Apache/Tomcat, and Servlets are all on the same machine).
3) the code syntax to load the driver is: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver&quot.newInstance();
4) the code syntax to get the connection is: c = DriverManager.getConnection("jdbcdbc:[dsnName]", "[usename]", "[password]" where dsnName is the name of your datasource, username is a valid user with access to the database/table, and password is the password for that user.

Why is it that there seems to be so many questions and so few answers?

 

[Guest_imported]

anyone have trouble getting connections through jdbc-odbc driver using jndi?