Error when reading from SQLMX database via JDBC connector

[feenst]

I am working on a pipeline to pull data from a SQL Server database and insert into a SQLMX database.

I got this pipeline working, but it fails after the first run as it’s trying to insert records that already exist.

My thought was to use a pipeline like the following:

However, the Generic JDBC select from the SQLMX database fails with the following error:

Failure: Failed to execute select query., Reason: SQL [select * from "DEFAULT_SCHEMA"."ASSETS" limit ?]; *** ERROR[3128] LIMIT is a reserved word. It must be delimited by double-quotes to be used as an identifier. [2017-09-01 16:42:39], Resolution: Please check sql query.

I have nothing included in the limit clauses.

I am able to use the Generic JDBC - Execute snap to read from that database / table, but the snap has no head that I can connect with other snaps.

[akidave]

SQL/MX does not seem to support the LIMIT clause. You can use the Generic JDBC Execute snap, add an output view to be able to link it to downstream snaps.

[feenst]

Thanks @akidave for the tip on the output view. That worked in this scenario.
However, I don’t have any of the “limit” fields included in my UI so I’m not sure why the limit clause would be included in the query.

[akidave]

Did you get the LIMIT error during pipeline save? The preview data generation automatically adds a LIMIT to the SQL query in the SELECT snap. The LIMIT is not added during pipeline execution.

[feenst]

Yes, it seems to only happen during save.

Not sure why the SQLMX fails though when the SQL Server (via JDBC as well) succeeds as I can’t use a LIMIT clause when querying the SQL Server database directly …

[akidave]

The specific database snaps know which features are supported for each database. The generic database snaps try to detect the features supported based on the database JDBC metadata, but that is not reliable in some cases. Using Execute would be a workaround, or SELECT snaps can be used just for execution.