Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Handling empty strings

  1. #1
    Join Date
    Jan 2006
    Posts
    12

    Default Handling empty strings

    I'm trying to copy some data from SQL Server into Oracle. The problem is that when kettle finds an empty varchar field (mind it, empty, not null), it tries to insert a null into oracle, which throws an exception, because that particular field is defined as 'not null'.


    Am I missing something here?
    (I'm sorry if this was already addressed)



    Thanks,
    Alin

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

    Default RE: Handling empty strings

    Hi Alin,

    This is a very nice question. In fact, there is no way to distinguish between NULL and an empty string in Oracle. To Oracle, they are the same. So I guess if you want to solve this on the Oracle side, you need to use a Sentinel like '-' to do this or simply remove the NOT NULL constraint.

    Nothing that Kettle can do to help here except that you might want to put a little piece of java script in between with something like

    if ( !field.isNull() && field.getString().length()==0 )
    {
    field.setValue("-");
    }

    Hope this helps,
    Matt

  3. #3
    Join Date
    Jan 2006
    Posts
    12

    Default RE: Handling empty strings

    So, it was an oracle thing, afterall.

    Thank you, that did it.

    Alin

  4. #4
    Join Date
    Feb 2008
    Posts
    216

    Unhappy

    Quote Originally Posted by MattCasters View Post
    Hi Alin,

    This is a very nice question. In fact, there is no way to distinguish between NULL and an empty string in Oracle. To Oracle, they are the same. So I guess if you want to solve this on the Oracle side, you need to use a Sentinel like '-' to do this or simply remove the NOT NULL constraint.

    Nothing that Kettle can do to help here except that you might want to put a little piece of java script in between with something like

    if ( !field.isNull() && field.getString().length()==0 )
    {
    field.setValue("-");
    }

    Hope this helps,
    Matt
    I'm experiencing a similar problem. However, I have a SQL server source AND target. The source contains either empty strings or space-filled strings. When I output the values to another SQL server table, it is creating them as NULLs instead. If I output the value to a text file, I see that they are space filled or empty strings and not NULLs. Is there a way around this behavior? I'm trying to move data from our production system to an archived copy. The initial copy that I did was from a Progress db to SQL server. Those seemed to copy correctly as empty strings. When I check to see if the data in the archive copy is different from incoming production data, this is when I am running into trouble. Since I can't compare to a NULL value (and the values weren't NULL to begin with) this is causing me a lot of grief.

    Also, a quick question about your java code above. When I attempt a getString().length() on a field that is NULL, I'm getting an error message that it can't return a length value for a field that contains a NULL value. Is it just me?

    Also, I'm getting an error trying to use the isNull() function.
    **********************************************************************************************
    org.pentaho.di.core.exception.KettleException:
    General error executing script:
    TypeError: Cannot find function isNull. (script#14)

    at org.pentaho.di.ui.trans.steps.scriptvalues_mod.ScriptValuesModDialog.test(ScriptValuesModDialog.java:1103)
    at org.pentaho.di.ui.trans.steps.scriptvalues_mod.ScriptValuesModDialog.access$300(ScriptValuesModDialog.java:128)
    at org.pentaho.di.ui.trans.steps.scriptvalues_mod.ScriptValuesModDialog$4.handleEvent(ScriptValuesModDialog.java:461)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.trans.steps.scriptvalues_mod.ScriptValuesModDialog.open(ScriptValuesModDialog.java:555)
    at org.pentaho.di.ui.spoon.delegates.SpoonStepsDelegate.editStep(SpoonStepsDelegate.java:124)
    at org.pentaho.di.ui.spoon.Spoon.editStep(Spoon.java:6420)
    at org.pentaho.di.ui.spoon.trans.TransGraph.editStep(TransGraph.java:2153)
    at org.pentaho.di.ui.spoon.trans.TransGraph.access$100(TransGraph.java:116)
    at org.pentaho.di.ui.spoon.trans.TransGraph$4.mouseDoubleClick(TransGraph.java:312)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:873)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:5626)
    at org.pentaho.di.ui.spoon.Spoon.run(Spoon.java:5722)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:371)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:116)
    Caused by: org.mozilla.javascript.EcmaError: TypeError: Cannot find function isNull. (script#14)
    at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3229)
    at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3219)
    at org.mozilla.javascript.ScriptRuntime.typeError(ScriptRuntime.java:3235)
    at org.mozilla.javascript.ScriptRuntime.typeError1(ScriptRuntime.java:3247)
    at org.mozilla.javascript.ScriptRuntime.notFunctionError(ScriptRuntime.java:3307)
    at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThis(ScriptRuntime.java:1991)
    at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:2932)
    at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2251)
    at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:161)
    at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:340)
    at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:2758)
    at org.mozilla.javascript.InterpretedFunction.exec(InterpretedFunction.java:172)
    at org.pentaho.di.ui.trans.steps.scriptvalues_mod.ScriptValuesModDialog.test(ScriptValuesModDialog.java:1036)
    ... 26 more
    Last edited by DebbieKat; 05-20-2008 at 01:54 PM. Reason: Added error about isNull()

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

    Default

    You hijacked a 2-year old thread.
    Please do tell us what Kettle version you are using before you tell us the script you used.

    Matt

  6. #6
    Join Date
    Feb 2008
    Posts
    216

    Default Empty Strings... cont'd

    Quote Originally Posted by MattCasters View Post
    You hijacked a 2-year old thread.
    Please do tell us what Kettle version you are using before you tell us the script you used.

    Matt
    I thought that was preferred to opening a new thread on the same topic... Should I move this to a new thread? I'm using 3.0.2.

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

    Default

    As you like, but a little bit of context is always nice.

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.