Solved

JDBC What, Where, How and Why?

Posted on 2001-06-13
15
270 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
Comment Utility
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
Comment Utility
0
 
LVL 92

Author Comment

by:objects
Comment Utility
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
 
LVL 9

Expert Comment

by:Ovi
Comment Utility
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
Comment Utility
Type 3 drivers are the java drivers for database systems maded by the database system vendor.
0
 
LVL 92

Author Comment

by:objects
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 9

Expert Comment

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

Expert Comment

by:Ovi
Comment Utility
... 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks to both of you for the help :)
Ovi, I've posted some points for you also.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now