Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Unexpeted Data Type Conversion error javascript and sql server

  1. #1
    Join Date
    Jun 2014
    Posts
    28

    Default Unexpeted Data Type Conversion error javascript and sql server

    I have a transformation where I use the modified javascript step to calculate a couple of integers and a couple of BigNumbers.

    I create them as new variables of the appropriate type and output them from the javascript step. Showing Output Fields shows they are the appropriate data type.

    I later use a Set Fields step to put their values into pre-defined variables (cop_remaining an Integer is set to the value in cop_remaining_out also an Integer from my javascript step)

    When I attempt to write cop_remaining to a sql server table into an Integer field I get 'Conversion error: num_err Integer(3): There was a data type error: the type of java.lang.String object.... Error converting data type nvarchar to decimal..'

    I think it may be related to http://jira.pentaho.com/browse/PDI-16602 . Are there any work arounds?
    I'm using CE 7.0 on Windows.
    Last edited by bcrow; 11-08-2017 at 05:40 PM.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I doubt that it's that particular bug.

    First: Separate "Variables" in your mind from field/column values. Field/Column values are *NOT* variables in PDI.

    JavaScript Variables, are not statically typed, so your comment of "new variables of the appropriate type" doesn't make sense.
    PDI variables are Strings, but can't be set and used in the same transformation, so if you're doing that, you're doing something wrong.

    So... Post your transformation, and we'll help you debug.

  3. #3
    Join Date
    Jun 2014
    Posts
    28

    Default

    you're right, variables mean something different in PDI.Test_JS_type.ktr I've uploaded a sample of what I'm talking about. I have a column in my row named mon_remaining defined as BigNumber and initialized to zero in an add contants step. in a javscript step I calculate a new version and add it to my row as mon_remaining_out, in the output pane of the javascript step I set this to type BigNumber

    Later in a set fields step I set mon_remaining my previously defined field to mon_remaining_out, my calculated field.

    I follow with process with cop_earned, cop_remaining, iss_earned, iss_remaining which are Integers

    when writing to SQL Server , whether I'm writing the mon_earned or mon_earned_out, I get the type conversion error. I don't know exactly which of these fields cause the sql error.

    Another version of the transformation that doesn't use javascript doesn't have this issue. (the other non-javascript version isn't quite right which is why I'm trying to get the javscript version to work.)

    thanks for any help or direction.

    I may try using a calculator step, first remove mon_earned, then in the calculator step make a copy of mon_earned_out to mon_earned and calling it a bignumber)

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Modified JavaScript Value (MJSV) is a step I almost always tell people to avoid. It is a troublesome step, that often decreases performance.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    None of your S2 fields are in the stream. If any of them are wrong, MJSV will implicitly convert the fields.
    If they are coming from a Text File Input, it's possible that they are coming in as "Lazy Convert" which causes all sorts of interesting issues.

    Also, for net-new code, you should always turn "Compatibility Mode" OFF (you have it on for some reason)

  6. #6
    Join Date
    Jun 2014
    Posts
    28

    Default

    the same thing happens with compatibility mode off, I turned it On just to see if it was a factor.

    the S2F fields are coming from a text file but not with lazy convert, KETTLE_LENIENT_STRING_TO_NUMBER_CONVERSION is set to N
    I didn't supply the entire transformation as the problem seems to be with fields coming out of the javascript step .

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Post the most simplified version that can be run on another machine as an example.
    Sometimes a step that is throwing an error isn't the step that is causing the error.

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.