Logging queries

[nmorti]

Is there anyway to log whole query when working with DB?
I enabled jdbc logger and set Log level in File ->Preferences ->Knime GUI to Debug but when I executed the DBInsert node then I got log:
INSERT INTO “public”.“users” (“id”, “username”) VALUES (?,?) ;
instead of “?” i want to log the actual values.

[LukasS]

Hi @nmorti ,

there are actually “two logs”: The log in the console, and the one you find under View → Open KNIME Log. The later is also the file in <your workspace>/.metadata/knime/knime.log. You set its log level under KNIME:

and it often contains more information than the console. Do you find the information you are looking for there?

Kind regards,
Lukas

[nmorti]

Thanks @LukasS .
I’ve tried that but still I can’t find what I want. both log configs don’t show query data.(e.g, values in insert query)

[nmorti]

DB SQL Executor node logs statements as they are written(info log). but this node is deprecated (knime-base source code). I want something like this for DB Insert, DB update and etc.

[LukasS]

Hi @nmorti,

indeed as I just learned from our developers is that we use Prepared Statements in the new nodes nodes to make them more efficient - this is what the question marks mean, they are placeholders for the actual values.

A few suggestions:

• You can use a DB SQL Executor Node and write a statement explicitly, e.g. injecting it via flow variables. This way, no prepared statements are used - less efficient execution and probably more complicated query building, but you get readable logs.
• You could have a look at your specific drivers documentation and find out whether it allows more detailed logging - maybe it writes out the values?
• Or we can take a step and look at the issue at large - what is your overall goal and what prevents you from reaching it? Is it logging for documentation purposes or do you want to investigate the logs for a specific purpose?

Kind regards,
Lukas

[nmorti]

Hi @LukasS ,
I want to investigate logs. I need to track everything happening on data. For instance I want to know which values inserted to database or which rows deleted or updated. (every query run on database)
DB SQL Executor can’t operate like DB Insert. Assume rows of data are generating and we want to insert them into database So I can’t figured out any right way to do that with DB SQL Executor. Also DB Insert, DB Update and similar nodes are user friendly and efficient as you said.

I’ve decided to create new node extension but I get into trouble:
https://forum.knime.com/t/problem-with-reusing-exsiting-packages-extensions-to-develop-new-node-extension/62765?u=nmorti

[LukasS]

Hi @nmorti ,

I see. I can’t help for writing your own extension, but please let me suggest a workflow that demonstrates how SQL statements can be generated dynamically and then executed with the DB SQL Executor:

Documenting DB Insert – lukass

On the top part, it takes the data-table, generates a SQL-Insert-statement for each row that is then aggregated to a huge string and finally executed as a flow variable by the SQL Executor Node in the bottom part. This way, no Prepared Statements are used, but explicit hand-crafted ones, that will fully show up in the log.

If you have users, you could think about putting this into configurable shared components to make it available for them… Maybe that has less overhead than building your own extension, but ofc I’m not gonna stop you

Hope that helps and happy KNIMEing/coding!
Lukas