Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Character set when using kitchen and spoon

  1. #1
    Join Date
    Jul 2007
    Posts
    247

    Default Character set when using kitchen and spoon

    Hi everybody,

    it seems like there's a difference concerning the character set used when opening a database connection by a transform depending if one is using either kitchen or spoon...

    Here's what I did:

    I created a transformation with a table input step, connecting to an Oracle database. The Oracle server is a Windows 2003 Server English edition, the nls_characterset is WE8MSWIN1252, nls_language is AMERICAN, lns_territory is AMERICA. I used the following SQL statement to test the numeric character set:

    Code:
    SELECT to_number('3.12') as num FROM DUAL;
    Clicking on Preview return one row with the correct number, no errors.

    Now I designed a job calling this transformation (the result is copied back to the job) and run it in spoon - everything is ok.

    Now I tried to run the job from kitchen calling kitchen.bat from the command line from the same server where spoon is installed (even the oracle database is on the same server...). Now the job reports an error, stating invalid number...

    Why is there a difference when using spoon or kitchen? Is there any windows environment variable to be set before using kitchen?

    The only workaround I discovered so far is to execute an "ALTER SESSION" statement whenever the database connection is used (SQL tab in connection settings):
    Code:
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;
    Is there any other way to handle language and character setting correctly?

    BTW: When using the SQL command in the database connection settings I think I've found another bug in spoon: After entering the SQL statement and clicking OK the statement is no longer displayed in the connection window, though the information is safely stored in the repository...

    Thx in advance,
    Ben

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It's got nothing to do with Kettle, Spoon or Pan. it's got to do with the Oracle NLS settings.
    Figuring those out is the topic of many a nightmare.

    Your solution of forcing it sounds great actually, it's what I would have proposed.

    Otherwise you're never sure WHAT the Oracle client has set in your registry, as environment variables, etc.

    All the best,

    Matt

  3. #3
    Join Date
    Jul 2007
    Posts
    247

    Default

    Hi Matt,

    thanks for the quick reply.

    What I don't understand is, I run both apps on the same machine for the same job. There's only one Windows environment, one registry and one Oracle installation!

    So why do I get different behavior for kitchen and spoon on one machine???

    Thx,
    Ben

    P.S. Do you agree that the "disappearing" of the SQL statement for the connection setting is a bug?

  4. #4
    Join Date
    Jul 2007
    Posts
    247

    Default

    any idea, anyone?

    thx!

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

    Default

    No, not directly. You can raise a bugtracker for it.

    If you would run it on UNIX I would blame the scheduler ;-)

    Regards,
    Sven
    Last edited by sboden; 07-23-2007 at 05:43 AM.

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.