How to connnect to MS SQL database and read data

christer777 · Jul 22, 2009

Hello,

How would I connect to a MS SQL 2005 database and read data from this database and then display on the JSP page? Actually took a college course five years ago and I can't remember much. I need the plain and stupid instructions how to do it. I know you should use servlets, but not interested and the webpage is only an internal application. I also read about JSTP or whatever it called, but that's seems like too much hassle anyway. I am not looking to relearn Java and the application is running on JRUN and I don't want to mess with it too much as the original programmer is long gone. I just need to read a few fields from a database. I would appreciate any help. Thanks, CHris

prosper · Jul 22, 2009

http://www.java-samples.com/showtutorial.php?tutorialid=619

christer777 · Jul 24, 2009

Hi,

Giving me an error: Unhandled exception thrown from /test9.jsp:8

Which is caused by this line: Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = java.sql.DriverManager.getConnection(url, id, pass);

I am running. Can I use DSN instead and if what is the correct format?

<html>
<head><title>Enter to database</title></head>
<body>
<table>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*" %>

<%

java.sql.Connection con;
java.sql.Statement s;
java.sql.ResultSet rs;
java.sql.PreparedStatement pst;

con=null;
s=null;
pst=null;
rs=null;

// Remember to change the next line with your own environment
String url=
"jdbc:jtds:sqlserver://192.168.1.22/ARP";
String id= "sa";
String pass = "password";
//try{

Class.forName("net.sourceforge.jtds.jdbc.Driver");
con = java.sql.DriverManager.getConnection(url, id, pass);

//}catch(ClassNotFoundException cnfex){
//cnfex.printStackTrace();

//}
String sql = "select top 10 * from UserInf";
try{
s = con.createStatement();
rs = s.executeQuery(sql);
%>

<%
while( rs.next() ){
%><tr>
<td><%= rs.getString("cust_id") %></td>
<td><%= rs.getString("rdate") %></td>
<td><%= rs.getString("email") %></td>
</tr>
<%
}
%>

<%

}
catch(Exception e){e.printStackTrace();}
finally{
if(rs!=null) rs.close();
if(s!=null) s.close();
if(con!=null) con.close();
}

%>

</body>
</html>

Diancecht · Jul 27, 2009

Maybe you should start with a regular Java program to connect and after that go to a JSP.

Which problem are you getting? It should appear in the log of the container you're using. Is the driver jar in the classpath?

Cheers,
Dian

prosper · Jul 27, 2009

use JRun Management Console (JMC) by
typing

http://localhost:8000/

to login JMC.
After you have started the default server on port 8100, add JNDI in resources function of JMC.
The following is Data Source Settings
JNDI Name=mssql
Driver Class Name=net.sourceforge.jtds.jdbc.Driver
URL=jdbc:jtds:sqlserver://127.0.0.1:1433/pubs;TDS=7.0
Description=connect to MSSQL by jdbc
Make sure Pool Connections and Pool Statements options are checked
Enter the user name and password of the database in MSSQL7

Code:

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
...
      public int getMaxOrMinID(String sqlStr)
             {
              String result="";
              try {
		   InitialContext ctx = new InitialContext();
                   DataSource ds = (DataSource) ctx.lookup(myJNDI);
                   Connection con=ds.getConnection();               

                   Statement stmt = con.createStatement ();
                   ResultSet rset = stmt.executeQuery (sqlStr);
                   while (rset.next ())
                         {
                          result = rset.getString (1);
                         }
	               stmt.close();
                   con.close();
                   
                   return Integer.parseInt(result); 	    
                  } 
              catch(NumberFormatException nfe) {
                    return 0; 
                                     }

              catch(SQLException ex) {
                    System.err.println("getMaxOrMinID==> SQLException: ");
	                while (ex != null) {
		            System.out.println("Message:   " + ex.getMessage ());
		            System.out.println("SQLState:  " + ex.getSQLState ());
		            System.out.println("ErrorCode: " + ex.getErrorCode ());
		            ex = ex.getNextException();
	                                   }
                     return 0; 
                                     }
              catch (javax.naming.NamingException ne){
                     System.out.println("namingException in getMaxOrMinID");
                     return 0; 
                    }

             }

prosper · Jul 27, 2009

Forget about this line:
Enter the user name and password of the database in MSSQL7

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to connnect to MS SQL database and read data

christer777

IS-IT--Management

prosper

Programmer

christer777

IS-IT--Management

Diancecht

Programmer

prosper

Programmer

prosper

Programmer

Similar threads

Part and Inventory Search

Sponsor