[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1766
  • Last Modified:

Connecting SQL SERVER 6.5 with JAVA!!

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
PCMENIAC
Asked:
PCMENIAC
  • 18
  • 12
  • 7
1 Solution
 
PCMENIACAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
objectsCommented:
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
 
PCMENIACAuthor Commented:
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
 
objectsCommented:
Easiest it to put the jar file in the 'ext' directory of your jdk:
<jdk>/jre/lib/ext
0
 
objectsCommented:
the connection strinbg for jtds will look like:
jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]
0
 
objectsCommented:


    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
 
kennethxuCommented:
>> 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
 
objectsCommented:
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
 
PCMENIACAuthor Commented:
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
 
kennethxuCommented:
>> 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
 
PCMENIACAuthor Commented:
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
 
kennethxuCommented:
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
 
PCMENIACAuthor Commented:
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
 
kennethxuCommented:
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
 
objectsCommented:
>  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
 
kennethxuCommented:
>> 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
 
objectsCommented:
Because it is unnecessary.
0
 
kennethxuCommented:
unnecessary step of doing a remove from existing code?
0
 
PCMENIACAuthor Commented:
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
 
objectsCommented:
> java.sql.SQLException: Connection Refused

Check that the database is listeneing for connections and the TCP/IP protocol is enabled.
0
 
PCMENIACAuthor Commented:
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
 
objectsCommented:
not sure with that version, with 2000 there is a Server Network Utility.
0
 
PCMENIACAuthor Commented:
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
 
objectsCommented:
> 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
 
objectsCommented:
try
conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/ATC;user=objects;password=secret");
0
 
kennethxuCommented:
>> 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
 
PCMENIACAuthor Commented:
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
 
PCMENIACAuthor Commented:
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
 
objectsCommented:
> 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
 
PCMENIACAuthor Commented:
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
 
objectsCommented:
> 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
 
PCMENIACAuthor Commented:
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
 
objectsCommented:
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 18
  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now