Oninit cdi / Archive
Talk to an expert →

Converting illegal characters to '?'

Posted in 2014

Topics: Connectivity: ODBC / JDBC / .NET, Java & JDBC Development, Internationalization & Character Sets

LAURI PIETARINEN — — source: IIUG Forums & Mailing Lists

I am currently creating an ETL job to load rows from an Oracle DB to Informix 11.7. I am having problems with characters that are not supported in the Informix database (Oracle is presumably on Unicode, Informix is not, collation is fi_fi.819). Below is the error message given by Talend ETL-tool. Is it possible to somehow inform the JDBC-driver to convert non-supported characters to some special character, e.g. '?' ?? It is interesting that the character set used does not even support the EUR-sign. Thanks, Lauri Pietarinen Starting job EXSMT_MIG_JOB at 21:56 06/04/2014. [statistics] connecting to socket on port 3467 [statistics] connected java.io.IOException: Code-set conversion function failed due to illegal sequence or invalid value. at com.informix.lang.JavaToIfxType.doConversion(JavaToIfxType.java:845) at com.informix.lang.JavaToIfxType.JavaToIfxChar(JavaToIfxType.java:145) at com.informix.jdbc.IfxChar.fromString(IfxChar.java:291) at com.informix.jdbc.IfxValue.a(IfxValue.java:981) at com.informix.jdbc.IfxPreparedStatement.setString(IfxPreparedStatement.java:1460) at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.tOracleInput_12Process(EXSMT_MIG_JOB.j ava:7652) at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.runJobInTOS(EXSMT_MIG_JOB.java:62241) at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.main(EXSMT_MIG_JOB.java:62022) Exception in component tInformixOutput_11 java.sql.SQLException: Encoding or code set not supported. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407) at com.informix.jdbc.IfxChar.fromString(IfxChar.java:299) at com.informix.jdbc.IfxValue.a(IfxValue.java:981) at com.informix.jdbc.IfxPreparedStatement.setString(IfxPreparedStatement.java:1460) at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.tOracleInput_12Process(EXSMT_MIG_JOB.j ava:7652) at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.runJobInTOS(EXSMT_MIG_JOB.java:62241) at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.main(EXSMT_MIG_JOB.java:62022)

↪ replying to LAURI PIETARINEN
Fernando Nunes — — source: IIUG Forums & Mailing Lists

AFAIK, the answer would be: Technically yes, but you would need to create your own conversion code files. This is not properly documented and could create some upgrade challenges. The other option is to make the transformation in your ETL tool. As for the lack of support for Euro sign in 819, that's by design. 819 is equivalente to iso-8859-1. You'd need 8859-15 to support the euro sign. Regards On Tue, Apr 8, 2014 at 8:34 AM, LAURI PIETARINEN < lauri.pietarinen@relational-consulting.com> wrote: > I am currently creating an ETL job to load rows from an Oracle DB to > Informix > 11.7. I am having problems with characters that are not supported in the > Informix database (Oracle is presumably on Unicode, Informix is not, > collation > is fi_fi.819). > > Below is the error message given by Talend ETL-tool. > > Is it possible to somehow inform the JDBC-driver to convert non-supported > characters to some special character, e.g. '?' ?? > > It is interesting that the character set used does not even support the > EUR-sign. > > Thanks, > Lauri Pietarinen > > Starting job EXSMT_MIG_JOB at 21:56 06/04/2014. > > [statistics] connecting to socket on port 3467 > [statistics] connected > java.io.IOException: Code-set conversion function failed due to illegal > sequence or invalid value. > > at com.informix.lang.JavaToIfxType.doConversion(JavaToIfxType.java:845) > > at com.informix.lang.JavaToIfxType.JavaToIfxChar(JavaToIfxType.java:145) > > at com.informix.jdbc.IfxChar.fromString(IfxChar.java:291) > > at com.informix.jdbc.IfxValue.a(IfxValue.java:981) > > at > > com.informix.jdbc.IfxPreparedStatement.setString(IfxPreparedStatement.java:1460) > > at > > exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.tOracleInput_12Process(EXSMT_MIG_JOB.j ava:7652) > > at > > exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.runJobInTOS(EXSMT_MIG_JOB.java:62241) > > at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.main(EXSMT_MIG_JOB.java:62022) > Exception in component tInformixOutput_11 > java.sql.SQLException: Encoding or code set not supported. > > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407) > > at com.informix.jdbc.IfxChar.fromString(IfxChar.java:299) > > at com.informix.jdbc.IfxValue.a(IfxValue.java:981) > > at > > com.informix.jdbc.IfxPreparedStatement.setString(IfxPreparedStatement.java:1460) > > at > > exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.tOracleInput_12Process(EXSMT_MIG_JOB.j ava:7652) > > at > > exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.runJobInTOS(EXSMT_MIG_JOB.java:62241) > > at exsmt_mig.exsmt_mig_job_0_1.EXSMT_MIG_JOB.main(EXSMT_MIG_JOB.java:62022) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c3474c69b6a704f68471c3

↪ replying to Fernando Nunes
LAURI PIETARINEN — — source: IIUG Forums & Mailing Lists

Thanks for your answer! If I were to create such custom code tables, what would be a good way to go about it? The ETL-tool is one possibility. It seems very unpredictable, the kinds of characters that might pop up, so preferably it would be nice to just convert all illegal characters to '?', or something. Where would I find a list of the illegal characters? Thanks, Lauri Pietarinen

Related threads