Solved

JDBC What, Where, How and Why?

Posted on 2001-06-13
15
274 Views
Last Modified: 2010-03-31

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?
0
Comment
Question by:objects
  • 7
  • 5
  • 3
15 Comments
 
LVL 2

Accepted Solution

by:
venkat2000120699 earned 150 total points
ID: 6189081
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
0
 
LVL 2

Expert Comment

by:venkat2000120699
ID: 6189096
0
 
LVL 92

Author Comment

by:objects
ID: 6189161
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?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 9

Expert Comment

by:Ovi
ID: 6189607
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.

Cheers.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6189613
Type 3 drivers are the java drivers for database systems maded by the database system vendor.
0
 
LVL 92

Author Comment

by:objects
ID: 6189632
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.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6189640
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.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6189642
In the rest all is the same as making a connection from an application to the database.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6189652
... 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 {
      Class.forName("de.sag.jdbc.adabasd.ADriver");
    } 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(
           "jdbc:adabasd:"+"//"+TestVector.param+"/"+"MYDB",
        "HARTI","ITRAH");
    } catch (java.sql.SQLException e) {
      System.out.println("Error " + e.getErrorCode() +e.getMessage());
      System.exit(0);
    };
              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;
}
0
 
LVL 92

Author Comment

by:objects
ID: 6189709
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.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6189773
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.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6190372
take a look at this :
http://www.informatik.htw-dresden.de/~beck/JAVA11/JDBC/

(just founded today by mistake), I think you have there a full mysql JAVA driver.
0
 
LVL 2

Expert Comment

by:venkat2000120699
ID: 6192464
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.
0
 
LVL 92

Author Comment

by:objects
ID: 6193718
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 :)
0
 
LVL 92

Author Comment

by:objects
ID: 6199130
Thanks to both of you for the help :)
Ovi, I've posted some points for you also.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
what is a "java.lang.System Property"   ? 20 67
Why my table column Id is not passed to java object? 4 39
maven module vs maven project 3 23
going to wrong jsp page 2 21
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question