JDBC What, Where, How and Why?

I need some information on JDBC, related specifically to talking to a MS SQL Server database over the net from an applet.
For an applet to send queries to SQL server (residing on the applet host), what is required for:
1. the applet
2. the SQL Server

What software is available to perform this task and how does it all work?
LVL 92
Who is Participating?

Improve company productivity with a Business Account.Sign Up

venkat2000120699Connect With a Mentor Commented:
U need to write a servlet on the server side to access the MS SQL database via a jdbc driver.  

Call the servlet from the applet passing the necessary information to the servlet.  

Ur servlet will access the database as said above, and returns the formatted output to the applet.

There is a type 3 driver, SequeLink, for using with MS SQL.  For more  info on this driver, visit http://www.merant.com/datadirect/product/jdbc/s150/overview.asp
objectsAuthor Commented:
Thanks for that, can you explain why I have to make my JDBC calls from the servlet and not the applet.

> There is a type 3 driver

What's a type 3 driver?
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

1. The applet can do the connections to the database as long their codebase is the same. Also, since you use an applet which will run on your clients machines, you cannot use jdbc:odbc because you don't have access to the odbc resources instaled in the client, and more bad situation for Linux where you don't have any odbc data sources. When I say odbc resources I meen the installed odbc drivers for your database server on the client local machine which cannot be accesed if the applet is not signed and is a bad ideea to map on every client the database server.The solution to this problem is an Java driver for your database (We use for testing AdabasD as database server, which comes with an adabasd.jar driver).

2. If not 1, you need a servlet with the same codebase as the applet, which will do the work for you in queriing the database.

3. If not 1,2 the last solution is an CGI script on the same codebase as the applet which will query the databases and return to you (to applet) the results. The CGI script will be then accesible with a trivial URLConnection for GET and POST methods. The bad thing at CGI is that they are slower than servlets.

Type 3 drivers are the java drivers for database systems maded by the database system vendor.
objectsAuthor Commented:
The applet will be accessible from the net, so it sounds like odbc is out if calling from applet.
Have you had any experience making jdbc calls over the net?
Trying to sort out the pros and cons of doing it directly from the applet, or via a servlet.
I have done something directly from the applet. The problem is that you MUST have a java driver for the connection to database which must be loaded with the applet.
In the rest all is the same as making a connection from an application to the database.
... like this (this is taken from my project, so the names are "a little" very specific) :

import java.sql.*;
* Class that load the vector defined in WorldState class, wich contains the towns on the map
* Also load the driver for Adabas database
public class DBAccess {
     static void updateWState(){
          // let's consider that WorldState is created by another ... for this moment

          // Load hardware driver for ...          
     try {
    } catch (ClassNotFoundException e) {
          System.out.println("JDBC driver for Adabas D not found");
              System.out.println("JDBC driver succesfully loaded.");//******************

    try {
             // make a connection to database      
         con = java.sql.DriverManager.getConnection(
    } catch (java.sql.SQLException e) {
      System.out.println("Error " + e.getErrorCode() +e.getMessage());
              System.out.println("SQL connection  succesfully started.");     //************
          // read location & update point vector
          String cmd="select XPOS,YPOS,IDTOWN, NAME from location";
          try {
               WorldState.pointVector =  new PointVector(20,50);  
               WorldState.townsVector =  new PointVector(20,50);  
                stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(cmd);
                while (rs.next()) {
                      (WorldState.pointVector).add(new SimplePoint(rs.getInt("XPOS"),rs.getInt("YPOS"),rs.getInt("IDTOWN"),rs.getString("NAME")));
          } catch (SQLException e) {
            System.out.println("Error: " + e.getSQLState());
     public static Connection con;
    private static Statement stmt;
objectsAuthor Commented:
SO what sort of overhead are we talking about, ie. how big is the jar for the drivers?  And equally important, what's the performance of jdbc calls over the net?

What about security, can jdbc calls be made secure?

PS. I have increased the points.
The jar used by me is about 50K. Performance ? Hm, The applet we are speaking about was never runned from the net only on intranet, so the speed was good :). But I think will not be problems. I also have made an tree applet wich can be loaded over net and which is loading dinamically, by calling an CGI script to do the dirty job (SQL statements). The speed is OK, so if CGI is slower than Java I have reasons to believe that you will not have serious speed problems.
take a look at this :

(just founded today by mistake), I think you have there a full mysql JAVA driver.
You may visit the url: http://www.vn.ua/pub/unix/devel/java/jdbc.FAQ.htm
The page at this url will answer most of your questions.

There is something called a middle tier that helps connect one endpoint to another(an applet to a databse, for example).  The reason for putting a middle tier between a client and our ultimate data source is that software in the middle tier(commonly known as middleware) can include business logic.  Business logic abstracts complicated low-level tasks(such as updating database tables) into high-level tasks(placing an order), making the whole operation simpler and safer.  (What happens if someone decompiles your applet, which has the code for database operations, and try to destroy your database, by writing an application?)  If the database server changes in any way(by moving to a different machine, altering its internal table structure, or changing database vendors), the client applet may break.  

On the web, middle tiers are often implemented using servlets, jsps, cgi, asp, etc.. They provide a convenient way to connect clients built using HTML forms or applets to back-end servers.  A client communicates its requirements to the servlet using HTTP, and the business logic in the servlet handles the request by connecting to the back-end server.

Types of Drivers:

Visit the url, http://www.javaworld.com/javaworld/jw-07-2000/jw-0707-jdbc_p.html which I have already provided to you.

To know more about how many tiers you use for your application, visit the url, http://www.javaworld.com/javaworld/jw-01-2000/jw-01-ssj-tiers.html

I hope that these links and explanation answers your queries.
objectsAuthor Commented:
Thanks for the comments, all the jdbc calls will be to stored procedures containing our business logic so there should not be a need to include any business logic in the applet. Though for various reasons I'm starting to lean towards making the jdbc calls thru a sevlet.
But you've made many valid pointss, thanks :)
objectsAuthor Commented:
Thanks to both of you for the help :)
Ovi, I've posted some points for you also.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.