Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Handling Special Characters

Status
Not open for further replies.

mwa

Programmer
Jul 12, 2002
507
US
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
<><
 
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?
 
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
 
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
<><
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top