I’m having trouble connecting to a postgres db
connection node works but the query reader and table reader doesnt
when i try to fetch metadata i get this warn on the console:
WARN DB Query Reader 7:6 4 statements have not been closed before the closure of the managed connection.
thx in advance guys !
knime version is 5.11
Hi @YuriRibeiro , use
*/ to comment out lines in the DB Query Reader node, instead of
-- if you are going to use Evaluate because the whole statement gets concatenated into a single long string and so everything after the
-- (which includes subsequent lines that you have entered and the additional end of the wrapper select statement added by “evaluate”), becomes commented out.
If you have
Select x, y, z from mytable
-- a comment
Select * from (Select x, y, z from mytable -- a comment )
so that final additional
) is no longer part of the executable sql.
Thx but it seems that the comments are not the problem
the error occurs when i try to evaluate
the warning saing “4 statements have not been…” happens when i try to fetch metadata
Hi @YuriRibeiro , a pity that didn’t resolve it but certainly the commenting can cause problems.
Anyway, ignoring the meta fetch for a moment, what happens if you have a simple query such as
And press the evaluate button? Does it get a return from the database?
surprisingly it did returned something!
although i dont think i’m contacting the database
That will only return a date/time if there is a database on the other end. You could also try
Which will either return a result or an error from the database. It should prove you have a connection at least!
What if you try to fetch the metadata now. Does it still give the previous error?
it did returned the values showing that the schema ‘public’ indeed exists, but my query still doesnt work. it is returning the same errors saing the table doesnt exists.
I used an app called heidi to test the connection and the query, both working fine.
fetching metadata is returning the same warn message =/
So this is progress of sorts…
Try the following query
SELECT * FROM information_schema.tables
WHERE table_schema = 'public'
and see if that returns results
then try this query:
SELECT tablename FROM pg_tables
WHERE schemaname = 'public'
Finally, if you go back to your original query, and you ignore the “metadata” button, and also ignore the “Evaluate” button, and so the query is as per your screenshot:
then click Apply and OK, what happens if you try executing the DB Query Reader node. Do you get an error, or does the query itself return results?
@takbb my friend
your queries returned empty tables witch gave me info to confront my IT
they gave me access to an empty database and configured a diferent one on heidi
its all set now,
metadata is working fine
again, thx so much!
i learned a lot with ur suggestions
Phew! Glad you got it sorted @YuriRibeiro , and thanks for marking the post as the solution.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.