DBExecute -> Unexpected null value

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

TomFS
Posts: 3
Joined: Tue Jun 18, 2013 3:15 pm

DBExecute -> Unexpected null value

Postby TomFS » Tue Jun 18, 2013 3:23 pm

I am trying to use the DB Execute component with a file defined SQL statement. I want to create a series of graphs each using a shared metadata file, connection file and SQL file defining the stored procedure to execute. I did not find much specific information about externalizing use of a file to share SQL query functionality so maybe I am missing another simpler method of accomplishing the same thing.

The SQL is fairly basic and does not include any parameters yet. I have tested the SQL statement on the SQL server itself as well as executing by entering the SQL within the 'SQL query' parameter of the DBExecute component. Both work as expected. But when I move the identical SQL code to a file named 'Load.sql' and point to the file with the 'Query URL' DBExecute parameter it gives the below error.

Thank you for your assistance
Below is the full output from the log ->

INFO [DBEXECUTE_0] - Executing statement: EXECUTE [MasterData].[dbo].[InsertSourceData]
@FileName = 'test'
,@SourceName = 'test'
,@SourceRecordId = 'test'
,@Vintage = '6/17/2013'
ERROR [WatchDog_0] - Component [DBExecute:DBEXECUTE] finished with status ERROR.
Unexpected null value.
ERROR [WatchDog_0] - Error details:
org.jetel.exception.JetelRuntimeException: Component [DBExecute:DBEXECUTE] finished with status ERROR.
at org.jetel.graph.Node.createNodeException(Node.java:535)
at org.jetel.graph.Node.run(Node.java:514)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.lang.NullPointerException
at org.jetel.util.ReadableChannelPortIterator.hasNext(ReadableChannelPortIterator.java:173)
at org.jetel.util.ReadableChannelIterator.hasNext(ReadableChannelIterator.java:212)
at org.jetel.component.DBExecute.execute(DBExecute.java:628)
at org.jetel.graph.Node.run(Node.java:485)
... 1 more

kubosj
Posts: 372
Joined: Thu Jan 12, 2012 9:10 am

Re: DBExecute -> Unexpected null value

Postby kubosj » Wed Jun 19, 2013 8:18 am

Hi Tom,

I identified this behavior as bug. Please see https://bug.javlin.eu/browse/CLO-1047 for details and workaround.

Sorry for any inconveniences!
Jaroslav Kubos
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com

skaszyk
Posts: 12
Joined: Mon Apr 14, 2014 9:52 pm

Re: DBExecute -> Unexpected null value

Postby skaszyk » Thu Apr 24, 2014 9:39 pm

I'm getting two different error messages depending on whether I run the graph from Eclipse or command line.

Eclipse:
My graph was working without error until I added a DBExecute at the beginning to truncate the table. DBexecute does not read from a file but uses the SQL Query "truncate table <my_table>;". Now, it fails at varying stages of the the data load but it always gets past the truncation and loads some data before throwing an "Unexpected Null Value" error."

Command Line:
When I run the same graph from the command line, I get a "No reference to connected input port found in file URL." error.

I tried adding the "port:$0.field1:discrete" to the DBExecute Query URL but because I'm already using the SQL Query, it throws a warning and then I get an "Input port is not defined for 'port:$0.field1:discrete'." error.

Eclipse Error:

Code: Select all

ERROR [WatchDog_0] - Component [Read File:READ_FILE] finished with status ERROR.
 Unexpected null value.
ERROR [WatchDog_0] - Error details:
org.jetel.exception.JetelRuntimeException: Component [Read File:READ_FILE] finished with status ERROR.
   at org.jetel.graph.Node.createNodeException(Node.java:543)
   at org.jetel.graph.Node.run(Node.java:522)
   at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NullPointerException
   at org.jetel.util.ReadableChannelPortIterator.hasNext(ReadableChannelPortIterator.java:173)
   at org.jetel.util.ReadableChannelIterator.hasNext(ReadableChannelIterator.java:212)
   at org.jetel.util.MultiFileReader.nextSource(MultiFileReader.java:272)
   at org.jetel.util.MultiFileReader.getNext(MultiFileReader.java:432)
   at org.jetel.component.DataReader.execute(DataReader.java:268)
   at org.jetel.graph.Node.run(Node.java:493)
   ... 1 more


Command Line Error:

Code: Select all

ERROR [WatchDog_0] - Component [UniversalDataReader:UNIVERSAL_DATA_READER] finished with status ERROR. (In0: 0 recs, Out0: 0 recs)
 Component pre-execute initialization failed.
  No reference to connected input port found in file URL.
ERROR [WatchDog_0] - Error details:
org.jetel.exception.JetelRuntimeException: Component [UniversalDataReader:UNIVERSAL_DATA_READER] finished with status ERROR. (In0: 0 recs, Out0: 0 recs)
   at org.jetel.graph.Node.createNodeException(Node.java:571)
   at org.jetel.graph.Node.run(Node.java:547)
   at java.lang.Thread.run(Thread.java:744)
Caused by: Element [UNIVERSAL_DATA_READER:UniversalDataReader]-Component pre-execute initialization failed.
   at org.jetel.graph.Node.run(Node.java:501)
   ... 1 more
Caused by: [attribute = fileURL]-No reference to connected input port found in file URL.
   at org.jetel.util.ReadableChannelPortIterator.init(ReadableChannelPortIterator.java:90)
   at org.jetel.util.ReadableChannelIterator.initPortFields(ReadableChannelIterator.java:200)
   at org.jetel.util.ReadableChannelIterator.common(ReadableChannelIterator.java:141)
   at org.jetel.util.ReadableChannelIterator.init(ReadableChannelIterator.java:130)
   at org.jetel.util.MultiFileReader.initChannelIterator(MultiFileReader.java:134)
   at org.jetel.util.MultiFileReader.preExecute(MultiFileReader.java:529)
   at org.jetel.component.DataReader.preExecute(DataReader.java:239)
   at org.jetel.graph.Node.run(Node.java:499)
   ... 1 more


Eclipse using work around:

Code: Select all

ERROR [main] - Error during graph initialization !
 Component [Truncate Positions Table:TRUNCATE_POSITIONS_TABLE] initilization failed.
  Input port is not defined for 'port:$0.field1:discrete'.
ERROR [main] - Error details:
Element [TRUNCATE_POSITIONS_TABLE:Truncate Positions Table]-Component [Truncate Positions Table:TRUNCATE_POSITIONS_TABLE] initilization failed.
   at org.jetel.graph.Phase.init(Phase.java:171)
   at org.jetel.graph.TransformationGraph.init(TransformationGraph.java:466)
   at org.jetel.graph.runtime.EngineInitializer.initGraph(EngineInitializer.java:255)
   at org.jetel.graph.runtime.EngineInitializer.initGraph(EngineInitializer.java:235)
   at org.jetel.main.runGraph.runGraph(runGraph.java:413)
   at org.jetel.main.runGraph.main(runGraph.java:381)
Caused by: Input port is not defined for 'port:$0.field1:discrete'.
   at org.jetel.util.ReadableChannelIterator.prepareFileIterator(ReadableChannelIterator.java:185)
   at org.jetel.util.ReadableChannelIterator.common(ReadableChannelIterator.java:138)
   at org.jetel.util.ReadableChannelIterator.init(ReadableChannelIterator.java:130)
   at org.jetel.component.DBExecute.init(DBExecute.java:365)
   at org.jetel.graph.Phase.init(Phase.java:166)
   ... 5 more

imriskal
Posts: 393
Joined: Wed Aug 15, 2012 8:18 am

Re: DBExecute -> Unexpected null value

Postby imriskal » Fri Apr 25, 2014 11:43 am

Hi skaszyk,

Ad Eclipse) Are truncation and data load performed in the same phase? If so, please try to split them. Truncation should be performed e.g. in phase 0 and data load in phase 1.

Ad Command line) Please send me your graph and I will take a look on it. It would be easier for me to find the issue this way.

Regards,
---
Lubos Imriska
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com

skaszyk
Posts: 12
Joined: Mon Apr 14, 2014 9:52 pm

Re: DBExecute -> Unexpected null value

Postby skaszyk » Mon Apr 28, 2014 4:51 pm

Thanks, I tried using multiple phases in Eclipse but got the same error. I also ran the updated graph from the command line and same error there too.

I've attached a copy of my graph.
Attachments
LoadDailyLPLPositionFile.grf
(4.6 KiB) Downloaded 184 times

imriskal
Posts: 393
Joined: Wed Aug 15, 2012 8:18 am

Re: DBExecute -> Unexpected null value

Postby imriskal » Tue Apr 29, 2014 12:47 pm

The edge between DBExecute and UniversalDataReader is not necessary because no data flows between them. Please delete it and "No reference to connected input port found in file URL." error should disappear.
---
Lubos Imriska
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com

skaszyk
Posts: 12
Joined: Mon Apr 14, 2014 9:52 pm

Re: DBExecute -> Unexpected null value

Postby skaszyk » Tue Apr 29, 2014 2:55 pm

Awesome, both version work now!

I come from a background with SSIS which requires everything to be connected.

Thanks for the quick and accurate replies!


cron