web service access database problem

[timaquerra]

Hello, I created a web service to access oracle database. This is my code:

package mypack;

import java.util.List;
import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import java.sql.*;
import java.util.ArrayList;

@WebService()
public class AutoRepairWS {


    @WebMethod
    public List getCustomerId(@WebParam(name = "Cust_Id") String Cust_Id){
        String user = "****";
        String password = "****";
        String url = "jdbc:oracle:thin:***:1521:xe";
        String OracleDriver = "oracle.jdbc.OracleDriver";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        List value = new ArrayList();

        try{
            
            conn = DriverManager.getConnection(url, user, password);
            pstmt = conn.prepareStatement("select Cust_fname from Customers where Cust_Id = '" + Cust_Id + " '");

            pstmt.setString(1, Cust_Id);

            rs = pstmt.executeQuery();

            while(rs.next()){
                value.add(rs.getString("Cust_fname"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return value;
    }
}

The problem is that it's supposed to return the value of Cust_fname, but i'm getting following message when I test web service:

java.util.List : "[]"

Can you help to make my program! Thanks in advance!

[masijade]

Provide the full stack trace please. That message doesn't help at all, and it doesn't even seem to be produced by this piece of code.

[javaAddict]

First of all learn the difference between PreparedStatements and Statements.

You initialize the prepareStatement with a fixed query (wrong) and then you call this: pstmt.setString(1, Cust_Id); ????
You don't have an argument at index 1 at the query. Where the (1, Cust_Id) will be applied????

And if we assume that you keep your query the way it is and you don't assign a parameter since you already have one: Cust_Id = '" + Cust_Id + " '" Did you notice the space at the last set of quotes: Cust_Id = '" + Cust_Id + [B]"[/B] '[B]"[/B] PS. The question marks at the first part of the post aren't there for fun. They are a hint.

[timaquerra]

Thank you guys for your reply, and I'm very beginner so I don't know much about this. I fixed my code and now I have following:

package mypack;

import java.util.List;
import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import java.sql.*;
import java.util.ArrayList;

@WebService()
public class AutoRepairWS {


    @WebMethod
    public List getCustomerName(@WebParam(name = "Cust_Id") String Cust_Id){
        String user = "****";
        String password = "****";
        String url = "****:1521:xe";
        String OracleDriver = "oracle.jdbc.OracleDriver";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        List value = new ArrayList();

        try{

            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            String sql = "select Cust_fname from Customers where Cust_Id=" + Cust_Id;

            rs = stmt.executeQuery(sql);

            while(rs.next()){
                value.add(rs.getString("Cust_fname"));
            }

        }catch(SQLException e){
            e.printStackTrace();
        }
        return value;
    }
}

[masijade]

You were much better off with a PreparedStatement, you just need to do it right

pstmt = conn.prepareStatement("select Cust_fname from Customers where Cust_Id = ?");

Edit: javaAddict attempted to tell you this obliquely, but you seemingly didn't take the hint.

There are always the tutorials though
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

[~s.o.s~]

Oh and BTW, once you are done with understanding how web services work, try splitting your logic in layers like the business[model] layer, database[persistence] layer etc. Database access from a web method is err... bad. :)