cancel
Showing results for 
Search instead for 
Did you mean: 

Error when reading from SQLMX database via JDBC connector

feenst
New Contributor II

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:

image

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.

5 REPLIES 5

akidave
Employee
Employee

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.

image

feenst
New Contributor II

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.

image

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
New Contributor II

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 …