plz help

[aparnasofte]

hi 'm aparna.

Am developing a web application with j2ee and SQL server 2005 EXPRESS EDITION as the back end. When i connect it with JDBC , my connection turns out to be successful. WHen i run the application, the web page is openin, but the problem is values are not getting inserted or retrieved from the db..

plz someone help me out.

[masijade]

Code and exceptions would help.

[pakidesigner]

wish i could see code also arpana coz only that way anyone can help ya.

[aparnasofte]

i have attached one part of my stateless session bean code..

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package forum;

import com.sun.jmx.trace.Trace;
import javax.ejb.Stateless;
import java.util.*;
import java.sql.*;


/**
*
* @author Arthi
*/
@Stateless
public class MembersBean implements MembersLocal {
private int memberid;
private String username, password, firstname, lastname, email, regdate, type;

public int getMemberid() {
return memberid;
}

public void setMemberid(int memberid) {
this.memberid = memberid;
}

public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}

public String getPassword() {
return username;
}

public void setPassword(String password) {
this.password = password;
}

public String getFirstname() {
return firstname;
}

public void setFirstname(String firstname) {
this.firstname = firstname;
}

public String getLastname() {
return lastname;
}

public void setLastname(String lastname) {
this.lastname = lastname;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getRegdate() {
return regdate;
}

public void setRegdate(String regdate) {
this.regdate = regdate;
}

public String getType() {
return type;
}

public void setType(String type) {
this.type = type;
}

public boolean checkReg(String username) {
String query = "SELECT member_id FROM Members WHERE [username]='" + username + "'";

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
ResultSet rst = stat.executeQuery(query);
System.out.println(con);
if(rst.next() == false) {
con.close();
return true;
}
else {
con.close();
return false;
}
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

public boolean authenticate() {
String query = "SELECT * FROM Members";
String DbUsername = "admin ";
String DbPassword = "admin";

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
ResultSet rst = stat.executeQuery(query);
System.out.println("connec"+con);

while(rst.next()) {
//DbUsername = rst.getString("username");
//DbPassword = rst.getString("password");

System.out.println ("DBusername"+ rst.getString("username"));
System.out.println ("DBpassword"+rst.getString("password"));
System.out.println ("username"+username);
System.out.println ("password"+password);
if(username.equals(DbUsername) && password.equals(DbPassword)) {
con.close();
return true;
}
}
return false;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

public boolean regMember() {
memberid = (int) (1000 + Math.random()* 1000);
firstname = "";
lastname = "";
email = "";
regdate = new java.util.Date().toString();
type = "Member";

String query = "INSERT INTO Members (member_id, [username], [password], [firstname], [lastname], [email], [regdate], [type]) " +
"VALUES (" + memberid + ", " +
"'" + username + "', " +
"'" + password + "', " +
"'" + firstname + "', " +
"'" + lastname + "', " +
"'" + email + "', " +
"'" + regdate + "', " +
"'" + type + "')";
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
stat.execute(query);
System.out.println(con);
con.close();
return true;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

public boolean setMember(String username) {
String password_upd = "";

if(password != null) {
password_upd = "password='" + password + "', ";
}

String query = "UPDATE Members SET " + password_upd +
"firstname='" + firstname + "', " +
"lastname='" + lastname + "', " +
"email='" + email + "' WHERE [username]='" + username + "'";

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
stat.execute(query);
System.out.println(con);
con.close();
return true;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

public boolean setMember(int memberid) {
String password_upd = "";

if(password != null) {
password_upd = "password='" + password + "', ";
}

String query = "UPDATE Members SET " + password_upd +
"username='" + username + "', " +
"firstname='" + firstname + "', " +
"lastname='" + lastname + "', " +
"email='" + email + "', " +
"type='" + type + "' WHERE member_id=" + memberid;

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
stat.execute(query);
System.out.println(con);
con.close();
return true;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

public boolean getMember(String username) {
String query = "SELECT * FROM Members WHERE [username]='" + username + "'";

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
ResultSet rst = stat.executeQuery(query);
System.out.println(con);
while(rst.next()) {
memberid = rst.getInt("member_id");
this.username = rst.getString("username");
firstname = rst.getString("firstname");
lastname = rst.getString("lastname");
email = rst.getString("email");
regdate = rst.getString("regdate");
type = rst.getString("type");
}
con.close();
return true;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

public boolean getMember(int memberid) {
String query = "SELECT * FROM Members WHERE member_id=" + memberid;

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
ResultSet rst = stat.executeQuery(query);
System.out.println(con);
while(rst.next()) {
this.memberid = rst.getInt("member_id");
username = rst.getString("username");
firstname = rst.getString("firstname");
lastname = rst.getString("lastname");
email = rst.getString("email");
regdate = rst.getString("regdate");
type = rst.getString("type");
}
con.close();
return true;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

public int getNumPosts(int memberid) {
int numMsgs = 0;
String query = "SELECT thread_id FROM Threads WHERE member_id=" + memberid;

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
ResultSet rst = stat.executeQuery(query);
System.out.println(con);
while(rst.next()) {
numMsgs++;
}
con.close();
}
catch(Exception e) {
e.printStackTrace();
}
return numMsgs;
}

public List getMembers() {
List members = new LinkedList();
String query = "SELECT member_id FROM Members ORDER BY member_id";

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
ResultSet rst = stat.executeQuery(query);
System.out.println(con);
while(rst.next()) {
memberid = rst.getInt("member_id");
members.add(new Integer(memberid));
}

con.close();
}
catch(Exception e) {
e.printStackTrace();
}
return members;
}

public List getMods() {
List members = new LinkedList();
String query = "SELECT member_id FROM Members WHERE type='Administrator' OR type='Moderator' ORDER BY member_id";

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
ResultSet rst = stat.executeQuery(query);
System.out.println(con);
while(rst.next()) {
memberid = rst.getInt("member_id");
members.add(new Integer(memberid));
}

con.close();
}
catch(Exception e) {
e.printStackTrace();
}
return members;
}

public boolean delMember(int memberid) {
String query = "DELETE FROM Members WHERE member_id=" + memberid;

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1269;DatabaseName=Forum");
Statement stat = con.createStatement();
stat.execute(query);
System.out.println(con);
con.close();
return true;
}
catch(Exception e) {
e.printStackTrace();
return false;
}
}

// Add business logic below. (Right-click in editor and choose
// "Insert Code > Add Business Method" or "Web Service > Add Operation")

}

[masijade]

And the errors?

[aparnasofte]

am not getting any errors, but the values are not getting inserted or retrieved from the database

[masijade]

First, and foremost, change those cobbled together SQL queries with Statement to PreparedStatements (see the tutorial on PreparedStatements).

Secondly, if no error is occurring, than I can only assume the where clause is not matching anything (in the updates).

Other than that, try printing out the query and examining it, and executing it manually, to analyse further.

Also, use executeUpdate, rather than execute, and capture the returned int and print it out, that int lists the number of affected rows. If it is 0, then the updates where clause didn't match (for the updates).

[aparnasofte]

k thanks a lot.. :) will try that... actually i read somewhere tat SQL 2005 Express edition shouldn't b used, v hav to use only developer edition... is that true??

[masijade]

I wouldn't expect it to be. Don't have clue. I can only suggest checking out the MS sites.

[aparnasofte]

k thank u :)

[sandipdandel]

hi ,ur using SQL database and still ur trying for JDBC drivers pls
change the drivers bcoz JDBC supports only to the MS Access.