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
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
-
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! -
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: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: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: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 -
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, -
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.
Please sign in to leave a comment.
Comments 6