Connect to SQL Server: jdbc

Showing posts with label jdbc. Show all posts

Tuesday, March 27, 2012

connecting to SQL Server 2000 using JDBC

I've download Microsoft SQL Server 2000 JDBC Driver. All these files are being installed and saved in a folder called sql. For example, the IP adress is 255.255.255.255 , I updated the code and saved it as test1.java in this manner:

import java.sql.*;
/**
* Microsoft SQL Server JDBC test program
*/
public class test1 {
public test1() throws Exception {
// Get connection
DriverManager.registerDriver(new
com.microsoft.jdbc.sqlserver.SQLServerDriver());
Connection connection = DriverManager.getConnection(
"jdbc:microsoft:sqlserver:@.255.255.255","abc","abc");
if (connection != null) {
System.out.println();
System.out.println("Successfully connected");
System.out.println();
// Meta data
DatabaseMetaData meta = connection.getMetaData();
System.out.println("\nDriver Information");
System.out.println("Driver Name: "
+ meta.getDriverName());
System.out.println("Driver Version: "
+ meta.getDriverVersion());
System.out.println("\nDatabase Information ");
System.out.println("Database Name: "
+ meta.getDatabaseProductName());
System.out.println("Database Version: "+
meta.getDatabaseProductVersion());
}
} // Test
public static void main (String args[]) throws Exception {
test1 test = new test1();
}
}

I'm able to compile but when I run the program, the given error is:

Exception in thread "main" java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source
)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:512)
at java.sql.DriverManager.getConnection(DriverManager .java:171)
at test1.<init>(test1.java:10)
at test1.main(test1.java:31)

I have checked that the given IP address is correct, so please can anyone enlighten me about the problem.

Thanks.You must set your database name and port number such as following
Connection connection = DriverManager.getConnection(
"jdbc:microsoft:sqlserver:@.255.255.255:1433;Databas eName=MyDB","abc","abc");

You can also use your computer name in url

"jdbc:microsoft:sqlserver://MYSERVER:1433;DatabaseName=MYDB","USERID","PASSWORD"

Originally posted by diablos00
I've download Microsoft SQL Server 2000 JDBC Driver. All these files are being installed and saved in a folder called sql. For example, the IP adress is 255.255.255.255 , I updated the code and saved it as test1.java in this manner:

import java.sql.*;
/**
* Microsoft SQL Server JDBC test program
*/
public class test1 {
public test1() throws Exception {
// Get connection
DriverManager.registerDriver(new
com.microsoft.jdbc.sqlserver.SQLServerDriver());
Connection connection = DriverManager.getConnection(
"jdbc:microsoft:sqlserver:@.255.255.255","abc","abc");
if (connection != null) {
System.out.println();
System.out.println("Successfully connected");
System.out.println();
// Meta data
DatabaseMetaData meta = connection.getMetaData();
System.out.println("\nDriver Information");
System.out.println("Driver Name: "
+ meta.getDriverName());
System.out.println("Driver Version: "
+ meta.getDriverVersion());
System.out.println("\nDatabase Information ");
System.out.println("Database Name: "
+ meta.getDatabaseProductName());
System.out.println("Database Version: "+
meta.getDatabaseProductVersion());
}
} // Test
public static void main (String args[]) throws Exception {
test1 test = new test1();
}
}

I'm able to compile but when I run the program, the given error is:

Exception in thread "main" java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source
)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:512)
at java.sql.DriverManager.getConnection(DriverManager .java:171)
at test1.<init>(test1.java:10)
at test1.main(test1.java:31)

I have checked that the given IP address is correct, so please can anyone enlighten me about the problem.

Thanks.|||I had the same problem,

The problem is that the Microsoft jdbc driver used unicode and Ianywhere can't handle it.

Use an other jdbc driver from a 3de party will solve this problem.

I used the jdbc driver from datadirect (Type 4 jdbc driver) and this one
was working perfectly. Only thing is you have to pay for it.

I hope i could help you with this.

Greetz|||Microsofts JDBC driver works just fine. In fact it's an old data direct Type 4 driver. Make sure you have the service pack 1 jdbc driver. Here's the exact connection url I use with no problems.

jdbc:jtds:sqlserver://tlmssql3:1388/db1;USER=appsrv1;PASSWORD=password;TDS=8.0

Monday, March 19, 2012

Connecting to MSSQL Server 2000 using JDBC

Hello,

I am developing an application which needs JDBC connection to a Named Instance of SQL Server 2000 (other than default instance). I am facing problem in this regard as my code gives an exception "Time Out" or "Connection Failed". However it works fine with the default named instance...The main driver class is "com.microsoft.sqlserver.jdbc.SQLServerDriver"

and Connection URL is

"jdbc: sqlserver://localhost;instanceName=Testing:1432;databaseName=testDB;"

It gives the exception that Unable to connect to named instance....

Please if any one have any knowledge in this regard....do let me know....I have read an article on MSDN according to which on MS SQL Server 2000 the named Instance other than default can be accessed only through named pipes.

http://msdn2.microsoft.com/en-us/library/aa224779(sql.80).aspx

if some body can tell me Connection string for JDBC driver which utilizes named pipes...the it will be very help full for me.........any help from MSDN Experts is really appreciated...

Thanks

Muhammad:

The JDBC driver does connect to named instances but doesn't use Named Pipes -- only TCP/IP. In your case it looks like you're overloading the instanceName property in the connection string by putting the instanceName and portNumber together. Try breaking them apart like this:

"jdbc: sqlserver://localhost;instanceName=Testing;portNumber=1432;databaseName=testDB;"

If that still doesn't work let me know.

-shelby

|||

Thanks Shelby for your quick reply.....I have tried the string you have given and it is given the same error..

"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

at java.sql.DriverManager.getConnection(DriverManager.java:525)

at java.sql.DriverManager.getConnection(DriverManager.java:193)

at com.frt.test.DatabaseTest.main(DatabaseTest.java:21)"

One thing i want to discuss with you is that when I go to "Server Network utility" the value written in default port for the second instance in "TCP/IP" is 0..why it is so? I have chaged it to 1432 myself and have restarted the server again....My code is as follows (however it is very simple but I am sending it)

import java.sql.*;

public class DatabaseTest
{
public static void main(String[] args)
{
try
{
java.lang.Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
// Class.forName("com.inet.tds.TdsDriver");
Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost;instanceName=TESTING;portNumber=1432;databaseName=testDB;" );
// Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost/pipe/MSSQL$TESTING/sql/query?database=testDB&user=sa&password=abc");
// Connection c = DriverManager.getConnection("jdbc:inetdae7://localhost/pipe/MSSQL$TESTING/sql/query?database=CMS&user=sa&password=abc");
Statement s = c.createStatement();
ResultSet r =s.executeQuery("select * from table1");
while(r.next())
{
System.out.println(r.getString(2));
}
System.out.println( "Connected!" );
}
catch( Exception ex )
{
ex.printStackTrace();
}
}
}

as you can see that in connection statement I have commented out some lines....that is connection string of some other driver I have downloaded for testing...and when I use it with piped names it connects perfectly to named instance...but I have to use the first driver....so please help me...

Thanks for your coordination

bye

|||

At last i got the solution.....All the problem is due to cause that Service Pack 3.0 for SQL Server 2000 was not installed on my machine...as it is a bug in SQL Server 2000, that it does not listen on TCP/IP port, as u can verify by using TELNET, however this bug has been removed in SP3

Regards

Muhammad Ummar

Connecting to MSSQL Server 2000 using JDBC

Hello,

and Connection URL is

"jdbc: sqlserver://localhost;instanceName=Testing:1432;databaseName=testDB;"

It gives the exception that Unable to connect to named instance....

http://msdn2.microsoft.com/en-us/library/aa224779(sql.80).aspx

if some body can tell me Connection string for JDBC driver which utilizes named pipes...the it will be very help full for me.........any help from MSDN Experts is really appreciated...

Thanks

Muhammad:

"jdbc: sqlserver://localhost;instanceName=Testing;portNumber=1432;databaseName=testDB;"

If that still doesn't work let me know.

-shelby

|||

Thanks Shelby for your quick reply.....I have tried the string you have given and it is given the same error..

"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

at java.sql.DriverManager.getConnection(DriverManager.java:525)

at java.sql.DriverManager.getConnection(DriverManager.java:193)

at com.frt.test.DatabaseTest.main(DatabaseTest.java:21)"

import java.sql.*;

Thanks for your coordination

bye

|||

Regards

Muhammad Ummar

Sunday, March 11, 2012

connecting to database

Hi everybody

I was trying to connect to datatbase on my computer, through java. I understand that I will need to download JDBC driver for that. But i am not sure which driver will I need, and where do i need to put it. Do I have to make some changes in CLASSPATH as well?

I am using SQL 2000. My jdk profile is j2sdk1.4.2.01. Also what is subname in

jdbc:<subprotocol>:<subname>

is it the name of database or what?

In need of help !!!!!!

thanks in advanceThe jdbc drive should be in the classpath and added to your project (depends on your environment).

I believe that the subname is the database (could be its ip), with subprotocol being the driver, for db2 its db2, not sure thought for sql 2000.

Are you creating a standalone java application and appled or jsp ?

Sunday, February 19, 2012

Connecting JDBC with SQL Server, error establishing socket

I have tried everything..I cant get Java to connect to my sql
server...
heres the code I altered provided my microsoft to test this connection:
import java.sql.*;
class test {
public static void main(String args[]) {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbc:datadirect:sqlserver://dbserver:1433;DatabaseName=sample",
"sa", "");
conn.close();
System.out.println("ok");
}
catch (Exception e) {
e.printStackTrace();
}
}
}
Heres the error messages:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establis
hing socket.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source
)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSConnection.<in it>(Unknown
Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplConnecti on.open(Unknown
Sou
rce)
at
com.microsoft.jdbc.base.BaseConnection.getNewImplC onnection(Unknown S
ource)
at com.microsoft.jdbc.base.BaseConnection.open(Unknow n Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:512)
at java.sql.DriverManager.getConnection(DriverManager .java:171)
at test.main(test.java:12)
thanks
It appears that you do not have access to your db server machine.
try ping command for the IP address.
This has to do with the network settings.
I use MSSQLServer driver and It seems that my DB-URL is different.
DRIVER=com.microsoft.jdbc.sqlserver.SQLServerDrive r
URL=jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor
this works fine.
Message posted via http://www.sqlmonster.com

Connecting JDBC with SQL Server

I have tried everything..I cant get Java to connect to my sql
server...port 1433 is what sql server is running off of..my password
and username are correct..ive tried the ip, comp name and localhost for
server name and I cant make a connection to it...
heres the code I altered provided my microsoft to test this connection:
public class Connect{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "192.168.1.102";
private final String portNumber = "1433";
private final String databaseName= "KB";
private final String userName = "kbuser";
private final String password = "password";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";
// Constructor
public Connect(){}
private String getConnectionUrl(){
return
url+serverName+":"+portNumber+";databaseName="+dat abaseName+";selectMethod="+selectMethod+";";
}
private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
con =
java.sql.DriverManager.getConnection(getConnection Url(),userName,password);
if(con!=null) System.out.println("Connection
Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " +
e.getMessage());
}
return con;
}
/*
Display the driver properties, database details
*/
public void displayDbProperties(){
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try{
con= this.getConnection();
if(con!=null){
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+
dm.getDriverName());
System.out.println("\tDriver Version: "+
dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+
dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+
dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next()){
System.out.println("\tcatalog: "+
rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}catch(Exception e){
e.printStackTrace();
}
dm=null;
}
private void closeConnection(){
try{
if(con!=null)
con.close();
con=null;
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}
Heres the error messages:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establishing socket.Error Trace in getConnection() :
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
Error: No active Connection
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSConnection.<in it>(Unknown
Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplConnecti on.open(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.getNewImplC onnection(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection.open(Unknow n Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager .java:512)
at java.sql.DriverManager.getConnection(DriverManager .java:171)
at Connect.getConnection(Connect.java:36)
at Connect.displayDbProperties(Connect.java:53)
at Connect.main(Connect.java:89)
thanks
-Jim
Jim,
There could be a number of reasons why a connection can't be
established: server not running, TCP/IP not enabled on the server, a
firewall blocking the connection etc. Check all these and until "telnet
<server_host> 1433" won't connect, the JDBC driver won't connect either
(unless it used named pipes).
Alin,
The jTDS Project.
|||Yep...I could not telnet to that port and when I did a netstat sql
server wasnt listening on that port...
To fix this I upgraded from the RTM to service pack3...rebooted and was
able to connect just fine...
Thanks
-Jim

connecting jdbc type 4 with ms sql server.

Hello to all,

I am using windows 2000 and i want to connect to ms sql server with
jdbc type 4 driver.
can you give me sample code and few tips to get me through this.,

thank you in advance,
vishnu.http://support.microsoft.com/defaul...kb;en-us;313100

http://java.sun.com/docs/books/tuto...ot0/sample.html

HTH,
Dave

"vishnu mahendra" <vishnu_mahendra@.yahoo.com> wrote in message
news:c24d8b9.0311200931.2e4d5ad2@.posting.google.co m...
> Hello to all,
> I am using windows 2000 and i want to connect to ms sql server with
> jdbc type 4 driver.
> can you give me sample code and few tips to get me through this.,
> thank you in advance,
> vishnu.|||"Dave Hau" <nospam_dave_nospam_123@.nospam_netscape_nospam.net_ nospam> wrote in message news:<puevb.11619$_m6.10136@.newssvr13.news.prodigy.com>...
> http://support.microsoft.com/defaul...kb;en-us;313100
> http://java.sun.com/docs/books/tuto...ot0/sample.html
> HTH,
> Dave
>
> "vishnu mahendra" <vishnu_mahendra@.yahoo.com> wrote in message
> news:c24d8b9.0311200931.2e4d5ad2@.posting.google.co m...
> > Hello to all,
> > I am using windows 2000 and i want to connect to ms sql server with
> > jdbc type 4 driver.
> > can you give me sample code and few tips to get me through this.,
> > thank you in advance,
> > vishnu.

hello dave,

Thank you for the information.
your help is much appreciated.

vishnu.

Connecting jdbc driver from an applet

Hi,

I've found problems when executing

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

I get a java.lang.ClassNotFoundException, and I have done this steps:

1o I got the driver from msdn web page

2o I modified the classpath in Windows XP adding the path to the sqljdbc.jar, testing if there was any problems with blanks in the path, I tried to put the .jar file in a more simple folder, but it didn't work.

Perhaps I've to do something whith my policy.java or so archives to let applets do something about this but I have no idea.

Can someone help me? Thanks!

Hi there,

What IDE are you using (e.g. Eclipse, IntelliJ)? Are you coding and then running your app through the IDE?

If you are running your app through the IDE, something to be aware of is that most Java IDE's define their own classpath and by default don't use the CLASSPATH environment variable you set up in Windows.

To fix your problem, you need to configure the classpath used by your IDE so that it includes the sqljdbc.jar file.

If you're not sure how to do this then consult the doco that came with the IDE but if it's no help give us a buzz and we can probably work something out (I'm only familiar with Eclipse and IntelliJ though).

Hope that helps a bit, but sorry if it doesn't.
|||

Hi and thanks for your time,

I'm not using any kind of IDE. I've used to program with Eclipse but I'm not allow to do that now. With Eclipse I had add jar files other times without problems.I have to ask for it and I had this as my last solution.

So I'm doing it with a simple text editor and my JVM compiling everytime I want to. Then I open my applet with my internet explorer and that's when I get my error.

I'm looking for a solution with this conditions. Thanks

|||Hi there
I'm quite sure my problem is CLASSPATH, because I've found some examples where is tested my line code error and, in the catch block there is always a report like "You haven't registered SQL Server 2000 in the classpath" or so.
In my case, I have just add .jar file containing the driver, to the CLASSPATH variable. Does is it correct?

Thanks!
|||Yes, if the sqljdbc.jar file is not in the classpath then it needs to be there (if that's what you're asking).

So your classpath variable should be something like:

%classpath%;C:\JDBC\sqljdbc.jar

|||I've found another solution, and it seems to be working ok. Instead of using directly a new JDBC driver, I'm using the JDBC-ODBC generic bridge, so in the calling I should write

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

The only thing I' have to take in account is that I must give permissions to the applet in order to be able to get that driver and use the classes. I can do that if I modify my java.policy file.
|||

Hmmm, this may be a year too late, but for the sake of others running into the same problem...

First, I've had better luck getting the sqljdbc driver to work with SQL Server 2005, using

DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

rather than

Class.forName(...)

Second, I couldn't get this to work until I put an "ARCHIVE" parameter in the tag, referring specifically to the sqljdbc.jar:

Next, I ran into a problem with running the applet from a web server that is *separate* from a remote database server (whose IP and port, below, I have changed in the interest of security):

Exception in thread "Thread-73" java.security.AccessControlException: access denied (java.net.SocketPermission 123.235.12.43:1234 connect,resolve)

As mentioned, the solution to this is to create a policy file, in this example named "jdbcTest.pol", that gives the applet the necessary permissions:

grant
{
permission java.net.SocketPermission "123.235.12.43:1234", "connect,resolve";
};

The problem with this seems to be that although it works when run under appletviewer, by specifying the policy file:

appletviewer -J-Djava.security.policy=jdbcTest.pol jdbcTest.html

It DOES NOT work when run in a browser! There does not seem to be a way to specify the policy file (for instance, as a parameter in the <APPLET> tag). A possible solution to this might be referred to in this snippet from the Java security FAQ:

"How can an applet open a network connection to a computer on the internet?

Applets are not allowed to open network connections to any computer, except for the host that provided the .class files. This is either the host where the html page came from, or the host specified in the codebase parameter in the applet tag, with codebase taking precendence."

So, it sounds like it might be possible to set the CODEBASE parameter to point to the remote database server (point to what, exactly, though?) or to have a page on the web server load the jdbcTest.class from a location on the remote database server. But I haven't got any of this to work yet! I'll post something here if I do. Otherwise, I'd be happy to hear from anyone who has got this to work.

The question is: Is it possible, given the security restrictions, to connect to a SQL Server running on a remote host from an applet running on a separate web server? If so, how?