Handling Special Characters

[mwa]

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
<><

 

[papadba]

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?

 

[MarcLodge]

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

 

[mwa]

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

--  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:

--  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
<><