aspose file tools*
The moose likes JDBC and the fly likes Connection with database Help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection with database Help" Watch "Connection with database Help" New topic
Author

Connection with database Help

campos teixeira
Ranch Hand

Joined: May 23, 2004
Posts: 51
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
Ali Gohar
Ranch Hand

Joined: Mar 18, 2004
Posts: 572
Are you getting any exception out of this? If yes what is that? Post the error description.
campos teixeira
Ranch Hand

Joined: May 23, 2004
Posts: 51
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??
campos teixeira
Ranch Hand

Joined: May 23, 2004
Posts: 51
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
campos teixeira
Ranch Hand

Joined: May 23, 2004
Posts: 51
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
campos teixeira
Ranch Hand

Joined: May 23, 2004
Posts: 51
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");

}
}
}
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection with database Help