DB Query Reader return the same data anytime

[lcanfora]

Hi everyone, I’m trying to access a DB access 2013 through the DB Query Reader, but even if I change the data on the DB, the query always returns the same data. I have reset both the connection node and the DB Query Reader node each time, but nothing changes. I have installed knime version 4.3.1.

Does anyone know why?

Thanks

[takbb]

Hi @lcanfora , welcome to the forum!

My immediate thoughts regarding this type of thing are that either you are not actually reading from the same table that you are updating (even though you think you are ), or else when you are changing the data, you are not committing the changes, so the changes are not yet visible to other connections.

So I’d double and triple-check those two things. If you have any other connection you can open outside of KNIME, I’d also check that it can see the changes, just to be sure.

Failing that, if you are able to post screenshots of your node configurations, maybe we can take a look at that for anything odd…

[lcanfora]

Thanks for the reply, but I have checked more than once. I discovered, however, that I see the data modified by closing and reopening knime. Even if I reset the jdbc connection node, nothing changes until I restart knime

[takbb]

@lcanfora

I see the data modified by closing and reopening knime

If you close and open the workflow (rather than all of KNIME), do you see the modification?

A second thought… if you place a second DB Query Reader on the same workflow, after modifying the data, and put the same query in it as the existing reader, what does it return?

A third thought… if you modify the query in the existing DB Query Reader after you have changed the data (e.g. add something like

and 1=1
to the where clause, so that it changes the query but doesn’t affect it, does it then return the newly modified data? I’m trying to understand if there’s maybe some statement/resultset caching going on…

Also try adding a second Connector and DB Query reader to the same workflow, after you’ve modified the data. Does that then see the new data?

Unfortunately I haven’t used KNIME with Access, and don’t have an Access database available, so I’m a bit in the dark on this one.

I assume you are using the Microsoft Access Connector node? Have you modified any of the default config on the Connector node, other than the database name/credentials?

[mlauber71]

Here is an example with an MS access file to toy around:

demonstrates the use of DB Update and DB Merge with MS Access DB – mlauber71

[izaychik63]

Could you please post your WF. What you are saying sounds like legacy DB nodes work.
I’d recommend to use separate DB connector for the DB Query Reader and activate it by
flow variable port after update.

[lcanfora]

@takbb

If you close and open the workflow (rather than all of KNIME), do you see the modification?

it returns the same data, while if I close and reopen everything knime, I see them modified

@takbb

A second thought… if you place a second DB Query Reader on the same workflow, after modifying the data, and put the same query in it as the existing reader, what does it return?

it returns the same data of first DB Query Reader

@takbb

A third thought… if you modify the query in the existing DB Query Reader after you have changed the data (e.g. add something like

and 1=1
to the where clause, so that it changes the query but doesn’t affect it, does it then return the newly modified data? I’m trying to understand if there’s maybe some statement/resultset caching going on…

Again notrhing has changed

@takbb

I assume you are using the Microsoft Access Connector node? Have you modified any of the default config on the Connector node, other than the database name/credentials?

yes, i use the Microsoft Access ConnectorDB Access.knwf (10.4 KB)

[lcanfora]

Another test: if I change the query by setting the user’s surname in the where clause, I only extract that row but with the old data

[takbb]

I’ve been puzzling over this today but still no suggestions. I found I had Access available on my Office 365 so I tried out a database on my pc, (as obviously I cannot see your database). It worked for me OK so that didn’t help!

Yours is on a network share I guess as it says U: drive in the config. Would you be able to copy the DB file onto a local drive on your pc and run similar tests. I’m wondering if it is anything to do with it being on a share… although it ought to be OK… Just trying to rule things out.

[lcanfora]

Today I also tried to use the database connection closer node, but again nothing has changed. I followed your advice to copy the DB locally and it actually works and the changes show in the node with no problems. The problem therefore seems to be that the DB on a network share is cached in some way.

[takbb]

Are you able to work with your database on a local drive, or does it need to be on a network share for others to use and also maybe so it’s backed up ?

It doesn’t surprise me that being on a share might cause issues, although I’ve not been able to replicate that myself. Access isn’t a client-server database so it has to operate by pulling the data from the file across the network, rather than having a callable service on a centralized server to do the job for you. I wonder if it’s to do with the size of the database file itself, or perhaps file-change notifications aren’t working as well from the share as they do from a local drive. It could as you say be down to some form of caching.

At least there is a “smoking gun” now that can potentially be investigated now and hopefully somebody with lower-level knowledge of the workings of the Microsoft Access Connector node may have better ideas on what is causing this, or if there is a workaround.

[lcanfora]

Let’s hope so, also because the query is the first step, but then I also have to update the DB, so it is necessary that it has the guarantees to modify it as I expect . thanks for the time you have dedicated to me

[izaychik63]

Today I’ve got the same issue. KNIME v 4.2.5 is on Win 10 desktop. My Access DB is located on the file server and split into Application and data files. Using DB Merge node I directly updated data part of Access DB. After that, I opened the Application and tried to read new data. The data were not where. Only after closing KNIME I was able to read data in Application.

[takbb]

Do you have any replication on the network share? Just wondering because of this…

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_win10-mso_2016/access-2016-db-on-shared-network-drive-but-data/5f1be909-ac07-454f-9382-e7dc26cbbd84

The other thing I should ask… Do you have any other client software on your pc that can query the database? If so, does that see the updates even though knime doesn’t see them?

(of course neither of those would necessarily explain why it becomes available on restarting knime)

[bruno29a]

Hi @izaychik63

@izaychik63

Only after closing KNIME I was able to read data in Application.

Can you do an explicit db close connection in your workflow after the operation is done?

You can use this:

DB Connection Closer

I think it’s waiting for the end of the script, which is usually detected when the connection is closed - most scripts in various languages usually close the db connection at the end of the script.

In Knime, there’s not really a concept of end of script. You can keep adding nodes on the fly, even after the whole workflow executed without having to re-execute the whole workflow again, and the connection is most probably closed when you are closing KNIME.

Try to explicitly close the db connection in the workflow, and see if the data gets written.

[izaychik63]

I believe there is another connected issue with Access DB. If I connected to it by Microsoft Access Connector, DB cannot be compacted/repaired in Access until KNIME is closed. DB Connection Closer does not help.