INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Handling Special Characters

Handling Special Characters

(OP)
I have an ASP.net web application that reads/writes data to a DB2 database. We have been getting numerous "Conversion Errors" when writing data. I think I have narrowed it down to users entering text containing the ' character (or char(146))... Not to be confused with the ' character (or char(39)). It seems that DB2 does not like char(146) and errors out. Is that correct? Is there a way to get DB2 to accept that character?

thanks,

mwa
<><

RE: Handling Special Characters

Any byte may contain a value between x'00' and x'FF'. DB2 can handle all 256 possible values. . .

What is the actual db2 sqlcode?

RE: Handling Special Characters

Is it possible that the "'" is being recognised as the end character of the text column and that is pushing everything else out of sync?

Marc

RE: Handling Special Characters

(OP)
I'll try to spare you some of the irrelevant code... Here is the Insert SP:

CODE

--  Generated on:                  02/26/10 15:53:47
--  Relational Database:           MYSERVER
--  Standards Option:              DB2 UDB iSeries
  
SET PATH "QSYS","QSYS2","MYDB" ;
  
CREATE PROCEDURE TRMSPSQL.PER0202P11_INS_EXP_REC (
    IN P_EMP_ID VARCHAR(10) ,
        .....DELETED SOME CODE HERE......
    IN P_RESPONSIBILITIES VARCHAR(240) ,
    IN P_REASON VARCHAR(60) )
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    SPECIFIC MYDB.PER0202P11
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    SET OPTION  ALWBLK = *ALLREAD ,
    ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *NONE ,
    DECRESULT = (31, 31, 00) ,
    DFTRDBCOL = *NONE ,
    DYNDFTCOL = *NO ,
    DYNUSRPRF = *USER ,
    SRTSEQ = *HEX   
    BEGIN
.....DELETED SOME CODE HERE......
 --> ADD RECORD TO ATXTP TABLE:
    IF RTRIM ( P_RESPONSIBILITIES ) || RTRIM ( P_REASON ) <> '' THEN
        INSERT INTO MYDB.ATXTP
        VALUES (
        W_DIST ,  --TXTKDIST CHAR(3)
        'CNT' ,  --TXTKFILE CHAR(3)
        'CNT' ,  --TXTKRCD CHAR(3)
        W_DIST || RTRIM ( P_EMP_ID ) || ' X' || W_CNTKDATE || P_EXP_BUCKET ,  --TXTKVALU CHAR(79)
        '01' ,  --TXTKPAGE CHAR(2)
        'WEBSQLIN' ,  --TXTUSR CHAR(8)
        LEFT ( W_UPDATED , 2 ) ,  --TXTUCC CHAR(2),
        SUBSTR ( W_UPDATED , 3 , 2 ) ,  --TXTUYY CHAR(2),
        SUBSTR ( W_UPDATED , 5 , 2 ) ,  --TXTUMM CHAR(2),
        SUBSTR ( W_UPDATED , 7 , 2 ) ,  --TXTUDD CHAR(2),
        SUBSTR ( W_UPDATED , 9 , 2 ) ,  --TXTUHR CHAR(2),
        SUBSTR ( W_UPDATED , 11 , 2 ) ,  --TXTUMN CHAR(2),
        SUBSTR ( W_UPDATED , 13 , 2 ) ,  --TXTUSC CHAR(2),
        '' ,  --    TXTPROT CHAR(1)
        P_POSITION_TITLE ,  --TXTLIN1 CHAR(60)
        P_GRADE_SUBJECT ,  --TXTLIN2 CHAR(60)
        CASE
            WHEN LENGTH ( RTRIM ( P_RESPONSIBILITIES ) ) < 61 THEN RTRIM ( P_RESPONSIBILITIES )
            ELSE LEFT ( P_RESPONSIBILITIES , 60 )
        END ,  --TXTLIN3 CHAR(60) CHARS 1-60
        CASE
            WHEN LENGTH ( P_RESPONSIBILITIES ) > 60 THEN
                CASE
                    WHEN LENGTH ( P_RESPONSIBILITIES ) < 121 THEN SUBSTR ( P_RESPONSIBILITIES , 61 , LENGTH ( RTRIM ( P_RESPONSIBILITIES ) ) - 60 )
                    ELSE SUBSTR ( P_RESPONSIBILITIES , 61 , 60 )
                END
            ELSE ''
        END ,  --    TXTLIN4 CHAR(60) CHARS 61-120
        CASE
            WHEN LENGTH ( P_RESPONSIBILITIES ) > 120 THEN
                CASE    
                    WHEN LENGTH ( P_RESPONSIBILITIES ) < 181 THEN SUBSTR ( P_RESPONSIBILITIES , 121 , LENGTH ( RTRIM ( P_RESPONSIBILITIES ) ) - 120 )
                    ELSE SUBSTR ( P_RESPONSIBILITIES , 121 , 60 )
                END
            ELSE ''
        END ,  --TXTLIN5 CHAR(60) CHARS 121-180
        CASE
            WHEN LENGTH ( P_RESPONSIBILITIES ) > 180 THEN SUBSTR ( P_RESPONSIBILITIES , 181 , 60 )
            ELSE ''
        END ,  --TXTLIN6 CHAR(60) CHARS 181-240
        P_REASON ,  --TXTLIN7 CHAR(60)
        '' ,  --TXTLIN8 CHAR(60)
        'Y' ,  --TXTPRINT CHAR(1)
        ''  --TXTFL1
        ) ;
        

    END IF ;
.....DELETED SOME CODE HERE......

And here is the table that data is inserted into:

CODE

--  Generate SQL
--  Version:                       V5R4M0 060210
--  Generated on:                  02/26/10 16:05:12
--  Relational Database:           MYSERVER
--  Standards Option:              DB2 UDB iSeries
  
CREATE TABLE MYDB.ATXTP (
    TXTKDIST CHAR(3) CCSID 37 NOT NULL DEFAULT '' ,
    TXTKFILE CHAR(3) CCSID 37 NOT NULL DEFAULT '' ,
    TXTKRCD CHAR(3) CCSID 37 NOT NULL DEFAULT '' ,
    TXTKVALU CHAR(79) CCSID 37 NOT NULL DEFAULT '' ,
    TXTKPAGE CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUSR CHAR(8) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUCC CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUYY CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUMM CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUDD CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUHR CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUMN CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTUSC CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
    TXTPROT CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN1 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN2 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN3 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN4 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN5 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN6 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN7 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTLIN8 CHAR(60) CCSID 37 NOT NULL DEFAULT '' ,
    TXTPRINT CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,
    TXTFL1 CHAR(16) CCSID 37 NOT NULL DEFAULT '' ,
    PRIMARY KEY( TXTKDIST , TXTKFILE , TXTKRCD , TXTKVALU , TXTKPAGE ) )   
      
    RCDFMT TXT        ;

The table (physical file) is a delivered table from our HR program. No way for us to change the structure of the table.

mwa
<><

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close