I use a JDBC driver to connect to Salesforce. The driver has limitations which are requiring KNIME workarounds that dont seem to be working, I'm hoping someone may be able to offer guidence.
As shown below I've tried a number of query methods
The Database reader node fails due to a JDBC driver limitation: Query will batch results into 250rows, so if the batchSize is 500, it will result in 2 submissions to Salesforce. (this causes an issue with database row id, documented here).
As mentioned in that post I tried Database Connector, with the Database Connection Table Reader. But I'm finding that the Database Connector errors with "unexpected token 'FROM'"
Query:
Select *
from Account
Where LastModifiedDate >= '2016-07-01 00:00:00'
there seems to be something wrong with the queries. Could you please attach a screenshot with the configuration of your DB query nodes? It may also depend on how the JDBC driver is configured.
Select *
from Account
Where LastModifiedDate >= '2016-07-01 00:00:00'
LIMIT 250
Same node( errors with duplicate row ID)
Select *
from Account
Where LastModifiedDate >= '2016-07-01 00:00:00'
LIMIT 251
Taking the "working query" into a DB Table Selector node I get the following error:
ERROR Database Table Selector 3:62 Execute failed: Error while validating SQL query: The expression "2016-07-01 00:00:00'
LIMIT 250" is not resolved, following "SELECT * FROM (Select *
from Account
Where LastModifiedDate >= '".
Same DB Table Selector node, I then simplified the query & recieve this error
Select *
from Account
ERROR Database Table Selector 3:62 Execute failed: Error while validating SQL query:
SELECT FROM null WHERE (Id = null)
^
ERROR at Row:1:Column:7
unexpected token: 'FROM'
I'm trying to understand the differences between the DB Table Selector node & DB reader node, the behaviour seems to vairy while using the same (working) DB Connector node and I dont know why.
The database nodes make heavy use of nested queries. You can see this if you inspect the SQL query built by a series of database nodes (look at the output port view). Some databases (or their drivers) are not fully SQL compliant and cannot deal with nested queries. It looks like yours is one of those.
The Database Reader node is the only node that executes the query as entered in the dialog. This is the reason why this node works with your driver, and the others don't.
did you try with the DB Reader node standalone? It can open a connection to a DB and perform the SQL query on its own. This is what I use in most of my workflows and it works as expected (just note our CRM uses MS SQL).
I still believe the problem is with the setup/behavior of your JDBC driver toward the salesforce.com database. Have a look at the connection URL. Is that the right one for the DB connection? Do you have any other way to check whether the connection to the DB works properly with the same setup (e.g. outside KNIME)?
I find wierd that you get error messages on the syntax of the SQL queries.
Yes the DB Reader node is very useful and it's my usual "go to" node. The JDBC driver does indeed work with the DB Reader node for queries under 250 rows. This particular JDBC driver pulls data in batches (of 250) which resets the DB Reader node function (ResultSet.getRow()), causing the issue.
The workaround for this, is to use a combinition of DB Table Connector node & DB Connection Table Reader node which uses an internal counter instead of the ResultSet.getRow() id. Functionaly we may soon see in the DB Reader node
My issue (clarified by @thor above) is "database nodes make heavy use of nested queries. ...Some databases (or their drivers) are not fully SQL compliant and cannot deal with nested queries." Salesforce uses propritory language, known as SOQL & I've no information on how the driver deals with the connections! But this is the reason my simple statements are failing with the 'unexpected token FROM' errors.
Interestingly "The Database Reader node is the only node that executes the query as entered in the dialog." , so the DB reader node will execute as expected. However due to the ResultSet.getRow() function & my JDBC driver limited 250 rows, my options are limited.
I reckon I'll export the tables via flat file (oh the data cleaning horror), & hope to see the database row id checkbox introduced to the Database Reader node in the Dec 6th update.
This is in line with what I'm seeing (and could not explain).
To your point " inspect the SQL query built by a series of database nodes" , I can see the data output in the output port view but is it also possible to see the SQL that knime commits?
In the case of eg a Database Table Connector erroring, it would be very useful to see the post processed SQL that is causing the node to error. Even a log file would provide great insight