How does JSP compare to ASP in terms of connecting to databases such as MS SQL Server from the web?

anil chakravarthy

ASP and MS SQL Server together is a great combination.I have used them in a production environment with little or no problems (except for one or two occassions when IIS just stopped and I never knew why...).

Connecting to SQl Server through ASP is pretty straight forward. ASP manages its connections to the database through a connection pool that IIS maintains.I wouldn't have to worry a thing about how to manage the pool, return the connection back to the pool, ans so on. Syntax wise, this is how a connection is made and a resultset is obtained:

set conn = server.createobject("ADODB.connection")
dsnstr = "DSN=prdn;uid=prdn;pwd=prdn;"
conn.open dsnstr
sql = "select * from users_records"
set rs = dataconn.execute(sql)
do while not rs.eof
 response.write(rs("user_name") & "<br>") 

DSN or the data source name is configured using the ODBC mannager, which is available under settings in the control panel. This DSN can be configured to point to a machine on the LAN that hosts the IIS server.

On the other hand, JSP can also be used using the same DSN, but should be stricly avoided due to the fact that the JDBC-ODBC bridge leaks memory and shouldnt be used in a production environment. A disadvantage of using JSP is that you have to manage the connection pool all by yourself, typically by writing additional Java classes for the purpose. However, the good news is that all major application server vendors are coming out with connection pool packages that implement this for you.

I have used i-Net Software'sJDBC drivers for MS SQL Server. These drivers give you a choice of either connecting to the database through named pipes or socket based connections. SQL server by default listens on port number 1433. These are the kind of drivers that are supposed to be used in a production environment, and they are 100% Java and are robust. The syntax for connecting to a database using JSP would be something like:

<%@ page import='java.sql.*' %>
 Connection conn = DriverManager.getConnection
 Statement stmt = conn.createStatement();
 ResultSet rs = stmt.executeQuery("select * from abc");
  out.println(rs.getString("def") + "<br>");

If you chose to use named pipes the Connection statement would look like:

Connection conn = DriverManeger.getConnection

To test your JSP pages you can also use the DSN. Two lines have to be modified in the above JSP code. First the jdbc-odbc bridge driver has to be loaded into memeory and the connection object should be created using the correct parameters:

Connection conn = DriverManager.getConnection("jdbc:odbc:prdn","prdn","prdn");

Not to anger anybody or wage a war, but we've found that our jsp pages used with these jdbc drivers with pooled connections are way faster than ASP pages. :)