Web service connection

[Sl1ver]

Figured out how the web service interacts with a jsp page. now i just want to know. in the web service when connection to a db it creates a datasource using the jdbc driver that you specified. How do you use that @datasource to connect to the db and interact with the db. (using netbeans 5.5) its a project so i cant use anything else

[Frinavale]

What?

Are you talking about maybe providing a different connection string in order to connect to the database?

Maybe based on user provided credentials?

[Sl1ver]

Ok, i got it so that if i typed my name and surname in two boxes, it calls the web service and then return Hello "Jonathan" "Meyer". Now in that exact piece of code i made it connect to the database and return all information associated to me(compares my name and surname in the sql where clause). here is my code, What am i doing wrong, and is there any alternative to using the jdbc driver that netbeans creates in the web service?

@WebService()
public class Name {
 
    @Resource(name = "DB")
    private DataSource DB;
    String pass1 = "";
    String mail = "";
    String q = "";
    String a = "";
    Statement statement = null;
    ResultSet rs = null;
    int updateQuery = 0;
    /**
     * Web service operation
     */
    @WebMethod
    public String hi(@WebParam(name = "name") String name, @WebParam(name = "sname") String sname) {
 
        try {
 
        Connection con = DB.getConnection();
        statement = con.createStatement();
        String query = "Select * from where CUST__NAME ="+name+"and CUST__SURNAME = "+sname;
        rs = statement.executeQuery(query);
 
 
             while(rs.next()){
            pass1 = rs.getString("CUST__PASS1");
            mail = rs.getString("CUST__MAIL");
            q = rs.getString("CUST__QUESTION");
            a = rs.getString("CUST__ANSWER");
 
        }
            rs.close();
            statement.close();
            con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
 
 
        return "Hello "+name+" "+sname+ " Your password is "+pass1+ " and your e-mail is "+mail + ". Yout question is "+q+" and your answer is "+a;
 
    }
 
}
 

[Frinavale]

Well it looks ok except for the way that you're getting your connection might be incorrect.

Please look at the DriverManager class specifically how to use the DriverManager's getConnection method.

Oh, and your SQL Select statement is wrong. You should be specifying the table to select from.

So Line 23 should look something like:

String query = "Select * from TableName where CUST__NAME ="+name+"and CUST__SURNAME = "+sname;

[Sl1ver]

i tried now

@WebMethod
    public String hi(@WebParam(name = "name") String name, @WebParam(name = "sname") String sname) {
 
        try {
 
        Connection con = DriverManager.getConnection ("jdbc:odbc:Antique");
        statement = con.createStatement();
        String query = "Select * from CUSTOMER where CUST__NAME ="+name+"and CUST__SURNAME = "+sname;
        rs = statement.executeQuery(query);
 
 
             while(rs.next()){
            pass1 = rs.getString("CUST__PASS1");
            mail = rs.getString("CUST__MAIL");
            q = rs.getString("CUST__QUESTION");
            a = rs.getString("CUST__ANSWER");
 
        }
            rs.close();
            statement.close();
            con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
 
 
        return "Hello "+name+" "+sname+ " Your password is "+pass1+ " and your e-mail is "+mail + ". Yout question is "+q+" and your answer is "+a;
 
    }
 

and it still doesn't work. it returns all of the strings plus what i type in, but it doesnt shoe the string its supposed to from the db?

[Frinavale]

Your select statement still isn't right.
I think it should be:

"Select * from CUSTOMER where CUST__NAME ='"+name+"' and CUST__SURNAME = '"+sname+"'";

[Sl1ver]

changed the sql statment, driver is connected to db and still doesn't work
even changed the coding

 @WebMethod
    public String hi(@WebParam(name = "name") String name, @WebParam(name = "sname") String sname) {
 
        getDBInfo(name,sname);
        return qt;
 
    }
 
    public void getDBInfo(String name,  String sname)
    {
        try {
 
        Connection con = DriverManager.getConnection ("jdbc:odbc:Antique","","");
        statement = con.createStatement();
        String query = "Select * from CUSTOMER where CUST__NAME ='"+name+"' and CUST__SURNAME = '"+sname+"'";
        rs = statement.executeQuery(query);
 
 
            while(rs.next()){
            pass1 = rs.getString("CUST__PASS1");
            mail = rs.getString("CUST__MAIL");
            q = rs.getString("CUST__QUESTION");
            a = rs.getString("CUST__ANSWER");
 
        }
            con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
 
        qt = "Hello "+name+" "+sname+ " Your password is "+pass1+ " and your e-mail is "+mail + ". Yout question is "+q+" and your answer is ";
 
    }
 

[Sl1ver]

and its all to do with the connection adn sql, coz it still returns all of the string except the sql parts

[Frinavale]

Well, have you taken the time to step through your application to see what's going on?

Are you getting any error messages?

Have you tested the SQL statement using a database tool before using it in your application (using a tool like Hiedi)?

-Frinny

[Sl1ver]

if i run the sql from netbeans , it returns all the data, i dont know how to step through it, isnt it a web service and doesn't allow for step through?

[Sl1ver]

in debug, it says, Connection con is not a know variable in current context, what does that mean?