Unable to edit Tranformation

Guest ●

Hi Ruth,

thanks for the logs, after looking through them all, and the first thing I'd feel I should say is that I can see you are using the demo HSQL database as your Yellowfin config database and we really don't recommend at all because it is not a robust database and liable to corruption, it is only meant to be used for a quick sort of a demo but definitely not for more than that.

So that's the matter of your Yellowfin config database, and now onto the actual error you are experiencing. I can see that all through the yellowfin.log there are many errors because the connection to your HSQL Yellowfin config database was timed out:

YF:2018-04-17 15:15:14:ERROR (DBAction:error) - Error occured doing insert: java.lang.Exception: DBAction.createStatement() exception
java.lang.Exception: DBAction.createStatement() exception
	at com.hof.util.DBAction.C(DBAction.java:651)
	at com.hof.util.DBAction.doInsert(DBAction.java:781)
|
|
|
Caused by: java.sql.SQLNonTransientConnectionException: connection exception: connection does not exist
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.connectionClosedException(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.checkClosed(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.createStatement(Unknown Source)
	at com.hof.util.i4Connection.createStatement(i4Connection.java:165)
	at com.hof.util.DBAction.C(DBAction.java:628)
	... 42 more
Caused by: org.hsqldb.HsqlException: connection exception: connection does not exist
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	... 48 more
YF:2018-04-17 15:15:14:ERROR (TableInputETLStep:error) - Error: java.lang.Exception: Exception selecting data from database
java.lang.Exception: Exception selecting data from database
	at com.hof.util.DBAction.doSelect(DBAction.java:956)
	at com.hof.util.DBAction.doSelect(DBAction.java:817)
	at com.hof.mi.etl.step.definition.TableInputETLStep.processEndRows(TableInputETLStep.java:125)
	at com.hof.mi.etl.step.AbstractETLCachedStep.endRows(AbstractETLCachedStep.java:115)
	at com.hof.mi.etl.runner.ExecutionHead.call(ExecutionHead.java:29)
	at com.hof.mi.etl.runner.ETLRunnerImpl.run(ETLRunnerImpl.java:157)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:153)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:41)
	at com.hof.mi.util.background.ExecutionQueue$TaskRunner.call(ExecutionQueue.java:465)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver]Invalid SQL specified.

and then this causes a flow-on effect thus further errors are thrown, ending up with that "Invalid SQL Specified" which happens I imagine because there was no SQL specified because Yellowfin wasn't able to retrieve because the connection was closed.

So because of all those timeout errors I would suggest to increase the of your JDBCTimeout value (i.e. this is the timeout value of the connection to your Yellowfin database, not the data sources.), by default it is 5 minutes, so you may as well increase it to 60 minutes by following the instructions in the below Knowledge Base article:

https://community.yellowfinbi.com/knowledge-base/article/how-to-increase-the-connection-timeout-to-the-yellowfin-database

and as well as that I think it would also be worthwhile turning on the JDBCVerify feature which regularly checks the connection to the YF DB and tries to re-open it if it has become corrupted or closed.

https://community.yellowfinbi.com/knowledge-base/article/jdbc-verify

I hope that helps resolve the issue, please let me know how you get on with it.

regards,

David

Hi Ruth,

thanks for the logs, after looking through them all, and the first thing I'd feel I should say is that I can see you are using the demo HSQL database as your Yellowfin config database and we really don't recommend at all because it is not a robust database and liable to corruption, it is only meant to be used for a quick sort of a demo but definitely not for more than that.

So that's the matter of your Yellowfin config database, and now onto the actual error you are experiencing. I can see that all through the yellowfin.log there are many errors because the connection to your HSQL Yellowfin config database was timed out:

YF:2018-04-17 15:15:14:ERROR (DBAction:error) - Error occured doing insert: java.lang.Exception: DBAction.createStatement() exception
java.lang.Exception: DBAction.createStatement() exception
	at com.hof.util.DBAction.C(DBAction.java:651)
	at com.hof.util.DBAction.doInsert(DBAction.java:781)
|
|
|
Caused by: java.sql.SQLNonTransientConnectionException: connection exception: connection does not exist
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.connectionClosedException(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.checkClosed(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.createStatement(Unknown Source)
	at com.hof.util.i4Connection.createStatement(i4Connection.java:165)
	at com.hof.util.DBAction.C(DBAction.java:628)
	... 42 more
Caused by: org.hsqldb.HsqlException: connection exception: connection does not exist
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	... 48 more
YF:2018-04-17 15:15:14:ERROR (TableInputETLStep:error) - Error: java.lang.Exception: Exception selecting data from database
java.lang.Exception: Exception selecting data from database
	at com.hof.util.DBAction.doSelect(DBAction.java:956)
	at com.hof.util.DBAction.doSelect(DBAction.java:817)
	at com.hof.mi.etl.step.definition.TableInputETLStep.processEndRows(TableInputETLStep.java:125)
	at com.hof.mi.etl.step.AbstractETLCachedStep.endRows(AbstractETLCachedStep.java:115)
	at com.hof.mi.etl.runner.ExecutionHead.call(ExecutionHead.java:29)
	at com.hof.mi.etl.runner.ETLRunnerImpl.run(ETLRunnerImpl.java:157)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:153)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:41)
	at com.hof.mi.util.background.ExecutionQueue$TaskRunner.call(ExecutionQueue.java:465)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver]Invalid SQL specified.

and then this causes a flow-on effect thus further errors are thrown, ending up with that "Invalid SQL Specified" which happens I imagine because there was no SQL specified because Yellowfin wasn't able to retrieve because the connection was closed.

So because of all those timeout errors I would suggest to increase the of your JDBCTimeout value (i.e. this is the timeout value of the connection to your Yellowfin database, not the data sources.), by default it is 5 minutes, so you may as well increase it to 60 minutes by following the instructions in the below Knowledge Base article:

https://community.yellowfinbi.com/knowledge-base/article/how-to-increase-the-connection-timeout-to-the-yellowfin-database

and as well as that I think it would also be worthwhile turning on the JDBCVerify feature which regularly checks the connection to the YF DB and tries to re-open it if it has become corrupted or closed.

https://community.yellowfinbi.com/knowledge-base/article/jdbc-verify

I hope that helps resolve the issue, please let me know how you get on with it.

regards,

David

1

Guest ●

Hi Ruth,

thanks for the screen shots, I have looked through them and from studying the 1st error:

17/04/2018 23:02 Prime ETL Failed - java.lang.Throwable: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Column PayBank in table PUB.AcctMaster has value exceeding its max length or precision.

I would be interested to know the data type of PUB.AcctMaster.PayBank and also I would like to see its values.

And then from the 2nd error:

17/04/2018 23:09 Prime ETL Failed - java.lang.Throwable: [DataDirect][OpenEdge JDBC Driver] Invalid SQL specified

I would like to see the SQL you have entered (I'm assuming the FreehandSQL extraction step was used)

And at this point unfortunately I can't really get anything from that NullPointerException stack trace until you tell me which build of 7.4 you are using (because different builds may have different line numbers from one another)

Error: java.lang.NullPointerException At:
com.hof.web.action.ETLInfoSummaryAction.execute(ETLInfoSummaryAction.java:113)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:229)

regards,

David

Hi Ruth,

thanks for the screen shots, I have looked through them and from studying the 1st error:

17/04/2018 23:02 Prime ETL Failed - java.lang.Throwable: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Column PayBank in table PUB.AcctMaster has value exceeding its max length or precision.

I would be interested to know the data type of PUB.AcctMaster.PayBank and also I would like to see its values.

And then from the 2nd error:

17/04/2018 23:09 Prime ETL Failed - java.lang.Throwable: [DataDirect][OpenEdge JDBC Driver] Invalid SQL specified

I would like to see the SQL you have entered (I'm assuming the FreehandSQL extraction step was used)

And at this point unfortunately I can't really get anything from that NullPointerException stack trace until you tell me which build of 7.4 you are using (because different builds may have different line numbers from one another)

Error: java.lang.NullPointerException At:
com.hof.web.action.ETLInfoSummaryAction.execute(ETLInfoSummaryAction.java:113)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:229)

regards,

David

Reply URL

1

Ruth ●

Hie David

First question, as attached in paybank column.docx screenshots.

Second question, l did not use any FreehandSQL, l just combined two tables, Acctmaster and Matchdet tables

Third question, attached in Yellowfin Build.docx

Files:

URL

1

Guest ●

Hi Ruth,

thanks for that, although unfortunately when I looked at the relevant source code of 7.4 20180104 for that error I found that it is not helpful at all, it is just the following:

} catch (ActionErrorsException ae) {
    saveErrors(request, ae.getErrors());
    saveToken(request);
    return mapping.findForward("failure");

which shows that the null pointer exception during the exception handling process! In other words, the problem occurred well before this null pointer exception occurred.

Could I also please ask you for your log files (yellowfin.log, yellowfin.stdout if you have it, and also there might be some helpful information in the source logs).

thanks,

David

Hi Ruth,

thanks for that, although unfortunately when I looked at the relevant source code of 7.4 20180104 for that error I found that it is not helpful at all, it is just the following:

} catch (ActionErrorsException ae) {
    saveErrors(request, ae.getErrors());
    saveToken(request);
    return mapping.findForward("failure");

which shows that the null pointer exception during the exception handling process! In other words, the problem occurred well before this null pointer exception occurred.

Could I also please ask you for your log files (yellowfin.log, yellowfin.stdout if you have it, and also there might be some helpful information in the source logs).

thanks,

David

URL

1

Ruth ●

Hie David,

Please find attached logs.

Files:

URL

1

Guest ● ●

Hi Ruth,

thanks for the logs, after looking through them all, and the first thing I'd feel I should say is that I can see you are using the demo HSQL database as your Yellowfin config database and we really don't recommend at all because it is not a robust database and liable to corruption, it is only meant to be used for a quick sort of a demo but definitely not for more than that.

So that's the matter of your Yellowfin config database, and now onto the actual error you are experiencing. I can see that all through the yellowfin.log there are many errors because the connection to your HSQL Yellowfin config database was timed out:

YF:2018-04-17 15:15:14:ERROR (DBAction:error) - Error occured doing insert: java.lang.Exception: DBAction.createStatement() exception
java.lang.Exception: DBAction.createStatement() exception
	at com.hof.util.DBAction.C(DBAction.java:651)
	at com.hof.util.DBAction.doInsert(DBAction.java:781)
|
|
|
Caused by: java.sql.SQLNonTransientConnectionException: connection exception: connection does not exist
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.connectionClosedException(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.checkClosed(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.createStatement(Unknown Source)
	at com.hof.util.i4Connection.createStatement(i4Connection.java:165)
	at com.hof.util.DBAction.C(DBAction.java:628)
	... 42 more
Caused by: org.hsqldb.HsqlException: connection exception: connection does not exist
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	... 48 more
YF:2018-04-17 15:15:14:ERROR (TableInputETLStep:error) - Error: java.lang.Exception: Exception selecting data from database
java.lang.Exception: Exception selecting data from database
	at com.hof.util.DBAction.doSelect(DBAction.java:956)
	at com.hof.util.DBAction.doSelect(DBAction.java:817)
	at com.hof.mi.etl.step.definition.TableInputETLStep.processEndRows(TableInputETLStep.java:125)
	at com.hof.mi.etl.step.AbstractETLCachedStep.endRows(AbstractETLCachedStep.java:115)
	at com.hof.mi.etl.runner.ExecutionHead.call(ExecutionHead.java:29)
	at com.hof.mi.etl.runner.ETLRunnerImpl.run(ETLRunnerImpl.java:157)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:153)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:41)
	at com.hof.mi.util.background.ExecutionQueue$TaskRunner.call(ExecutionQueue.java:465)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver]Invalid SQL specified.

and then this causes a flow-on effect thus further errors are thrown, ending up with that "Invalid SQL Specified" which happens I imagine because there was no SQL specified because Yellowfin wasn't able to retrieve because the connection was closed.

So because of all those timeout errors I would suggest to increase the of your JDBCTimeout value (i.e. this is the timeout value of the connection to your Yellowfin database, not the data sources.), by default it is 5 minutes, so you may as well increase it to 60 minutes by following the instructions in the below Knowledge Base article:

https://community.yellowfinbi.com/knowledge-base/article/how-to-increase-the-connection-timeout-to-the-yellowfin-database

and as well as that I think it would also be worthwhile turning on the JDBCVerify feature which regularly checks the connection to the YF DB and tries to re-open it if it has become corrupted or closed.

https://community.yellowfinbi.com/knowledge-base/article/jdbc-verify

I hope that helps resolve the issue, please let me know how you get on with it.

regards,

David

Hi Ruth,

thanks for the logs, after looking through them all, and the first thing I'd feel I should say is that I can see you are using the demo HSQL database as your Yellowfin config database and we really don't recommend at all because it is not a robust database and liable to corruption, it is only meant to be used for a quick sort of a demo but definitely not for more than that.

So that's the matter of your Yellowfin config database, and now onto the actual error you are experiencing. I can see that all through the yellowfin.log there are many errors because the connection to your HSQL Yellowfin config database was timed out:

YF:2018-04-17 15:15:14:ERROR (DBAction:error) - Error occured doing insert: java.lang.Exception: DBAction.createStatement() exception
java.lang.Exception: DBAction.createStatement() exception
	at com.hof.util.DBAction.C(DBAction.java:651)
	at com.hof.util.DBAction.doInsert(DBAction.java:781)
|
|
|
Caused by: java.sql.SQLNonTransientConnectionException: connection exception: connection does not exist
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCUtil.connectionClosedException(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.checkClosed(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.createStatement(Unknown Source)
	at com.hof.util.i4Connection.createStatement(i4Connection.java:165)
	at com.hof.util.DBAction.C(DBAction.java:628)
	... 42 more
Caused by: org.hsqldb.HsqlException: connection exception: connection does not exist
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	... 48 more
YF:2018-04-17 15:15:14:ERROR (TableInputETLStep:error) - Error: java.lang.Exception: Exception selecting data from database
java.lang.Exception: Exception selecting data from database
	at com.hof.util.DBAction.doSelect(DBAction.java:956)
	at com.hof.util.DBAction.doSelect(DBAction.java:817)
	at com.hof.mi.etl.step.definition.TableInputETLStep.processEndRows(TableInputETLStep.java:125)
	at com.hof.mi.etl.step.AbstractETLCachedStep.endRows(AbstractETLCachedStep.java:115)
	at com.hof.mi.etl.runner.ExecutionHead.call(ExecutionHead.java:29)
	at com.hof.mi.etl.runner.ETLRunnerImpl.run(ETLRunnerImpl.java:157)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:153)
	at com.hof.mi.util.background.ETLQueueItem.process(ETLQueueItem.java:41)
	at com.hof.mi.util.background.ExecutionQueue$TaskRunner.call(ExecutionQueue.java:465)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver]Invalid SQL specified.

and then this causes a flow-on effect thus further errors are thrown, ending up with that "Invalid SQL Specified" which happens I imagine because there was no SQL specified because Yellowfin wasn't able to retrieve because the connection was closed.

So because of all those timeout errors I would suggest to increase the of your JDBCTimeout value (i.e. this is the timeout value of the connection to your Yellowfin database, not the data sources.), by default it is 5 minutes, so you may as well increase it to 60 minutes by following the instructions in the below Knowledge Base article:

https://community.yellowfinbi.com/knowledge-base/article/how-to-increase-the-connection-timeout-to-the-yellowfin-database

and as well as that I think it would also be worthwhile turning on the JDBCVerify feature which regularly checks the connection to the YF DB and tries to re-open it if it has become corrupted or closed.

https://community.yellowfinbi.com/knowledge-base/article/jdbc-verify

I hope that helps resolve the issue, please let me know how you get on with it.

regards,

David

Reply URL

1

Ruth ●

Hie Dave

How do l resolve the issue as attached, my transformation flow had no error upon saving, but upon running the flow it was not successful.

/SnPifQAAAABJRU5ErkJggg==

Below are the available Tranformations steps l am able to use

/B6ZKO8aUfiR1AAAAAElFTkSuQmCC

Regards

Ruth

Reply URL

1

Guest ●

Hi Ruth,

from what I can see in the screenshot the transformation is failing in the Output step with the error

java.lang.Thowable Only dates between January 1, 1753 and December 31, 9999 are accepted.

this means that the JDBC driver you are using to connect to the SQL Server database where the transformation results are to be saved has detected that a date that is less than 01/01/1753 or greater than 31/12/9999 had tried to be inserted into the table. This error most commonly occurs when the source database contains a zero date (or "0000-00-00 00:00:00").

Could you please search through all the date values from your input source and check whether there are any zero values, or values < 01/01/1753 or > 31/12/9999, and if you find any the easiest approach to fix the problem is to simply change them to NULL.

Please let me know what you find.

regards,

David

Reply URL

1

Ruth ●

Hie David

Changing the values of the < 01/01/1753 or > 31/12/9999 in my source data resolved the issue, my transformation ran successfully.

Regards

Ruth

Reply URL

1

Guest ●

that's great news Ruth, thanks for letting me know!

regards,

David

Reply URL

Unable to edit Tranformation

Replies (6)