Query Issue - Generic JDBC - Execute

[shane]

Using a Generic JDBC - Execute snap at the beginning of my pipeline with a hard-coded SQL query that has no issues running from any other place (workbench, phpstorm, etc) returns 800 results and an error instead of the full result-set.

The error is “SQL [{my_query}] Zero date value prohibited”

If I am running a single query, and am already getting 800 results before the error, then why and how is this error occurring, and why is it blaming the sql?

I have other pipelines that have pulled in well more results than this query results in. I cannot find any reason that there would be an issue with a Date field as far as the SQL goes so this error response is very misleading.

Looking at the stack trace: “org.jooq.exception.DataAccessException: SQL [*my_query*] ]; Zero date value prohibited\n\tat org.jooq_3.9.1.MYSQL.debug(Unknown Source)\n\tat org.jooq.impl.Tools.translate(Tools.java:1983)\n\tat org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:676)\n\tat org.jooq.impl.CursorImpl$CursorIterator.fetchOne(CursorImpl.java:1569)\n\tat org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1520)\n\tat org.jooq.impl.CursorImpl.fetch(CursorImpl.java:342)\n\tat com.snaplogic.snap.api.sql.DatabaseCursorImpl.fetchOne(DatabaseCursorImpl.java:78)\n\tat com.snaplogic.snap.api.sql.DatabaseUtils.processSelect(DatabaseUtils.java:251)\n\tat com.snaplogic.snap.api.sql.DatabaseUtils.processSelect(DatabaseUtils.java:284)\n\tat com.snaplogic.snaps.sql.SimpleSqlExecuteSnap.processQuery(SimpleSqlExecuteSnap.java:287)\n\tat com.snaplogic.snaps.sql.SimpleSqlExecuteSnap.processDocument(SimpleSqlExecuteSnap.java:245)\n\tat com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:270)\n\tat com.snaplogic.snap.api.ExecutionUtil.process(ExecutionUtil.java:106)\n\tat com.snaplogic.snap.api.ExecutionUtil.execute(ExecutionUtil.java:70)\n\tat .......”

The only thing I can think of is that Snaplogic is trying to force a data-point into a Date field when the row has a null value for that field, but in the set of 800 results there are null values for date fields which can return null from the query already. I cannot see any reason why I should be seeing this error, and everything points to the Java component used to parse that row of data.

[shane]

I found that there was an invalid date-time value set on one record which was the issue.
This does make me curious though, why would the whole process stop because of one row instead of either casting it as a string or just skipping only that one record and outputting an error for it?

The hard-stop seems counter to what snaplogic is supposed to do with creating a document for each row of data from the query.

[dmiller]

On the Views tab of the Generic JDBC Execute Snap, what do you have Error set to?

[shane]

I have all of my pipelines set to use a generic error pipeline that feeds the data into an s3 bucket.

Which is funny because that generic error pipeline is working fine and logging currently, but didn’t log anything for this error.