Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Problem with special character in SQL command

  1. #1
    Join Date
    Mar 2008
    Posts
    8

    Default Problem with special character in SQL command

    I am using the following valid postgres command in a SQL command task. However, it gets rejected when I try to run the job because Kettle says it is an invalid XML character.

    copy INVALID_EMAIL_DTLS from
    'work/INVALID_EMAIL_DTLS.txt'
    with csv
    delimiter as E'\t' escape as E'\x01'
    null as ''

    [Fatal Error] :561:62: An invalid XML character (Unicode: 0x1) was found in the element content of the document.
    ERROR 20-08 12:27:40,897 (LogWriter.javarintln:403) -Thread[bulkmail_invalid_email (bulkmail_invalid_email (Thread-2)),5,main] - org.pentaho.di.core.exception.KettleException:
    Unexpected error during transformation metadata load

    Error reading information from file
    An invalid XML character (Unicode: 0x1) was found in the element content of the document.

  2. #2
    Join Date
    Mar 2008
    Posts
    8

    Default

    Actually, it seems like this might be the problem query from Oracle. However, the question remains on how to use special characters.

    SELECT
    TXN_ID
    , regexp_replace(email, '([",]{1,})\', '\1' ) as EMAIL
    , STATUS
    , CREATE_DATE
    FROM INVALID_EMAIL_DTLS

  3. #3
    Join Date
    Mar 2008
    Posts
    8

    Default

    Instead of using a literal in the query, I used the Oracle function chr() and I did not get the same XML error from Kettle.

    regexp_replace(EMAIL, '([[unct:][:space:]]{1,})\', chr(1)||'\1' )

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    It's probably not in Oracle, it's more in the saving routines of the transformations which doesn't take into account "strange characters" can occur in an SQL statement.... "no one in his right mind is going to use that"-type of stuff.

    Regards,
    Sven

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.