Re: SQL PreparedStatement for update

Hmmm... I'm not having problems doing what I think you are doing.  Other
than your seceond attempt:

setSalesCnt.setString(1, count.add(new BigDecimal(1)));

which shouldn't even compile because of a BigDecimal and a String not being
assignment compatible.

I have created a little program that tries to simulate what you are doing
at that works fine.  I would suggest that you
check to make sure you are up to date on PTFs.  If the problem persists,
please get info on the release to me and
try to provide more details about what your specific failure is (exception
stack and such).   Also, a recreation
program would be useful.  I can provide you with my sample program that is
working fine here to start from if
that will help - just email me directly if you want that.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"Biologists have a special word for stability -- dead"

                Larry Wall
                Open Source Developers Journal
                Issue 1, Jan  2000


keith.mccully@hsbcam.com@midrange.com on 02/01/2001 06:33:47 AM

Please respond to JAVA400-L@midrange.com

Sent by:  owner-java400-l@midrange.com


To:   JAVA400-L@midrange.com
cc:
Subject:  SQL PreparedStatement for update






I am testing a prototype DB2/400 connection/update class in QSH  -
eventually to
be executed from a servlet.

 I am having a problem updating a field which is numeric 5 0 packed.

I am creating a SQL PreparedStatement object for performance reasons in the
DB
connection method:

getSalesCnt = dbConnection.prepareStatement("SELECT CLIENTS "+
"FROM SALES WHERE DISTRICT = ? FOR UPDATE");

getSalesCnt.setCursorName("SAL");

setSalesCnt = dbConnection.prepareStatement("UPDATE SALES "+
"SET CLIENTS = ? WHERE CURRENT OF SAL");


In the update method I create the record set OK:

ResultSet rs = null;

getSalesCnt.setString(1, loc);
rs = getSalesCnt.executeQuery();

// Extract the count from the result set

rs.next();

BigDecimal count = rs.getBigDecimal("CLIENTS", 0);

However, the method throws an SQLException on the following line when I try
to
increment the number of
clients by 1 :

 setSalesCnt.setString(1, count.add(new BigDecimal(1)).toString());

I tried an alternative line:

setSalesCnt.setString(1, count.add(new BigDecimal(1)));

But this also threw an SQLException.

What is the correct syntax for this update?

Any assistance very much appreciated.

Keith



















__________________________________________________________________

This transmission has been issued by a member of the HSBC group ("HSBC")
for the information of the addressee only and should not be reproduced
and / or distributed to any other person. Each page attached hereto must
be read in conjunction with any disclaimer which forms part of it.
This transmission is neither an offer nor the solicitation of an offer
to sell or purchase any investment. Its contents are based on information
obtained from sources believed to be reliable but HSBC makes no
representation and accepts no responsibility or liability as to its
completeness or accuracy.
+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---



+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---