Solved

Connecting SQL SERVER 6.5 with JAVA!!

Posted on 2004-04-05
37
1,711 Views
Last Modified: 2013-11-23
Hi Guys,

I am trying to connect to a SQL SERVER 6.5 DB using java. CAn you please suggest whether we can achieve this by using the ODBC driver or would I have to use a third party JDBC Driver. I believe JTDS Driver is a good one for connecting to SQL SERVER 6.5. Has anybody used to before?? If yes can you please give me the code for all the steps for using JTDS driver in a java Programme and where exactly to include the jar file path in the java code.

If not, then has anybody has had any experience connecting SQL SERVER 6.5 with ODBC Driver and could give me the code, please.

Thanks for your quick reply!!

PCMENIAC
0
Comment
Question by:PCMENIAC
  • 18
  • 12
  • 7
37 Comments
 
LVL 92

Expert Comment

by:objects
Comment Utility
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
0
 

Author Comment

by:PCMENIAC
Comment Utility
Objects,

Both Bea WebLogic and DataDirect Drivers are not freely available. Thats why I was looking into integrating JTDS into my application as it supposed to be fast, reliable and stable. Can you please suggest on how to use Jtds??

Thanks,

PC MENIAC
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
Sorry didn't realise you were after a free one specifically.
Not much needed to use thejtds driver, just drop the jar in your 'ext' directory and away you go.
Heres some background with jdbc if you need it:
http://java.sun.com/docs/books/tutorial/jdbc/index.html
0
 

Author Comment

by:PCMENIAC
Comment Utility
Hello Objects,

Sorry, for not mentioning it before that I was after a freebei.
Anyways, I still am not able to compile my program and I think the reason is that I am not doing something right with the "jtds-0.8-rc1.jar" file. CAn you please help me in this matter.

I have this jar file in the same folder as my Java program but I think I need to specify its full path somewhere in my java program. CAn you tell me where to set its class path. I'll really appreciate if you can give me a piece of code as well to connect to SQL SERVER 6.5 using JTDS driver.

Thanks!!

Regards,

PCMENIAC

0
 
LVL 92

Expert Comment

by:objects
Comment Utility
Easiest it to put the jar file in the 'ext' directory of your jdk:
<jdk>/jre/lib/ext
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
the connection strinbg for jtds will look like:
jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]
0
 
LVL 92

Expert Comment

by:objects
Comment Utility


    Connection connection = null;
    try {
        // Load the JDBC driver
        String driverName = "net.sourceforge.jtds.jdbc.Driver";
        Class.forName(driverName);
   
        // Create a connection to the database
        String serverName = "localhost";
        String mydatabase = "mydatabase";
        String url = "jdbc:jtds://" + serverName +  "/" + mydatabase; // a JDBC url
        String username = "username";
        String password = "password";
        connection = DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
        // Could not find the database driver
    } catch (SQLException e) {
        // Could not connect to the database
    }

0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
>> I still am not able to compile my program
windows:
javac -classpath %classpath%;jtds-0.8-rc1.jar YourProgram.java

unix:
javac -classpath $CLASSPATH:jtds-0.8-rc1.jar YourProgram.java
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
Easier to put the jar in your 'ext' directory as I mentioned earlier. That way you don't need to add it to your classpath.

0
 

Author Comment

by:PCMENIAC
Comment Utility
Hello Objects,

I completely agree with you and that was the sort of help/answer I was looking for, but even after placing the jar file in the ext directory, I still have the following message:
"Exception in thread "main" java.lang.NoClassDefFoundError".

Can you please suggest something??

Also, do you know what the Connection URL would be if the SQL Server6.5 is located on my local machine?? Would we be using localhost:1433 as our server name?? And what if it resides on a server (server name = newct04).

Thanks very much for answering both parts!!

Regards,

PCMENIAC
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
>> but even after placing the jar file in the ext directory,
there are multiple ext dirs. I complete disagree on using ext dir. you'll have a lot problem in future if you use that. just use what I showed you to compile, to see if it works firts. once you get it works, we can easily simplify your compile process further.

>> Would we be using localhost:1433
yes.

>> And what if it resides on a server (server name = newct04).
newct04:1433
make sure you can ping newct04 from command line.

above assumes your sql server setup to listen on the default port 1443.
0
 

Author Comment

by:PCMENIAC
Comment Utility
Hello Kennethxu, Objects

I am able to compile my program, it when I am executing the program that I get the above mentioned error.

I even tried executing my program by: java -cp x:/paragchauhan/java/jtds-0.8-rc1.jar connectdb, but still the same problem.

It does'nt look like that its even entering the program and try to establish a connection.

Help Please, so we can start rewarding!!

KENNETHXU,

Yes, I can ping the server from cmd. And Thanks for letting me know that difference in URL for accessing SQL SERVER 6.5 locally or from a server.

Regards,

PCMENIAC
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
asumming in windows and x is the drive letter. try this:
java -cp x:/paragchauhan/java/jtds-0.8-rc1.jar;. connectdb
if it is unix(linux), try this:
java -cp x:/paragchauhan/java/jtds-0.8-rc1.jar:. connectdb

if you still have problem, then
1. is connectdb the class name? FYI, class name is case sensitive.
2. did you put your class in any package? if you do then you need to do something diff. let me know.
3. is your class public? it must be public.

0
 

Author Comment

by:PCMENIAC
Comment Utility
Kennethxu,

X is the drive and X:/parag chauhan/java/ is where the jar file and my java program are stored.

>> is connectdb the class name?
ConnectDB.java is the name of my java program which I have compiled. (Assuming, that after compiling,  a class file with the same name gets created.)

>> Did you put your class in any package?
NO, my class is not in any package. I have just written a java program in the above mentioned folder and compiled it.

>> 3. is your class public? it must be public.

Yes,  it is public. (I assume u mean the main class in the java program.)

Let me know if there is any other information that I can provide you to facilitate things.

Thanks a lot for your help and advice!!

PCMENAIC
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
can you try this in sequence and exact case. **java is very case sensitive**
x:
cd "\parag chauhan\java\"
javac -classpath %classpath%;jtds-0.8-rc1.jar ConnectDB.java
java -cp %classpath%;jtds-0.8-rc1.jar;. ConnectDB

also, make sure this is you class definition:

public class ConnectDB {

again **case sensitive**

let me know.
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
>  it when I am executing the program that I get the above mentioned error.

Don't bother stuffing around with classpaths in this case, put your jar also in your jre's 'ext' directory (probably under Program File/Java/...).

>  Would we be using localhost:1433 as our server name??

No remove the port part, unless you are using a non standard port.
localhost would be used if database was running on same machine.

> And what if it resides on a server (server name = newct04).

then use that server name:
String serverName = "newct04";
String url = "jdbc:jtds://" + serverName +  "/" + mydatabase; // a JDBC url
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
>> No remove the port part, unless you are using a non standard port.
why remove the port part. the port part works no problem at all!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 92

Expert Comment

by:objects
Comment Utility
Because it is unnecessary.
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
unnecessary step of doing a remove from existing code?
0
 

Author Comment

by:PCMENIAC
Comment Utility
HELLO OBJECTS, KENNETHXU,

Just a quick note on your code:

        // Create a connection to the database
        String serverName = "localhost";
        String mydatabase = "mydatabase";
        String url = "jdbc:jtds://" + serverName +  "/" + mydatabase; // a JDBC url
        String username = "username";
        String password = "password";
        connection = DriverManager.getConnection(url, username, password);

AS yousaid earlier that the connection string for jtds looks like: jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

So, we have to specifically define in property about the TDS version we intend to use Bcos the default value is 7.0 (valid only for SQL SERVER7 and 2000) but it is 4.2 for SQL SERVER 6.5.
So, the URL would be something like:
conn = DriverManager.getConnection("jdbc:jtds:sqlserver://SRVSUND32:1433/ATC;TDS=4.2;User=uname;Password=pwd");

ATC is the database in SQL SERVER 6.5 which I want to connect.

I am able run the program now but its giving me a new error:
java.sql.SQLException: Connection Refused

HElp Please guys,

Regards,
 
PC MENIAC
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
> java.sql.SQLException: Connection Refused

Check that the database is listeneing for connections and the TCP/IP protocol is enabled.
0
 

Author Comment

by:PCMENIAC
Comment Utility
Objects,

How can we check that database is listening for connections and that TCP/IP protocol is enabled for it. And if nto enabled, how can we enable it. Once again, it an oldie SQL SERVER 6.5 that I'm using.

Thanks for your help!!

PCMENIAC
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
not sure with that version, with 2000 there is a Server Network Utility.
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
0
 

Author Comment

by:PCMENIAC
Comment Utility
Objects, The above support page is more or less for SQLServer 2000..

I have given up on 6.5 now and am looking into SQL SERVER 2K but with 2000 as well, I am having the following error:

java.sql.SQLException: No Suitable Driver

at java.sql.DriverManager.getConnection<DriverManager.java:532>

at java.sql.DriverManager.getConnection<DriverManager.java:193>

at ConnectDB2000.getConnection<ConnectDB2000.java:52>

at ConnectDB2000.main<ConnectDB2000.java:66>

Now, according to the FORUM at JTDS website, the only reason that "No suitable driver" exception is thrown by the DriverManager when none of the registered Driver implementations recognizes the supplied URL. This means that you either did not register jTDS with the DriverManager first (by calling Class.forName("net.sourceforge.jtds.jdbc.Driver")) or you mistyped the URL (e.g. "jbdc:jtds:..." instead of "jdbc:jtds:...").

Inorder to make sure that the JTDS Driver is registered with DriverManager, I got a message displayed if whether driver was loaded properly or not. The code for this is:

try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        }        
        catch (ClassNotFoundException e)
        {
            System.out.println ("Could not load the Driver!!");
            e.printStackTrace();
        }
            System.out.println("Driver Loaded Successfully");

****************************************************************************************
The second reason could be that I might have mistyped the URL, so here is the URL as well:

String uname = "ATCConnect";
String pwd = "ATC";
try
        {
             conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/ATC;uname;pwd");
        }
        catch (SQLException e)
        {
             System.out.println ("Could not create the connection!!");
             e.printStackTrace();
        }      

******************************************************************************************      
 Any suggestions??

Regards,

Parag Chauhan
 
atleast you and kennethxu managed to answer my original question correctly (although couldnt solve the problem completely). So, do you think give me the solution of the

I'm trying
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
> The above support page is more or less for SQLServer 2000..

From the bottom of that support page:

The information in this article applies to:
Microsoft SQL Server 4.2x
Microsoft SQL Server 6.0
Microsoft SQL Server 6.5

> I have given up on 6.5 now and am looking into SQL SERVER 2K but with 2000 as well

You might want to also try M$'s JDBC driver then.

> conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/ATC;uname;pwd");

uname;pwd doesn't look right to me.
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
try
conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/ATC;user=objects;password=secret");
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
>> am looking into SQL SERVER 2K but with 2000 as well
if you use sql server 2k, there is a free jdbc driver released by Microsoft:
http://www.microsoft.com/sql/downloads/default.asp (search for the work jdbc)
more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;313100
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
0
 

Author Comment

by:PCMENIAC
Comment Utility
Objects,

You were right there!!
The connection URL that I had:-   conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/ATC;uname;pwd");
was not correct.

It needs to be:-   conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/ATC;user=uname;password=pwd");

So, everything is sorted hopefully!!

KENNETHXU, OBJECTS

But it was actually Kennethxu's answer that got the Jtds Driver fixed and enabled me to use it in my java program but it was your answer that got the URL corrected. So, I think its only fair to share the points between you and Kennethxu.

Please let me know what you think!!!

Thanks,

Parag Chauhan

0
 

Author Comment

by:PCMENIAC
Comment Utility
OBJECTS, KENNETHXU,

Looks like I was a bit too hasty in my judgement regarding the results.

I still have the error, which is:
Java.sql.SQLException: Logon failed. Login failed for user 'uname'.
at net.sourceforge.jtds.jdbc.TdsConnection.<init> (TdsConnection.java:223)
at net.sourceforge.jtds.jdbc.Driver.getConnection(Driver.java:112)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:96)
at java.sql.DriverManager.getConection(DriverManager.java:512)
at java.sql.DriverManager.getConection(DriverManager.java:193)
at ConnectDB2000.getConnection(ConnectDB2000.java:49)
at ConnectDB2000.main(ConnectDB2000.java:64)


Also, I was thinking about giving Microsoft Driver for JDBC a go, but while looking at the documentation (KENNETHXU's Link for More Info!!), I realized that it requires me to list the .jar file in my CLASSPATH variable and I dont know how to do that.
IS it the same as : Control Panels-----> System (or System Properties)-----> advanced---> Environment Variables

And then add a new Variable with variable name as = CLASSPATH and its value = CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar

Please advice!! I would really appreciate your help!!

Thanks,

PCMENIAC
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
> Java.sql.SQLException: Logon failed. Login failed for user 'uname'.

Does that user exist on your database?

> I realized that it requires me to list the .jar file in my CLASSPATH variable

Easier to copy the jar's to the relevant 'ext' directories as I mentioned above for the tds driver.
0
 

Author Comment

by:PCMENIAC
Comment Utility
Hello Objects,

Yup, I created the username in the database ATC my self by following these steps:  SQL SERVER ENTERPRISE MANAGER ----> SQL Server Group -----> Security ---> Logins ----> (Right Click) New Login ---> (General Tab) Name = ATCConnect ---> Select SQL SERVER Authentification ---> Password = ATC ---> Default Database = ATC ---> (Database Access Tab) Select Database ATC for Permit ---> (Database Roles for ATC) tick public, DB_owner, DB_Datareader, DB_Datawriter ---> click OK.

Did I do Anything wrong?? Let me know!!

Also, I have put my jtds-0.8-rc.jar file in ext directory (D:\J2sdk1.4.0\jre\lib\ext) but it still didn't work. But when I called my program in following manner: java -cp %classpath%;jtds-0.8-rc1.jar;. ConnectDB, then it worked but came up with the error:

Java.sql.SQLException: Logon failed. Login failed for user 'uname'.
at net.sourceforge.jtds.jdbc.TdsConnection.<init> (TdsConnection.java:223)
at net.sourceforge.jtds.jdbc.Driver.getConnection(Driver.java:112)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:96)
at java.sql.DriverManager.getConection(DriverManager.java:512)
at java.sql.DriverManager.getConection(DriverManager.java:193)
at ConnectDB2000.getConnection(ConnectDB2000.java:49)
at ConnectDB2000.main(ConnectDB2000.java:64)

CAn you please help??

Regards,

PCMENAIC
0
 
LVL 92

Accepted Solution

by:
objects earned 500 total points
Comment Utility
> I have put my jtds-0.8-rc.jar file in ext directory (D:\J2sdk1.4.0\jre\lib\ext) but it still didn't work

thats the ext directory for the jdk.
you also need to put it in the 'ext' directory for your JRE. probably somewhere under program files
0
 

Author Comment

by:PCMENIAC
Comment Utility
Thanks very much for your suggestions. I was ignorant about the "ext" file in JRE. I have placed the jar file in its proper place now.

Regards,

Parag Chauhan

0
 
LVL 92

Expert Comment

by:objects
Comment Utility
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
scoresSpecial  challenge 13 40
Java asynchronous logging 4 31
JAVA part two 5 39
Java / Linux and Regular Expressions 11 43
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This video teaches viewers about errors in exception handling.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now