SQL User Defined Java Function (Character conversion between CCSID 65535 and CCSID 1200 not valid)

I have been having a problem with an iSeries Function whereby it is not connverting data properly becuase my default user profile is using CCSID 65535. I can change the job to CCSID 37 and everything works fine.

I want a solution whereby the user does not need to change their job properties.

The function is running a java application and looks like this

CREATE FUNCTION mylib/re_Test2(input VARCHAR(500) CCSID 37,
                              regex VARCHAR(500) CCSID 37)
RETURNS INTEGER
EXTERNAL NAME 'UDFs.re_Test'
LANGUAGE Java
PARAMETER STYLE Java
FENCED
NO SQL
RETURNS NULL ON NULL INPUT
SCRATCHPAD
DETERMINISTIC

I tried it without using the CCSID 37 initially but found some posts suggesting that adding this would force any parameters to be converted to US English. It does not seem 开发者_开发百科to be working for me.

Any suggestions?

I tried running from STRSQL and an RPGLE script but both don't work, however, from SQLSquirrel (an open source SQL program that uses ODBC) it works.

CCSID 65535 means 'no translation of characters' ... so if your table is created with a specific CCSID, I would suggest running the application with that CCSID.

I had the same problem. I found out it doesn't concern function parameters (I had integers).

The matter is how you call the function. For example in my case calling it with System i Navigator worked, but not with an RPG program (which probably uses the user CCSID, that is 65535).

For the latter I solved with CHGJOB CCSID(37) (37 is for "COM EUROPE EBCDIC", anyone can choose the proper code page) and then putting again CHGJOB CCSID(65535).

Another similar way is CHGUSRPRF USRPRF(MYUSER) CCSID(37). Haven't found anything better...

The above select works fine too, except if you try to put in a :INTO host variable within RPG it will through an error. Solution is do a chgjob of CCSID to 37, then run your embedded SQL, then changejob CCSID back to 65535. Works fine then. If you want more information or samples, send me email at William.Ramos@mohawkind.com for more information. I came across this problem using the ENCODE/DECODE functions in the RPG world. Thanks.

use simple cast, where CSSID 37 = CSSID 65535,

Example:

select cast( campo1 as varchar(500) CCSID 37 ) from biblioteca.tabla

db2 is unusual

Solution is do a CHGUSRPRF of CCSID to 37, then run your embedded SQL, then changejob CCSID back to 65535. Works fine then.