Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > java > answers

Web service connection

Sl1ver's Avatar
Member
  
Join Date: Mar 2009
Location: Cape Town, South Africa
Posts: 102
#1: Oct 5 '09
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's Avatar
Site Moderator
  
Join Date: Oct 2006
Location: The Great White North
Posts: 5,066
#2: Oct 6 '09

re: Web service connection

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's Avatar
Member
  
Join Date: Mar 2009
Location: Cape Town, South Africa
Posts: 102
#3: Oct 7 '09

re: Web service connection

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?
Expand|Select|Wrap|Line Numbers
  1. @WebService()
  2. public class Name {
  3.  
  4.     @Resource(name = "DB")
  5.     private DataSource DB;
  6.     String pass1 = "";
  7.     String mail = "";
  8.     String q = "";
  9.     String a = "";
  10.     Statement statement = null;
  11.     ResultSet rs = null;
  12.     int updateQuery = 0;
  13.     /**
  14.      * Web service operation
  15.      */
  16.     @WebMethod
  17.     public String hi(@WebParam(name = "name") String name, @WebParam(name = "sname") String sname) {
  18.  
  19.         try {
  20.  
  21.         Connection con = DB.getConnection();
  22.         statement = con.createStatement();
  23.         String query = "Select * from where CUST__NAME ="+name+"and CUST__SURNAME = "+sname;
  24.         rs = statement.executeQuery(query);
  25.  
  26.  
  27.              while(rs.next()){
  28.             pass1 = rs.getString("CUST__PASS1");
  29.             mail = rs.getString("CUST__MAIL");
  30.             q = rs.getString("CUST__QUESTION");
  31.             a = rs.getString("CUST__ANSWER");
  32.  
  33.         }
  34.             rs.close();
  35.             statement.close();
  36.             con.close();
  37.         } catch (SQLException ex) {
  38.             ex.printStackTrace();
  39.         }
  40.  
  41.  
  42.         return "Hello "+name+" "+sname+ " Your password is "+pass1+ " and your e-mail is "+mail + ". Yout question is "+q+" and your answer is "+a;
  43.  
  44.     }
  45.  
  46. }
  47.  
Frinavale's Avatar
Site Moderator
  
Join Date: Oct 2006
Location: The Great White North
Posts: 5,066
#4: Oct 7 '09

re: Web service connection

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:

Expand|Select|Wrap|Line Numbers
  1. String query = "Select * from TableName where CUST__NAME ="+name+"and CUST__SURNAME = "+sname;
Sl1ver's Avatar
Member
  
Join Date: Mar 2009
Location: Cape Town, South Africa
Posts: 102
#5: Oct 8 '09

re: Web service connection

i tried now
Expand|Select|Wrap|Line Numbers
  1. @WebMethod
  2.     public String hi(@WebParam(name = "name") String name, @WebParam(name = "sname") String sname) {
  3.  
  4.         try {
  5.  
  6.         Connection con = DriverManager.getConnection ("jdbc:odbc:Antique");
  7.         statement = con.createStatement();
  8.         String query = "Select * from CUSTOMER where CUST__NAME ="+name+"and CUST__SURNAME = "+sname;
  9.         rs = statement.executeQuery(query);
  10.  
  11.  
  12.              while(rs.next()){
  13.             pass1 = rs.getString("CUST__PASS1");
  14.             mail = rs.getString("CUST__MAIL");
  15.             q = rs.getString("CUST__QUESTION");
  16.             a = rs.getString("CUST__ANSWER");
  17.  
  18.         }
  19.             rs.close();
  20.             statement.close();
  21.             con.close();
  22.         } catch (SQLException ex) {
  23.             ex.printStackTrace();
  24.         }
  25.  
  26.  
  27.         return "Hello "+name+" "+sname+ " Your password is "+pass1+ " and your e-mail is "+mail + ". Yout question is "+q+" and your answer is "+a;
  28.  
  29.     }
  30.  
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's Avatar
Site Moderator
  
Join Date: Oct 2006
Location: The Great White North
Posts: 5,066
#6: Oct 8 '09

re: Web service connection

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

Expand|Select|Wrap|Line Numbers
  1. "Select * from CUSTOMER where CUST__NAME ='"+name+"' and CUST__SURNAME = '"+sname+"'";
Sl1ver's Avatar
Member
  
Join Date: Mar 2009
Location: Cape Town, South Africa
Posts: 102
#7: Oct 8 '09

re: Web service connection

changed the sql statment, driver is connected to db and still doesn't work
even changed the coding
Expand|Select|Wrap|Line Numbers
  1.  @WebMethod
  2.     public String hi(@WebParam(name = "name") String name, @WebParam(name = "sname") String sname) {
  3.  
  4.         getDBInfo(name,sname);
  5.         return qt;
  6.  
  7.     }
  8.  
  9.     public void getDBInfo(String name,  String sname)
  10.     {
  11.         try {
  12.  
  13.         Connection con = DriverManager.getConnection ("jdbc:odbc:Antique","","");
  14.         statement = con.createStatement();
  15.         String query = "Select * from CUSTOMER where CUST__NAME ='"+name+"' and CUST__SURNAME = '"+sname+"'";
  16.         rs = statement.executeQuery(query);
  17.  
  18.  
  19.             while(rs.next()){
  20.             pass1 = rs.getString("CUST__PASS1");
  21.             mail = rs.getString("CUST__MAIL");
  22.             q = rs.getString("CUST__QUESTION");
  23.             a = rs.getString("CUST__ANSWER");
  24.  
  25.         }
  26.             con.close();
  27.         } catch (SQLException ex) {
  28.             ex.printStackTrace();
  29.         }
  30.  
  31.         qt = "Hello "+name+" "+sname+ " Your password is "+pass1+ " and your e-mail is "+mail + ". Yout question is "+q+" and your answer is ";
  32.  
  33.     }
  34.  
Sl1ver's Avatar
Member
  
Join Date: Mar 2009
Location: Cape Town, South Africa
Posts: 102
#8: Oct 8 '09

re: Web service connection

and its all to do with the connection adn sql, coz it still returns all of the string except the sql parts
Frinavale's Avatar
Site Moderator
  
Join Date: Oct 2006
Location: The Great White North
Posts: 5,066
#9: Oct 8 '09

re: Web service connection

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's Avatar
Member
  
Join Date: Mar 2009
Location: Cape Town, South Africa
Posts: 102
#10: Oct 8 '09

re: Web service connection

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's Avatar
Member
  
Join Date: Mar 2009
Location: Cape Town, South Africa
Posts: 102
#11: Oct 8 '09

re: Web service connection

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

« previous question | next question »

Similar Java bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,272 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved