Hi I downloaded KNIME a few days ago and I'm getting more and more impressed by the possibilities given by this software.
However, I've been trying to use the Database Reader Node, but it seems that it won't process complex requests, ie involving at least 2 tables linked by an external key :
ex :
select tableA.col1, tableA.col2, tableB.col1, tableB.col2
from tableA, tableB
where tableA.id=tableB.col3
Is this normal ? And if so, will this kind of node allow users to execute more complex SQL requests in the future versions of KNIME ?
Thanks in advance !
My understanding is that Knime replies on your jdbc driver and database for it's capabilities.
I'm not sure how complex the SQL statement is which you are trying to execute but perhaps try executing it outside of Knime to validate the SQL first and then check on your jdbc driver.
Jay
my request works perfectly on any SQL editor (Toad, sqlplus), but won't work with the database reader node as long as it involves more than one table.
Are you sure that you ran this statement on your database system outside of KNIME. Please check your tableX names, I am sure you meant tableA instead of table1, aren't you? The SELECT statement should then work just fine in KNIME!
I just corrected the request I put in my first message. You 're right, I meant tableA and not table1.
However :
- When only one table is involved, the request can be executed (orange light).
- When 2 tables are involved, I get the following WARNING on the Node (not in the console) : "Warning : could not decrypt password" and the light is red. (I remember having entered this password while configuring the database reader.)
That's why I'm quite confused : the connection to my oracle database works fine. I only get this warning in this particular case ! (and furthermore I don't really understand its meaning.)
Thanks a lot for answering so quickly !
Maybe you can have a look at the log file (the location is printed on the console view at startup), the error message should be more elaborate there. Maybe you get a hint or can post additional information?
Regards,
Fabian
Here is the message :
WARN Database Reader Configure failed: Could not decrypt password.
As I said, the oracle database configuration is fine, since everything works perfectly with single table requests ! (nothing appears in the console then)
Sorry for being so imprecise - but I'm interested in the more elaborate DEBUG messages.
In File/Preferences/KNIME you can set the log level of either the console view or the log file. Per default the log file logs debug messages. So please have a look in the log file and either post or send me the debug messages. I will have a look at it as soon as possible. Maybe I can help - not sure about it :)
Great, Here are the logs for the following requests :
2-table request :
request wrote:select * from bo, cds where bo.id=cds.id
logs wrote:DEBUG NodeContainerEditPart Database Reader: Opening node dialog after double click...
INFO Database Reader reset
WARN Database Reader Configure failed: Could not decrypt password.
INFO Database Reader reset
DEBUG WorkflowEditor Workflow event triggered: WorkflowEvent [type=NodeReset;old=null;new=null;timestamp=Jun 23, 2008 9:36:48 AM]
DEBUG WorkflowRootEditPart WorkflowRoot: workflow changed, refreshing children/connections..
--> Doesn't work.
Single table request :
request wrote:select * from bo
logs wrote:DEBUG NodeContainerEditPart Database Reader: Opening node dialog after double click...
INFO Database Reader reset
DEBUG WorkflowEditor Workflow event triggered: WorkflowEvent [type=NodeConfigured;old=null;new=null;timestamp=Jun 23, 2008 9:41:39 AM]
DEBUG WorkflowRootEditPart WorkflowRoot: workflow changed, refreshing children/connections..
DEBUG NodeContainerEditPart Database Reader: Opening node dialog after double click...
--> Works fine.
Thanks a lot for your help !
Hmm - too bad. I remember having an elaborate Oracle SQL exception once with the same warning message.
So probably it is indeed a problem with the encryption key.
Have you seen this forum topic already?
http://www.knime.org/node/344
It's about a very similar problem...
Maybe it helps you as well?
Yes I had already seen that topic before posting mine, but none of the solutions would work for me.
But I think I found the solution to my problem :
When replacing the request (which does not work) :
Old request wrote:SELECT cds.id, bo.id FROM cds, bo where bo.id=cds.id
by the same request with aliases :
New request wrote:SELECT cds.id CDS_ID, bo.id BO_ID FROM cds, bo where bo.id=cds.id
It works perfectly.
It seems that knime's SQL editor does not accept the presence of 2 columns with the same name in the output (in this case, the "ID" column). That's why the request "SELECT * from cds, bo where bo.id=cds.id" wouldn't work either.
You have to give an alias to the output, here CDS_ID and BO_ID. Otherwise it won't work.
I think it would probably be more userfriendly to accept this particular case ("Old request") in the future versions of KNIME, by automatically appending a number to the columns with same IDs in the output. (In this case ID_1 and ID_2, like in "toad SQL editor" for example).
Thanks a lot for your patience !
Sorry, but this has something to do with the underlying database system. I doubt that we will start parsing SQL statements in order to insert random identifiers to make the columns unique. In KNIME, the query you enter inside the database dialog is just passed to the java database connection and is executed directly in the db system.
Cheers, Thomas
Hi, I think I somehow misunderstood your last posting. I was a little bit confuse by the totally wrong warning message. This is something I really need to clean-up. You are correct, the statement has to work fine and KNIME should in this case not complain about duplicate column names. I will fix this issue and handle this duplicates. Thanks again for your feedback! Thomas
Great, that will be very helpful !
Thanks a lot for you help and patience !
Philippe