Read out database

Hey folks,

 

I have a problem to read out a database. With the “Database Reader” I am able to connect to the database. But the moment if I want to print out the date over the “Interactive Table” an error occurred.

  Execute failed: Encountered duplicate row ID  "Row1" at row number 201    

Then I searched the forum and found this link: https://tech.knime.org/forum/knime-reporting/incompatible-between-knime-and-netezza

 

I followed these steps form tobias.koetter but I still get an error. Now it is a different error but it is still an error:

ERROR Database Table Selector 2:6        Execute failed: Error while validating SQL query: [interclient][interbase]Dynamic SQL Error

[interclient][interbase]SQL error code = -104

[interclient][interbase]Token unknown - line 1, char 15

[interclient][interbase]SELECT

 

The driver for the jdbc-database is included under File -> Preferences -> KNIME -> Databases. I can access to the database but I cannot print out the input.

 

What am I doing wrong? How can I fix this problem?

 

Thanks in advance.

Hi,

it seems to be a problem with the query that gets executed particularly the token/word in line word at position 15 seems to cause the problem. You can have a look in the KNIME log to see the SQL query that is send to the db to see what might cause this problem. To view the KNIME log open KNIME and go to View->Open KNIME log.

Bye

Tobias

Thanks for your reply.

I opened the log-file but I cannot found something weird---and, to be honest, there is a lot of information.

My the next step was to reduce the size of the database. As a result I can access and print out the database.

If I want to access the original database I get the same error as above mentioned:

Execute failed: Encountered duplicate row ID  "Row1" at row number 201

 

When I look into the database with the “normal” database-program I cannot see any duplicated rows---or anything strange. And the error shows always this number.

 

What am I missing?

Hi,

this is a problem with the JDBC driver which does not return a unique row id when a client calls the getRow() method.. The Database Connection Table Reader allows you to not use the database row id but an internal counter. So instead of using the Database Reader you should use the Database Connector, Database Table Selector and then the Database Connection Table Reader with the "Use database row id" option disabled.

Bye

Tobias

Hi tobias.koetter,

thanks for your answer. I followed your advice and used Database Connector, Database Table Selector and Database Connection Table Reader with the "Use database row id" option disabled. After the workflow is executed, the Database Connector shows a green status but Database Table Selector shows an error calling:

ERROR Database Table Selector 0:6        
Execute failed: Error while validating SQL query: [interclient][interbase]Dynamic SQL Error
[interclient][interbase]SQL error code = -104
[interclient][interbase]Token unknown - line 1, char 15
[interclient][interbase]SELECT

 

The SQL Statement calls

 

SELECT * FROM Test

 

I also used lower case but the result is still the same.

 

Any help will be greatly appreciated.

Hi,

this is a SQL syntax exception as described in my comment #2. Is it possible that interbase does not support subqueries? When you use the described workflow KNIME is creating a subquery for your statement such as:

  select * from (SELECT * FROM Test) limit 0 

to get the metadata of the table.

Bye

Tobias

Hi,

yes, you are right, Interbase does not support derived tables (http://stackoverflow.com/questions/6408627/help-with-sql-query-sub-query-using-interbase). Apparently I can confirm this problem.

Is there another way to connect to a database with the interbase-driver?

 

Bye

Torwaechter