Hi,

Please tell me the table structure and other details. I will provide you the example tomorrow.

Regards
Deepak Kumar

Thank You Deepak for your quick response.

Like I mentioned, I have an access database. This particular operation is only affecting the customers table which has the following fields:

Username (PK)
Firstname
Lastname
Location (FK)
Phonenumber
Physical address
CreditCardNumber
DrivingLicenceNumber

When registering online, they supply these details through a webservice. Let me know if you need further details. Again, am very grateful for you help!

Kind Regards,

HI Deepak, my sincere apologies for sounding so desperate maybe I am and you not the one to blame.

Just want to find out if by any chance you had time to look into my problem.

Than you once again for your help.

Kind Regards,

Mwamba.

Hi Deepak,

U though we could start from somewhere so I followed one of the tutorials I found on this awesome site. I followed it quite literally and went through all the processes and procedures. However I was an unable to insert the record I had created.

I have appended the results I got.



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

package pack1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.annotation.Resource;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;
import javax.sql.DataSource;

/**
*
* @author Ba Mwamba
*/
@WebService()
public class customer {
@Resource(name = "customer")
private DataSource customer;

/**
* Web service operation
*/
@WebMethod(operationName = "Create")
public String Create(@WebParam(name = "username")
String username, @WebParam(name = "drivinglicence")
String drivinglicence, @WebParam(name = "creditcard")
String creditcard, @WebParam(name = "firstname")
String firstname, @WebParam(name = "lastname")
String lastname, @WebParam(name = "physicaladdress")
String physicaladdress, @WebParam(name = "phonenumber")
String phonenumber, @WebParam(name = "password")
String password, @WebParam(name = "location")
String location, @WebParam(name = "Registrationdate")
String Registrationdate) {
//TODO write your implementation code here:
String status = "Record not inserted";
try
{
Connection con= customer.getConnection();
PreparedStatement ps=con.prepareStatement("INSERT INTO Customer VALUES(?,?,?,?,?,?,?,?,?,?)");
ps.setString(1, username);
ps.setString(2, drivinglicence);
ps.setString(3, creditcard);
ps.setString(4, firstname);
ps.setString(5, lastname);
ps.setString(6, physicaladdress);
ps.setString(7, phonenumber);
ps.setString(8, password);
ps.setString(9, location);
ps.setString(10, Registrationdate);
int i = ps.executeUpdate();
if(i!=0)
{
status="Record Inserted";
}
}catch(Exception e){
System.out.println("Error in storing data"+e);
}
return status;
}

}




Results from the above operation:





create Method invocation


Method parameter(s)

Type Value
java.lang.String mwamba
java.lang.String 123456789
java.lang.String 98765443
java.lang.String mwamba
java.lang.String chishimba
java.lang.String 48/50A Makeni
java.lang.String 098765433
java.lang.String mwamba
java.lang.String L002
java.lang.String 10/11/2009
Method returned

java.lang.String : "Record not inserted"
SOAP Request

<?xml version="1.0" encoding="UTF-8"?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">;
<S:Header/>
<S:Body>
<ns2:Create xmlns:ns2="http://pack1/">;
<username>mwamba</username>
<drivinglicence>123456789</drivinglicence>
<creditcard>98765443</creditcard>
<firstname>mwamba</firstname>
<lastname>chishimba</lastname>
<physicaladdress>48/50A Makeni</physicaladdress>
<phonenumber>098765433</phonenumber>
<password>mwamba</password>
<location>L002</location>
<Registrationdate>10/11/2009</Registrationdate>
</ns2:Create>
</S:Body>
</S:Envelope>
SOAP Response

<?xml version="1.0" encoding="UTF-8"?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">;
<S:Body>
<ns2:CreateResponse xmlns:ns2="http://pack1/">;
<return>Record not inserted</return>
</ns2:CreateResponse>
</S:Body>
</S:Envelope>


Where I am going wrong?

Kind Regards,

Hi,

Please check the complete tutorial at http://roseindia.net/webservices/web-services-database.shtml

Let me know if you face any problem.

Regards
Deepak Kumar

Thank you Deepak am sure this will be very helpful.

Just a quick question. I already have a connection to my database through a DAO class which is controlling the system interaction with the database and it is working just fine. Do I still need to create the connection in the webservice class as you are suggesting.
here is the code from my dao class:

import java.sql.*;
public class DAO
{
Connection con=null;
Statement st=null;
ResultSet rs=null;
public DAO()
{

try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url= new String("jdbc:odbc:CarHire");
con=DriverManager.getConnection(url,"","");
st=con.createStatement();

}
catch(Exception e)
{
System.out.print("Error:" + e);
}
}

with this dao in place all am doing is call the dao from my my servlets and pass appropriate parameters to it and it does what i have defined in the dao class.

I have tried to call the dao in the webservice class but gives an error owing to the fact that it's in another package.

is it possible for me to use this connection i have already defined or do i need to create one specifically for the webservice?

Kind Regards,

Mwamba.

Hi Deepak,

I want to sincerely thank you for your kindness and patience you shuold towards me!! Trust me, I can't thank you enough for what you have done for me.

So the code did manage to work but only after I changed from the preparedstatement to just statement and it worked perfectly well.

May God continue to prosper you in all what you do and I hope we can be "programming friends :)".

thank you one again!!

with kind regards,

Mwamba Chishimba.

Hi All,

This is an interesting post which is more useful for me as well. I need to show the results on UI by calling the service which interacts with the DB. The results are Name, ID, Address, Contact Number etc. Someone please provide me the same Axis2 service.

Best Regards Varma