Database Table Selector and OrientDB access

KNIME Analytics Platform
#1

Hi! I’m new to KNIME and need some advise on how to solve my problem.
I’m trying to access a table located in an OrientDB sample database using the orientDB jdbc driver: orientdb-jdbc-2.2.34-all.jar. I’m able to connect to the database using the Database Connector node and can see all tables later in the Database Table Selector node.
But when the Database Table Selector query is executed with just a simple [select * from one_of_the_tables] i receive an error from OrientDB. It seems that the syntax created by the node is not accepted by OrientDB. Anyone else experienced this or have some tip what I can try in order to access the OrientDB tables?
Thanks a lot!

Error message:
ERROR Database Table Selector 0:4        Execute failed: Error while validating SQL query: Error parsing query:
SELECT * FROM (SELECT * FROM sung_by) tempTable_2878304032138197649 WHERE (1 = 0)
                                                                  ^
Encountered " <IDENTIFIER> "tempTable_2878304032138197649 "" at line 1, column 39.
Was expecting one of:
    <EOF> 
    <WHERE> ...
    <ORDER> ...
    <GROUP> ...
    <LIMIT> ...
    <SKIP2> ...
    <OFFSET> ...
    <TIMEOUT> ...
    <FETCHPLAN> ...
    <LOCK> ...
    <LET> ...
    <NOCACHE> ...
    <PARALLEL> ...
    <UNWIND> ...
    ";" ...
    <LET> ...
    <WHERE> ...
    <GROUP> ...
    <ORDER> ...
    <UNWIND> ...
    <SKIP2> ...
    <OFFSET> ...
    <LIMIT> ...
    <FETCHPLAN> ...
    <TIMEOUT> ...
    <LOCK> ...
    <PARALLEL> ...
    <NOCACHE> ...
    
	DB name="GratefulDeadConcerts"
#2

Hi,
can you check if OrientDB supports subqueries e.g.
select * from (select * from my sung_by) subTable
KNIME makes heavy use of this queries in the DB nodes e.g. to only fetch the meta data for a query. If this is the case you can only use the Database Reader node to retrieve data from the DB since it sends the query without alteration to the DB and reads the returned result into a KNIME table. Make sure that the Run SQL query only during execute, skips configure option is enabled.
Bye
Tobias

#3

Hi! and thanks for the response. It seems that the version we have doesn’t support giving the subquery an alias. The query 

select * from (select * from sung_by)
executes well inside OrientDB, but when adding an alias like 
select * from (select * from sung_by)  subTable
or 
select * from (select * from sung_by)  as subTable
I will receive a syntax error.
Then we know the limitations. If there is a workaround (other than using the Database Reader) it will be appreciated.
BR,
Johan