Solved

JSP /Servlet DB question

Posted on 2007-03-30
21
1,495 Views
Last Modified: 2013-11-24
I am trying to create a jsp form that will take parameters from a sent request like this.

http://some.localhost.com/form.jsp?License%3D5555555&FirstName%3DTest&LastName%3DUser&State%3DAZ

I have the sending information finished what I need to a jsp / servlet to process this information and plug it into a DB2 database. I have found some very simple examples but can't get this to work. Any help on this would be great either show some code or point to where I can find examples to learn from. Thank you
0
Comment
Question by:jareddraper
  • 9
  • 6
  • 4
  • +2
21 Comments
 
LVL 15

Expert Comment

by:bpmurray
ID: 18827029
I think you'll find that if you actually use the URL correctly, it'll probably work. Don't encode what you don't need to encode, the URL should be:

            http://some.localhost.com/form.jsp?License=5555555&FirstName=Test&LastName=User&State=AZ

Why dod you encode the "="?
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 18827613
you need so show us the code that you have presently so that we can help you better... its fairly simple thing that you are trying here and almost all the examples about jsp/servlet would cover them..

just let us know which one you are trying and we should be able to help.
0
 

Author Comment

by:jareddraper
ID: 18827696
I will give that a try bpmurry. I would like to use a servlet for this operation.

kuldee I have the following information. The application I get the information here is part of the documentation. These are the fields I can get

[url-start]
url=https://www.mywebform.com
UserName=CSSNUSEDNAME
UserPwd=CSSNUSERPWD
License=CSSNLICENSEDATA
FirstName=CSSNFIRSTNAMEDATA
MiddleName=CSSNMIDDLENAMEDATA
LastName=CSSNLASTNAMEDATA
NameSuffix=CSSNNAMESUFFIXDATA
Address=CSSNADDRESSDATA
City=CSSNCITYDATA
State=CSSNSTATEDATA
Zip=CSSNZIPDATA
Issue=CSSNISSUEDATA
DateOfBirth=CSSNDOBDATA
Expire=CSSNEXPIREDATA
Class=CSSNCLASSDATA
Sex=CSSNSEXDATA
Height=CSSNHEIGHTDATA
Weight=CSSNWEIGHTDATA
EyesColor=CSSNEYESDATA
additional user specific data
[url-end]

This is how it paases the information

[url-start]
url=https://www.
Branch Number
LicenseNumber=
FirstName=
LastName=
Birth=CSSNDOBDATA
Issue=CSSNISSUEDATA
[url-end]

with a url of this

Then the application will send the following string to the web browser:
https://www.card-scanner.com/?Branch+Number+%3D+10&LicenseNumber%3D000 000
017&FirstName%3DLADY&LastName%3DLIBERTY&Birth%3D10-28-86&Issue%3D02-12-00
This string will be translated by the web form as:
Branch Number = 10
LicenseNumber=000 000 017
FirstName=LADY
LastName=LIBERTY
Birth=10-28-86
Issue=02-12-00

Then I want to take the information and insert it into a db2 database via the servlet. Hope this information helps
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18828224
so what are you looking for ?
an example of openning a connection with db2 and performing an insert ?
0
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 250 total points
ID: 18828287
okay so your doGet method in your servlet should look something like this:
First of all fix your URL to be correct without encoding the &, = & ? signs as bpmurry has suggeseted.
Then try the following code. You will need the IBM db2 jdbc driver in your classpath for the following code to run. Also I have written the code right here, so there is no gaurntee that I have not made any syntax errors here..:-)
public void doGet(HttpServletRequest rq, HttpServletResponse rs)
{
try{
DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());
Connection conn=DriverManager.getConnection("jdbc:db2@yourdb","user", "pwd");
PreparedStatement ps = conn.preparedStatement("insert into tablename values ?,?,?,?,??);
ps.setString(1,rq.getParameter("Branch Number");
ps.setString(2,rq.getParameter("LicenseNumber");
ps.setString(3,rq.getParameter("FirstName");
ps.setString(4,rq.getParameter("LastName");
ps.setString(5,rq.getParameter("BirthDate");
ps.setString(6,rq.getParameter("issue");
ps.executeQuery();
ps.close()
}
catch(Exception e)
{
e.printStackTrace();
}
}
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 250 total points
ID: 18828313
couple of things you need to fix in the above code
if the db2 reside on a different server than the web server, you need to use a type 4 driver, so the connection should look like
Connection conn=DriverManager.getConnection("jdbc:db2://hostname:port/dbname","user", "pwd");
if the db2 server resides on the same machine as the webserver, the connection request will look like
Connection conn=DriverManager.getConnection("jdbc:db2:dbname","user", "pwd");
you should also change
PreparedStatement ps = conn.preparedStatement("insert into tablename values ?,?,?,?,??);
into
PreparedStatement ps = conn.preparedStatement("insert into tablename values (?,?,?,?,?,?)");

after ps.close() make sure you issue a commit and then you close the connection
inside the catch exception you should rollback

momi

0
 

Author Comment

by:jareddraper
ID: 18831171
Thank you two for the great answers I will be using both of them. The last thing is the url that is create I have no control over its created by a application we didn't write I can just adjust fields of information that are sent so the encoding is there by default is that going to affect the above code?

Thanks
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 18831208
>>sent so the encoding is there by default is that going to affect the above code?

Well you can try it and hope that it does not affects it..:-) if it does then we will have to workout something else....
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 18831373
Is it a Java application on the other side? Anyway regardless of whatever it is, have they made sure that they encode the URL using URLEncoder or something equivalent to it (in other languages) before sending the request?
0
 

Author Comment

by:jareddraper
ID: 18832799
that I am not sure of mayankeagle I am guessing its .net and don't have access to source I just have a data.txt file which I choose fields to send

[url-start]
url=https://www.mywebform.com
UserName=CSSNUSEDNAME
UserPwd=CSSNUSERPWD
License=CSSNLICENSEDATA
FirstName=CSSNFIRSTNAMEDATA
MiddleName=CSSNMIDDLENAMEDATA
LastName=CSSNLASTNAMEDATA
NameSuffix=CSSNNAMESUFFIXDATA
Address=CSSNADDRESSDATA
City=CSSNCITYDATA
State=CSSNSTATEDATA
Zip=CSSNZIPDATA
Issue=CSSNISSUEDATA
DateOfBirth=CSSNDOBDATA
Expire=CSSNEXPIREDATA
Class=CSSNCLASSDATA
Sex=CSSNSEXDATA
Height=CSSNHEIGHTDATA
Weight=CSSNWEIGHTDATA
EyesColor=CSSNEYESDATA
additional user specific data
[url-end]

which then constructs the url  
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:jareddraper
ID: 18832849
momi

How would I perform this

"after ps.close() make sure you issue a commit and then you close the connection
inside the catch exception you should rollback"

Thanks,
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18833076
public void doGet(HttpServletRequest rq, HttpServletResponse rs)
{
try{
DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());
Connection conn=DriverManager.getConnection("jdbc:db2@yourdb","user", "pwd");
PreparedStatement ps = conn.preparedStatement("insert into tablename values ?,?,?,?,??);
ps.setString(1,rq.getParameter("Branch Number");
ps.setString(2,rq.getParameter("LicenseNumber");
ps.setString(3,rq.getParameter("FirstName");
ps.setString(4,rq.getParameter("LastName");
ps.setString(5,rq.getParameter("BirthDate");
ps.setString(6,rq.getParameter("issue");
ps.executeQuery();
ps.close()
conn.commit()
}
catch(Exception e)
{
conn.rollback()
e.printStackTrace();
}
}
0
 

Author Comment

by:jareddraper
ID: 18833125
I am getting an error in Rational Web dev on this line of code

DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());

saying COM cannot be resovled type also I get an erro rwith this line as well

conn.rollback(); e.printStackTrace():

saying conn cannot be resolved. Here is a copy of my code

protected void doGet(HttpServletRequest rq, HttpServletResponse rs) throws ServletException, IOException {
            try{
                  
                  DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());
                  
                  Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50001/ARKDLS","user", "password");
                  PreparedStatement ps = conn.prepareStatement("insert into DB2INST1.DLDATA values (?,?,?,?,?,?,?)");
                  ps.setString(1,rq.getParameter("CompanyID"));
                  ps.setString(2,rq.getParameter("License"));
                  ps.setString(3,rq.getParameter("FirstName"));
                  ps.setString(4,rq.getParameter("LastName"));
                  ps.setString(5,rq.getParameter("Address"));
                  ps.setString(6,rq.getParameter("City"));
                  ps.setString(7,rq.getParameter("State"));
                  ps.executeQuery();
                  ps.close();
                  
                  conn.commit();
            }
            catch(Exception e)
            {
                  conn.rollback(); e.printStackTrace();
            }
      }

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18833166
try to change it to
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:db2:ARKDLS","user", "password");

this is when the database may reside on a different machine
or

      Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
    Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50001/ARKDLS","user", "password");

this is when the database is on the same machine, it has better performance relative to the previos method

0
 

Author Comment

by:jareddraper
ID: 18833375
Thank you momi for that information I know I am asking a lot of questions but I am so close now

this part is still giving me a fit

conn.rollback(); e.printStackTrace():

saying conn cannot be resolved. Here is a copy of my code

also what is the other way to connect to a database I seem to remember another way instead of Class.forName  but can't recall the syntax.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 18833485
its because we have declared the conn inside the try block & hence it will not be visible outside the try block...

chage the code to following.

Connection conn;
try {
   DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());
                 
                  Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50001/ARKDLS","user", "password");
                  PreparedStatement ps = conn.prepareStatement("insert into DB2INST1.DLDATA values (?,?,?,?,?,?,?)");
..
...
..
0
 

Author Comment

by:jareddraper
ID: 18834080
ok thank you Kuldee,

Looks like I have enough to get this thing working and I understand what it is doing. Now it seems well not simple but I can see what the code is suppose to do. I thank both Kuldee and Momi a ton for helping with this because of both of you I am able to figure it out. So I will divide the points up between you two. again thank both of you
0
 

Author Comment

by:jareddraper
ID: 18834122
Here is my final code but its not working

import java.io.IOException;
import java.sql.Connection;
// import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
// import java.sql.SQLException;

import javax.servlet.Servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class Simple extends HttpServlet implements Servlet {
      /*
       * (non-Java-doc)
       *
       * @see javax.servlet.http.HttpServlet#HttpServlet()
       */
      public Simple() {
            super();
      }
      
      /*
       * (non-Java-doc)
       *
       * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest arg0,
       *      HttpServletResponse arg1)
       */
      protected void doGet(HttpServletRequest rq, HttpServletResponse rs) throws ServletException, IOException {
            
            
            
            try{
                  
                  Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
                    Connection conn = DriverManager.getConnection("jdbc:db2://host.local.com:50001/ARKDLS","user", "password");
                  PreparedStatement ps = conn.prepareStatement("insert into DB2INST1.DLDATA values (?,?,?,?,?,?,?)");
                  ps.setString(1,rq.getParameter("CompanyID"));
                  ps.setString(2,rq.getParameter("License"));
                  ps.setString(3,rq.getParameter("FirstName"));
                  ps.setString(4,rq.getParameter("LastName"));
                  ps.setString(5,rq.getParameter("Address"));
                  ps.setString(6,rq.getParameter("City"));
                  ps.setString(7,rq.getParameter("State"));
                  ps.executeQuery();
                  ps.close();
                  
                  conn.commit();
            }
            catch(Exception e)
            {
             e.printStackTrace();
            }
      }
      /*
       * (non-Java-doc)
       *
       * @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest arg0,
       *      HttpServletResponse arg1)
       */
      protected void doPost(HttpServletRequest arg0, HttpServletResponse arg1)
      throws ServletException, IOException {
            // TODO Auto-generated method stub
      }
      
}

Weird I get this error any ideas?

[4/1/07 20:39:14:718 MDT] 00000030 SystemErr     R com.ibm.db2.jcc.c.SqlException: [ibm][db2][jcc][10103][10941] Method executeQuery cannot be used for update.
      at com.ibm.db2.jcc.c.fg.a(fg.java:2314)
      at com.ibm.db2.jcc.c.gg.d(gg.java:2037)
      at com.ibm.db2.jcc.c.gg.V(gg.java:424)
      at com.ibm.db2.jcc.c.gg.executeQuery(gg.java:407)
      at com.arkona.Simple.doGet(Simple.java:48)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1212)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:629)
      at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:80)
      at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1657)
      at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:421)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:367)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:276)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:201)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:103)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:548)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:601)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:934)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1021)
      at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))

[4/1/07 20:39:14:719 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.fg.a(fg.java:2314)
[4/1/07 20:39:14:719 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.gg.d(gg.java:2037)
[4/1/07 20:39:14:719 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.gg.V(gg.java:424)
[4/1/07 20:39:14:719 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.gg.executeQuery(gg.java:407)
[4/1/07 20:39:14:720 MDT] 00000030 SystemErr     R       at com.arkona.Simple.doGet(Simple.java:48)
[4/1/07 20:39:14:720 MDT] 00000030 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
[4/1/07 20:39:14:720 MDT] 00000030 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
[4/1/07 20:39:14:720 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1212)
[4/1/07 20:39:14:720 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:629)
[4/1/07 20:39:14:721 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:80)
[4/1/07 20:39:14:721 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1657)
[4/1/07 20:39:14:721 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
[4/1/07 20:39:14:721 MDT] 00000030 SystemErr     R       at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:421)
[4/1/07 20:39:14:722 MDT] 00000030 SystemErr     R       at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:367)
[4/1/07 20:39:14:722 MDT] 00000030 SystemErr     R       at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:276)
[4/1/07 20:39:14:722 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:201)
[4/1/07 20:39:14:722 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:103)
[4/1/07 20:39:14:723 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:548)
[4/1/07 20:39:14:723 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:601)
[4/1/07 20:39:14:723 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:934)
[4/1/07 20:39:14:723 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1021)
[4/1/07 20:39:14:724 MDT] 00000030 SystemErr     R       at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))


Here is the url that I am testing

http://localhost:9080/learn/servlet/simple?CompanyID=AR1&License=12345643&FirstName=Test&LastName=Users&Address=10747%20South%20Redwood%20Road&City=South%20Jordan&State=UT
0
 

Author Comment

by:jareddraper
ID: 18834230
Sorry more guess I closed this to soo if I could have it reopened that would be great. I now get this error

[4/1/07 21:12:33:043 MDT] 00000030 SystemErr     R com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -117, SQLSTATE: 42802, SQLERRMC: null
      at com.ibm.db2.jcc.c.fg.e(fg.java:1596)
      at com.ibm.db2.jcc.c.fg.a(fg.java:1206)
      at com.ibm.db2.jcc.b.gb.g(gb.java:140)
      at com.ibm.db2.jcc.b.gb.a(gb.java:39)
      at com.ibm.db2.jcc.b.w.a(w.java:34)
      at com.ibm.db2.jcc.b.vb.g(vb.java:139)
      at com.ibm.db2.jcc.c.fg.n(fg.java:1177)
      at com.ibm.db2.jcc.c.gg.eb(gg.java:1862)
      at com.ibm.db2.jcc.c.gg.d(gg.java:2295)
      at com.ibm.db2.jcc.c.gg.W(gg.java:457)
      at com.ibm.db2.jcc.c.gg.executeUpdate(gg.java:440)
      at com.arkona.Simple.doGet(Simple.java:48)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1212)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:629)
      at com.ibm.ws.webcontainer.extension.InvokerExtensionProcessor.handleRequest(InvokerExtensionProcessor.java:195)
      at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:2837)
      at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:220)
      at com.ibm.ws.webcontainer.VirtualHost.handleRequest(VirtualHost.java:204)
      at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1681)
      at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:421)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:367)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:276)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:201)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:103)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:548)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:601)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:934)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1021)
      at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))
---- Begin backtrace for Nested Throwables
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-117;42802;
      at com.ibm.db2.jcc.c.fg.e(fg.java:1596)
      at com.ibm.db2.jcc.c.gg.a(gg.java:1811)
      at com.ibm.db2.jcc.b.gb.a(gb.java:185)
      at com.ibm.db2.jcc.b.gb.b(gb.java:82)
      at com.ibm.db2.jcc.b.w.b(w.java:71)
      at com.ibm.db2.jcc.b.wb.d(wb.java:236)
      at com.ibm.db2.jcc.c.gg.bb(gg.java:1802)
      at com.ibm.db2.jcc.c.gg.eb(gg.java:1863)
      at com.ibm.db2.jcc.c.gg.d(gg.java:2295)
      at com.ibm.db2.jcc.c.gg.W(gg.java:457)
      at com.ibm.db2.jcc.c.gg.executeUpdate(gg.java:440)
      at com.arkona.Simple.doGet(Simple.java:48)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1212)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:629)
      at com.ibm.ws.webcontainer.extension.InvokerExtensionProcessor.handleRequest(InvokerExtensionProcessor.java:195)
      at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:2837)
      at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:220)
      at com.ibm.ws.webcontainer.VirtualHost.handleRequest(VirtualHost.java:204)
      at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1681)
      at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:421)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:367)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:276)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:201)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:103)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:548)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:601)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:934)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1021)
      at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))
---- Begin backtrace for Nested Throwables
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-117;42802;
      at com.ibm.db2.jcc.c.fg.d(fg.java:1340)
      at com.ibm.db2.jcc.b.gb.k(gb.java:351)
      at com.ibm.db2.jcc.b.gb.a(gb.java:60)
      at com.ibm.db2.jcc.b.w.a(w.java:52)
      at com.ibm.db2.jcc.b.wb.c(wb.java:213)
      at com.ibm.db2.jcc.c.gg.ab(gg.java:1779)
      at com.ibm.db2.jcc.c.gg.d(gg.java:2324)
      at com.ibm.db2.jcc.c.gg.W(gg.java:457)
      at com.ibm.db2.jcc.c.gg.executeUpdate(gg.java:440)
      at com.arkona.Simple.doGet(Simple.java:48)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1212)
      at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:629)
      at com.ibm.ws.webcontainer.extension.InvokerExtensionProcessor.handleRequest(InvokerExtensionProcessor.java:195)
      at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:2837)
      at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:220)
      at com.ibm.ws.webcontainer.VirtualHost.handleRequest(VirtualHost.java:204)
      at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1681)
      at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:421)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:367)
      at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:276)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:201)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:103)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:548)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:601)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:934)
      at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1021)
      at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))

[4/1/07 21:12:33:043 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.fg.e(fg.java:1596)
[4/1/07 21:12:33:043 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.fg.a(fg.java:1206)
[4/1/07 21:12:33:044 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.b.gb.g(gb.java:140)
[4/1/07 21:12:33:044 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.b.gb.a(gb.java:39)
[4/1/07 21:12:33:044 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.b.w.a(w.java:34)
[4/1/07 21:12:33:044 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.b.vb.g(vb.java:139)
[4/1/07 21:12:33:045 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.fg.n(fg.java:1177)
[4/1/07 21:12:33:045 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.gg.eb(gg.java:1862)
[4/1/07 21:12:33:045 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.gg.d(gg.java:2295)
[4/1/07 21:12:33:045 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.gg.W(gg.java:457)
[4/1/07 21:12:33:045 MDT] 00000030 SystemErr     R       at com.ibm.db2.jcc.c.gg.executeUpdate(gg.java:440)
[4/1/07 21:12:33:046 MDT] 00000030 SystemErr     R       at com.arkona.Simple.doGet(Simple.java:48)
[4/1/07 21:12:33:046 MDT] 00000030 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
[4/1/07 21:12:33:046 MDT] 00000030 SystemErr     R       at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
[4/1/07 21:12:33:046 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1212)
[4/1/07 21:12:33:047 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:629)
[4/1/07 21:12:33:047 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.extension.InvokerExtensionProcessor.handleRequest(InvokerExtensionProcessor.java:195)
[4/1/07 21:12:33:047 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:2837)
[4/1/07 21:12:33:047 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:220)
[4/1/07 21:12:33:048 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.VirtualHost.handleRequest(VirtualHost.java:204)
[4/1/07 21:12:33:048 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1681)
[4/1/07 21:12:33:048 MDT] 00000030 SystemErr     R       at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
[4/1/07 21:12:33:048 MDT] 00000030 SystemErr     R       at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:421)
[4/1/07 21:12:33:048 MDT] 00000030 SystemErr     R       at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:367)
[4/1/07 21:12:33:049 MDT] 00000030 SystemErr     R       at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:276)
[4/1/07 21:12:33:049 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:201)
[4/1/07 21:12:33:049 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:103)
[4/1/07 21:12:33:049 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:548)
[4/1/07 21:12:33:050 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:601)
[4/1/07 21:12:33:050 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:934)
[4/1/07 21:12:33:050 MDT] 00000030 SystemErr     R       at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1021)
[4/1/07 21:12:33:050 MDT] 00000030 SystemErr     R       at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))


Here is the table and properties of that database table

CREATE TABLE "DB2INST1"."DLDATA" (
            "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE),
            "COMPANYID" VARCHAR(3) NOT NULL,
            "LICENSE" VARCHAR(25) NOT NULL,
            "FIRSTNAME" VARCHAR(25) NOT NULL,
            "MIDDLENAME" VARCHAR(25),
            "LASTNAME" VARCHAR(25) NOT NULL,
            "ADDRESS" VARCHAR(100) NOT NULL,
            "CITY" VARCHAR(25) NOT NULL,
            "STATE" VARCHAR(2) NOT NULL
      )

ALTER TABLE "DB2INST1"."DLDATA" ADD CONSTRAINT "CC1175275084020" PRIMARY KEY ("LICENSE", "STATE")

Sio I am unsure whats wrong with my statement
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 18834258
Hey my bad... I should have checked that preparedStatement.executeQuery will only excute query & not an insert..

Change the line

ps.executeQuery();

to
ps.executeUpdate();
should fix it
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 18834267
Also your table has 9 columns and your statement is sending only 7.. the query will fail with error code -117.... "The number of insert or update values is not the same as the number of columns"
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now