Connection string issues to SQL Server
Hey guys, I've been using these forums as a great reference for awhile and now I'm stumped and in need of a hand.
So I'm writing a web app and need to connect to a SQL Server db, here is what I've created from the Java side of things:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("Class found");
String accessDBURLPrefix ="jdbc:odbc:sqlserver://142.225.235.83;Initial Catalog=event;Persist Security Info=True;User ID=******";
Connection con = DriverManager.getConnection(accessDBURLPrefix);
System.out.println("Database Found");
Statement s = con.createStatement();
//String query = "SELECT * FROM [SITE SETUP]";
ResultSet rs = s.executeQuery(query);
I'm getting the error:
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and nodefault driver specified
Now I can open the db no problem in the VS 2005 connection manager and the connection string it lists in the properties is:
Data Source=142.174.35.83;Initial Catalog=event;Persist Security Info=True;User ID=*******
I guess even after looking up all the connection string examples online I still can't get it right.
Any hints?
Message was edited by:
Piers
[1445 byte] By [
Piersa] at [2007-11-27 8:55:14]
# 1
Use the JDBC Type 4 driver to connect to SQL Server. http://www.ftponline.com/special/sqlserver/dvohra/
# 2
try with:String accessDBURLPrefixDriver= "{SQL Server};Server=142.225.235.83;Database=event;Uid=myUsername;Pwd=myPassword";
# 3
that connection string nets me:java.sql.SQLException: No suitable driver
Piersa at 2007-7-12 21:16:04 >
# 4
http://msdn2.microsoft.com/en-us/library/ms378428.aspxjdbc:microsoft:sqlserver://<server_name>:<1433>=> http://forum.java.sun.com/thread.jspa?threadID=656388&messageID=3858155
# 5
Ok I now my issue is with the line:Class.forName("com.microsoft.jdbcx.sqlserver.SQLServerDataSource");now I isolated the error to here, it throws an exception in my try/catch block but I'm not sure why as I've never dealt with this before.
Piersa at 2007-7-12 21:16:04 >
# 6
Can you give us the exception or a stack trace? My experience with jdbc is that when it works it works great, but when it doesn't it's a compatibility issue with the driver version or type.
# 7
if u are using JDBC-ODBC Bridge Driver then connection string is
class.forName("sun.jdbc.odbc.JdbcOrbdDriver");
coonection=DriverManager("jdbc:odbc:DSNName","username","password");
and if u are using microsoft sql jdbc drivers then connectiong string is
class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;User=abc;Password=xyz")
n make sure u set classpath for SQLjdbc driver?
try the help web page tht is thr where u have installed/downloaded SQL JDBC drivers........
# 8
Does the SQL server have to have the JDBC drivers installed? I don't have access to it outside of it's address and uid/pass.i've added the driver to the classpath and it still fails to find the driver.I suppose I could try the jdbc:odbc method also
Piersa at 2007-7-12 21:16:04 >
# 9
import java.sql.*;
import com.microsoft.jdbc.*;
import com.microsoft.sqlserver.*;
public class AccessConnection {
public AccessConnection(){}
public static void AccessQuery(String query){
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String accessDBURLPrefix = "jdbc:sqlserver://142.xx..xx.83;User=anonymous;Password=anonymous";
Connection con = DriverManager.getConnection(accessDBURLPrefix);
System.out.println("Class found");
System.out.println("Database Found");
Statement s = con.createStatement();
//String query = "SELECT * FROM [SITE SETUP]";
ResultSet rs = s.executeQuery(query);
}
catch(ClassNotFoundException e){
System.out.println("Class not found");
}
catch(SQLException sqe){
System.out.println("SQL exception");
sqe.printStackTrace();
}
}
}
So I'm not getting past the Class.forName and I don't understand as I've added the sqljdbc.jar to the classpath
Piersa at 2007-7-12 21:16:04 >
# 10
HI u can use simple JDBC-ODBC bridge
class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection=DriverManager("jdbc:odbc:DSNName","username","password");
now if u have not set uername/password while creating DSN (ie when u create DSN for SQL DB u select window NT authentication and so u dont give username and password) then ur connection string will b like below
connection=DriverManager("jdbc:odbc:DSNName");
this should work.........
# 11
thank you, I have one last plead of ignorance: what is a DSN Name and where is it configured?
Piersa at 2007-7-12 21:16:04 >
# 12
Short for Data Source Name. Data Source Name provides connectivity to a database through an ODBC driver. The DSN contains database name, directory, database driver, UserID, password, and other information. Once you create a DSN for a particular database, you can use the DSN in an application to call information from the database.
There are three types of DSNs:
(1) System DSN -- can be used by anyone who has access to the machine. DSN info is stored in the registry.
(2) User DSN -- created for a specific user. Also stored in the registry.
(3) File DSN -- DSN info is stored in a text file with .DSN extension.
There is also what is known as a "DSN-less connection." Instead of using a DSN to connect to a database, the developer specifies the necessary information right in the application. With a DSN-less connection the developer is free to use connection standards other than ODBC, such as OLE DB.
# 13
Hi piers...
i think yannix gave good explanation for DSNname.....
now to configure it
go to control panel-->settings-->administrative tool-->Data Source(ODBC) double click on it
a window will open up ODBC Data Source Administrator
u can select USERDSN tab then click on add button...select the driver ie if u using microsoft access then u need to select Microsoft access driver(*.mdb)
and if u connecting to MSSQL the its the last option in list SQLServer
and abt other DB connection i dont have idea....then double click on the respective drive n window will open up asking u to give name for ur DSN....
let me know to which DB u r connecting..coz it to hard to write steps for diff DB connection.........
