RE: SQL update/insert for a string contain " ' " -- JAVA400-L

Thanks, Fred.  It works well by using prepared statement.

-----Original Message-----
From: Fred Kulack [mailto:kulack@us.ibm.com]
Sent: Thursday, October 04, 2001 10:54 AM
To: java400-l@midrange.com
Subject: Re: SQL update/insert for a string contain " ' "



You could ignore these difficulties (and from the looks of your code)
improve
performance if you'd use a prepared statement instead of mashing strings
together on your own. In general, this is a bad way to use SQL if you
execute
the statement more than one time with different parameters.

For some reason, I think that you'll come through this code more than
once in your application with the text field (and thus 'desc') set to
different values.
Use a prepared statement so you can avoid the engine recompiling
the statement and creating those resource each time.

> String desc = getJTextFieldDesc().getText();
>
> String update = "update myTable set DESCRIPTION = '"+desc+"' where ID =
1";
>
> stmt.createStatement(); <<-- Typo/Bug here.
> stmt.executeUpdate(update);

Should instead be this...

// Do one time per connection
PreparedStatement   pstmt = conn.prepareStatment("update myTable set
DESCRIPTION = ? where ID = 1");

// Later in mainline path
String desc = getJTextFieldDesc().getText();
pstmt.setString(1, desc);
pstmt.executeUpdate();



In every single ethnic, religious or racial group, there are a
very few truly evil people. For each of those people there
are many, many, many good people.
Assuming anything (evilness or capability for evil) about the
particular group is bigotry and idiocy. Don't do it.  -- Me

Fred A. Kulack  -  AS/400e  Java and Java DB2 access, Jdbc, JTA, etc...
IBM in Rochester, MN  (Phone: 507.253.5982   T/L 553-5982)
mailto:kulack@us.ibm.com   Personal: mailto:kulack@magnaspeed.net
AOL Instant Messenger: Home:FKulack  Work:FKulackWrk


"Bruce Jin" <brucej@mrc-productivity.com>@midrange.com on 10/04/2001
10:53:44 AM

Please respond to java400-l@midrange.com

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


To:   <java400-l@midrange.com>
cc:
Subject:  Re: SQL update/insert for a string contain "  '  "



 Hi Xu Weining;
 One way to do this is to parse the text before update. For single quote,
you have to make it double quote. Like this "Tom''s desk". This string will
be sent to database as "Tom's desk".

Bruce

----- Original Message -----
From: "Xu, Weining" <Weining.Xu@AIG.com>
To: <JAVA400-L@midrange.com>
Sent: Thursday, October 04, 2001 6:07 AM
Subject: SQL update/insert for a string contain " ' "


> Hi, all,
>
> I am using JDBC to perform update/insert for DB2 on AS400.  The user
types
> inputs from a Java GUI. For example, when user types in "Tom's desk" in a
> text field.  I will update the field with the typed string.  The partial
> code is like this:
>
> String desc = getJTextFieldDesc().getText();
>
> String update = "update myTable set DESCRIPTION = '"+desc+"' where ID =
1";
>
> stmt.createStatement();
> stmt.executeUpdate(update);
>
> The update will return error, since the string passed into SQL update
> statement would be 'Tom's Desk'.  The SQL hit the first closing "  '  "
> after letter m rather after letter k.
>
> Any solutions?  Thanks for your help.
>
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400
(JAVA400-L)
mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>

_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.




_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.