PDA

View Full Version : Handling empty strings



kettle_alinv
02-20-2006, 07:45 AM
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

MattCasters
02-20-2006, 08:56 AM
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

kettle_alinv
02-20-2006, 11:01 PM
So, it was an oracle thing, afterall.

Thank you, that did it.

Alin

DebbieKat
05-20-2008, 12:08 PM
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

MattCasters
05-20-2008, 02:05 PM
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

DebbieKat
05-20-2008, 02:09 PM
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.

MattCasters
05-20-2008, 03:58 PM
As you like, but a little bit of context is always nice.