Connection with database Help

Can anyone help with this code please originally. I want to connect to SQL Server 2000 database I already installed the driver and set the classpath for it + put the jar file inside tomcat and I am testing using MS ACcess dataabse.

I am trying to connect to A SQL Server database with a login name and password and to change my username and password through JSP.

The MSAccess database code

import java.sql.*;

public class ClassConnect{
Connection conn;
Statement stmt ;
ResultSet rs;

//setup the database connection here, once you change you username and passwd,
//you do not need to change it for any table
public static void main(String [] args){
public void DbInit(){

try{
Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/* change user name and password here */
String url = "jdbc

dbc:Usuario";
//conn = DriverManager.getConnection( url, "eloi.teixeira","eloi197525" );
conn = DriverManager.getConnection("jdbc.Odbc.DRIVER=Microsoft Access Driver (*.mdb);DBQ=c:/Usuario.mdb");
System.out.println("ok");

stmt = conn.createStatement();
}//try
catch(SQLException e){System.out.println("ok");}
}//dbInit
}//main

public boolean updateTable(String update_sql){
PreparedStatement ps = null;
try {
ps=conn.prepareStatement(update_sql);
ps.executeUpdate();
} catch (SQLException e) { return false; }
return true;
}
public ResultSet queryTable(String query_sql){
try{
Statement stmt = conn.createStatement();
return stmt.executeQuery(query_sql);
}catch(SQLException e){return null;}
}
public void DbClose(){
try{
conn.close();
}
catch(SQLException e){}
}
}

This is to connect with SQL Server:

import java.sql.*;
import java.util.*;

public class ClassConnect{
Connection conn;
Statement stmt ;
ResultSet rs;

public void DbInit(){
try{
DriverManager.registerDriver(com.microsoft.jdbc.sqlserver.SQLServeR);

String url = "com.jdbc:microsoft:sqlserver:http:\\localhost:8080

atabasename:tablename";
conn = DriverManager.getConnection( url, "username","password" );

stmt = conn.createStatement();
}catch(SQLException e){}
}

public boolean updateTable(String update_sql){
PreparedStatement ps = null;
try {
ps=conn.prepareStatement(update_sql);
ps.executeUpdate();
} catch (SQLException e) { return false; }
return true;
}
public ResultSet queryTable(String query_sql){
try{
Statement stmt = conn.createStatement();
return stmt.executeQuery(query_sql);
}catch(SQLException e){return null;}
}
public void DbClose(){
try{
conn.close();
}
catch(SQLException e){}
}
}

The Jsp file is:

<%@ page import = "java.sql.*, ClassConnect" %>
<jsp:useBean id="myCBean" class="ClassConnect" scope="session"/>
<html>
<head><title>JDBC</title></head>
<body>
<% myCBean.DbInit(); %>
Database connection is initialized successfully! <hr>

<% } %>
<% if (myCBean.updateTable("update WIZUSUARIOS set valor = eloi.campos where UID = 'eloi.teixeira' ")) { %>
Insert data into the new table successfully!<hr>
<% } else { %>
Cannot insert data into wizusuarios table!<br>
<% } %>
<% if (myCBean.updateTable("Update WIZUSUARIOS set valor = eloi197529 where PWD = 'eloi197525' ")) { %>
Insert data into the new table successfully!<hr>
<% } else { %>
Cannot insert data into the table!<br>
<% } %>

<% ResultSet rs = myCBean.queryTable("SELECT * FROM tablename"); %>

<% while (rs.next()){ %>
<%= rs.getInt(1) %>
<%= rs.getString(2) %>

<% } %>
<hr>
<% myCBean.DbClose(); %>
Database connection is closed!
</body>
</html>

Any help would be great.

Thanks

Are you getting any exception out of this? If yes what is that? Post the error description.

Hi

On the MS Code I am getting funny says the String error on token "(",";" expected.

I alredy setup the dns name with the database name Usuario.

import java.sql.*;

public class ClassConnect{
Connection conn;
Statement stmt ;
ResultSet rs;

//setup the database connection here, once you change you username and passwd,
//you do not need to change it for any table
public static void main(String [] args){
public void DbInit(){

try{
Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/* change user name and password here */
String url = "jdbc

dbc:Usuario";
//conn = DriverManager.getConnection( url, "eloi.teixeira","eloi197525" );
conn = DriverManager.getConnection("jdbc.Odbc.DRIVER=Microsoft Access Driver (*.mdb);DBQ=c:/Usuario.mdb");
System.out.println("ok");

stmt = conn.createStatement();
}//try
catch(SQLException e){System.out.println("ok");}
}//dbInit
}//main

public boolean updateTable(String update_sql){
PreparedStatement ps = null;
try {
ps=conn.prepareStatement(update_sql);
ps.executeUpdate();
} catch (SQLException e) { return false; }
return true;
}
public ResultSet queryTable(String query_sql){
try{
Statement stmt = conn.createStatement();
return stmt.executeQuery(query_sql);
}catch(SQLException e){return null;}
}
public void DbClose(){
try{
conn.close();
}
catch(SQLException e){}
}
}

What is wrong??

This is to access SQL Server 2000 with my username and password and to update the username and password. My driver name is MS SQL Server 2000 driver for JDBC and is installed on c:\ I have already copy the jar files to my tomcat directory + set up the class path.

I am getting an error saying
The import java.sql.*; is never used and
Unhandled exception type IllegalAccessException
Unhandled exception type InstatiationException
on
Class.forName("com.microsoft.jdbc.mssqlserver.SQLServerDriver").newInstance()

My SQL Server code is

package dbConnect;
import java.sql.*;
import java.util.*;

public class ClassConnect{
Connection conn;
Statement stmt ;
ResultSet rs;

public void DbInit() throws SQLException{
try{
//DriverManager.registerDriver(new Driver());
Class.forName("com.microsoft.jdbc.mssqlserver.SQLServerDriver").newInstance();

String url = "com.jdbc:microsoft:sqlserver:http:\\localhost:8080:SAC:WIZUSUARIOS";
conn = DriverManager.getConnection( url, "eloi.teixeira","eloi197525" );

stmt = conn.createStatement();
}
catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
}

public boolean updateTable(String update_sql){
PreparedStatement ps = null;
try {
ps=conn.prepareStatement(update_sql);
ps.executeUpdate();
} catch (SQLException e) { return false; }
return true;
}
public ResultSet queryTable(String query_sql){
try{
Statement stmt = conn.createStatement();
return stmt.executeQuery(query_sql);
}catch(SQLException e){return null;}
}
public void DbClose(){
try{
conn.close();
}
catch(SQLException e){}
}
}

Thanks

I found in the readme file in the Driver the following!

Setup copies the following files and directories to INSTALL_DIR/lib:

mssqlserver.jar Microsoft SQL Server 2000 driver for JDBC and
DataSource classes
msbase.jar Classes used by the SQL Server driver
msutil.jar Classes used by the SQL Server driver

Installation copies the following files to INSTALL_DIR/SQLServer JTA:

instjdbc.sql File for installing JTA Stored Procedures
sqljdbc.dll File for use with JTA Stored Procedures

SO what is the name of the driver which have to be load??

In the ODBC Data Source Administrator I have under System data source the name of the database sac and The driver name SQL Server

So what have to got in the Class.forName("com.microsoft.jdbc.mssqlserver.SQLServerDriver").newInstance();
and in the

WHen I run as an Java apllication I get an error messsage in DriverManager.registerDriver(new com.microsoft.sqlserver.SQLServerDriver());

the error is : com.microsoft cannot be resolved or is not a type

and When I change to DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver()); runs but says No Suitable Driver

My code is this one to test my connection:
import java.sql.*;
public class TestarConnect{

public static void main(String [] args){

Connection conn;

try{

//DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

DriverManager.registerDriver(new com.microsoft.sqlserver.SQLServerDriver());
conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://ANDROMEDA;DATABASE=sac","eloi.teixeira","eloi197525");
System.out.println("Connection Successfull");

}
//catch(java.lang.ClassNotFoundException e , SQLException sqle) {

catch(SQLException e) {

//System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("VendorError: " + e.getErrorCode());
System.out.println("Connection unSuccessfull");

}
}
}