Database connection

[lostinjava]

How can I connect to an external SQLServer 2000 database with JDBC?
The code will be on an applet.

 

[lostinjava]

bump!

No one?

 

[SalShaikh]

hi lostInJava,

this is very simple in java...

first things first before the nine steps
you have to have the JDBC drivers on your machine.

http://msdn.microsoft.com/downloads...=/msdn-files/027/001/779/msdncompositedoc.xml

I am positive you can download them for free from Microsoft.
then make sure you have set the CLASSPATH variable correctly on your machine to point to the correct *.jar or *.zip file which contains all the compiled java files for connecting to SQL SErver



If you need more help on the above issue.. the download file for SQLServer 2000 jdbc drivers should have proper documentation
alright now let's get started
step 1. make sure in your code you are importing the package java.sql.* --- using the line of code
import java.sql.*;
step 2. load your drivers. okay this is simple
use the following line of code:
Class.forName("this where the jdbc driver call goes"
step 3. make a connection object simple again:
Connection conn = DriverManager.getConnection("typeTheUrlOfYourDataBase","userName","passWord"
step 4. make a statement object from your connection object:
Statement stmt = conn.createStatment();
step 5. make a query:
String qry = "Select * from blah";
step 6. make a resultset object from your statment object and query:
ResultSet = stmt.executeQuery(qry);
Step 7. process the resultSet using the resultset methods describe on sun's website in the api.
Step 8. close things down:
rs.close();
stmt.close();
conn.close();

Its simple but you can do much more than i describe any more questions? I am here.

Sal

 

[lostinjava]

thank you for replying.
I think I didn't explained well, I know how to connect to a database, I have the drivers, etc, etc.
But when I try to connect to an external Database the connection fails (allways throws as java.Nullpointer exception).
I mean I can connect to a DB that is on my computer, I can connect to a DB that is inside my LAN/Workgroup, but if I try to let's say connect to a DB that is in your computer I can't(of course I know the admin user and pass for that DB).
So the matter here isn't exactly knowing on how using JDBC, but using it to connect to an external DB. I need to access to one of my clients DB with JDBC and I don't seem to be able to do it. Maybe there is some configuration needed by his side, maybe I need to add more code to the connection string... I really don't know...

If you can provide me further help I would need it please. Thank you again.

 

[Bygbobbo]

As long as the database is registered in the ODBC you can configure to any path as long as you have access rights...

Here is a nice little java code snippet that allows you a connection using JDBC-ODBC:

//class database setup

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;


public class JDBCsetup {

public static void JDBCsetup(){
try{
//steps 1 & 2
//DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver());
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
System.out.println("Driver setup here"
/*or use this
String drivername = "sun.jdbc.odbc.JdbcOdbcDriver";
Class.forName(drivername); */
}catch(SQLException e){
System.out.println(e.getMessage () + e.toString());
}
}

public static Connection getConnected (String DB) {
System.out.println ("getting connection..."
try {
// String jdbc_url = "jdbcracle:thinlocalhost:1521:"+DB;
// Connection conn = DriverManager.getConnection (jdbc_url, "scott", "tiger"
String jdbc_url = "jdbcdbc:"+DB; // DB is the database passed in
Connection conn = DriverManager.getConnection (jdbc_url);
System.out.println ("connection created ..."
return conn;
}
catch (SQLException e) {
System.err.println (e.getMessage () + e.toString() );
}
return null; // never happen
}

public static Statement makeStatement (Connection conn) {
try {
System.out.println ("Making a Statement..."
Statement stmt = conn.createStatement ();
System.out.println ("Statement created..."
return stmt;
}
catch (SQLException e) {
System.err.println (e.getMessage () + e.toString() );
}
return null; // never happen
}
public static int openResultSetUpdate (Statement stmt, String query) {
try {
System.out.println ("Creating resultSet..."
int rs = stmt.executeUpdate (query);
System.out.println ("Resultset created..."
return rs;
}
catch (SQLException e) {
System.err.println (e.getMessage () + e.toString() );
}
return -1; // never happen
}
public static ResultSet openResultSet (Statement stmt, String query) {
try {
System.out.println ("Creating resultSet..."
ResultSet rs = stmt.executeQuery (query);
System.out.println ("Resultset created..."
return rs;
}
catch (SQLException e) {
System.err.println (e.getMessage () + e.toString() );
}
return null; // never happen
}
public static void closeThingsDown (ResultSet rs, Statement stmt, Connection conn) {
try {
System.out.println ("Closing Things Down..."
rs.close();
System.out.println ("ResultSet Closed..."
stmt.close ();
System.out.println ("Statement closed..."
conn.close();
System.out.println ("Connection closed..."
}
catch (SQLException e) {
System.err.println (e.getMessage () + e.toString() );
}
}

public static void closeThingsDown (Statement stmt, Connection conn) {
try {
System.out.println ("ResultSet Closed..."
stmt.close ();
System.out.println ("Statement closed..."
conn.close();
System.out.println ("Connection closed..."
}
catch (SQLException e) {
System.err.println (e.getMessage () + e.toString() );
}
}
}

As you can see all 9 steps are documented..

good luck

 

[lostinjava]

ok. That's more or less what I do, but let me explain better what I'm trying to achieve.

My client has SQL Server 2000 DB on their server. I'm developing a java applet that will be stored on their servers, but it will be ran from any web browser, and I need to connect to the database. Wich I seem to be failing... I'm using the Sprinta2000 drivers.

When I test the applet on a web browser inside their LAN everything works sweet. If I try to run it from a web browser on let's say my home it simply won't connect!
Do I need to register the DB on ODBC? How? Would I need to do this on every computer that would possibly access their DB? That would be impossible since this will run from a browser... maybe something is missing me here...
Like I said before I KNOW how to connect to a DB by code with JDBC, since the code works fine when I run it in a browser inside their LAN, I just can connect when I'm outside.

 

[Bygbobbo]

There is a better way of connecting to a database. Im not exactly sure what your applet is trying to do or what your trying to accomplish from the client side with the applet.. BUT if you are trying to, lets say, work from home and connect to the database you may want to get tsclient, which is free shareware for connecting to servers through the internet.

!!!!!Are you trying to get full database access and make this available for the client side??

Is your applet extracting information from the database?

Otherwise, can you clearify exactly what it is your trying to accomplish? I may have an alternative solution.

Bygs ...

 

[turncom]

You know, this sounds like a security issue. Don't applets run in a sandbox? And if the datasource is not on the same server as the applet, isn't access denied? It's a feature, not a bug.

I am not an expert on security in applets. I hope someone with some experience in these matters can expand.

 

[godcomplex]

This is just a shot in the dark, and you have probably already thought of this, but it sounds to me like the problem you are having is an ip problem. I assume you are connecting to this db using an ip address. If so, the ip on the lan ( if running behind a firewall or proxy ) would only be valid for that lan. For instance 198.168.212.11 might be valid on your lan, but only on you lan, and not on the entire web. When you go home, you are on a different physical network, so when trying to connect to this computer you would not get a connection, because a db probably doesn't exist on that ip on the web, ot that ip may not even exist on the web.

Once again, this is just a thought. Might be completly off base.

-gc "I don't look busy because I did it right the first time."

 

[SusieQue]

Something is not handled correctly in your applet.. JDBC should set up a connection to the database from anywhere. BUT, the connection/inputStream must be maintained with a socket... Just out of curiousity what is your applet doing that a servlet can't handle...

To solve this I would handle the java.Nullpointer exception with some tagged code to find out where the applet crapped out.

Could you please be more descriptive or put up some code that I can look at....

thanks,
Susie-Q

 

[lostinjava]

ok, again thanks for the replies. I still can't figure it out what's going on...

Bygbobbo:

What exactly would tsclient do? Never heard of it before. My applet will read data from the database, insert and delete records that's more or less what I want to do.

But the problem isn't in what I want to do I think, because I get the error exactly in the line of code where I try to connect to the database.

turncom: I don't think it's a feature. An applet should be able to connect to any database, also I'm not saying this is a bug, I'm just probably missing something out. Probably this is a security issue, I don't think it's the code...

godcomplex: Yes, I've thought of that, but I think(actually I'm sure) it is possible to connect to a database from anywhere, probably I'm missing something...

SusieQue: Within a socket? How exactly would I do that?

This is the code I use to connect to the database:

public DatabaseConnection(String strDbServer) throws DataModelException
{
if (c == null)
{
dbServer = strDbServer;
try
{
System.out.println(ru.getString("Connecting to &quot + dbServer + ":1433"
c = new DataTableConnection(
"com.inet.tds.TdsDriver", // driver
"jdbc:inetdae:" + dbServer ":1433", // url
"sysadmin", // user
"passadmin", // password
"Operative" // database

}
catch(Exception e)
{
System.out.println(e);
return;
}
}

When the new DataTableConnection is being made I'll get an error:

com.klg.jclass.datasource.DataModelException: com.inet.tds.SQLException: connect (code=10051)


I was thinking this could be a problem on the ports to connect to the database? Could they be locked from my side or the server side?
Anyway this is really strange, and I'm not seeing a way to solve this

 

[BaconMakinWabbit]

I know you have probably already checked this but are you running a "thin" driver?
I believe that applets demand that you use a thin driver so that the client does not have to download it.
Just a thought.

 

[BaconMakinWabbit]

Actually now that I look further at it....You DO NEED to use a thin driver for Applets.