KNIME SQLite Connector - Web Portal

Hi,
I’m building a workflow which would take a query as input from the user and execute it against a database. In the background I have a database.db file which contains the tables and necessary data. The user’s query would be executed against database.db file. I’m using “SQLite Connector” node to make the connection to the .db file and “DB Query Reader” node to execute the query.

The workflow works in the Analytics Platform but fails with an error “No such table found” as it’s not able to make connection to the .db file I presume. I can’t enter a relative path “knime://knime.workflow/…/data/database.db” in the SQLite Connector node and the local path won’t allow it to access the .db file.

Can you please how to operate with the SQLite Connector node, when using it via Web Portal and giving it a relative path to .db file

Thank you.

Regards,
Vipul Phulphagar

HI @vipul,

could you please comment where is the db file located wrt the workflow both locally and on the server?

In general, if the relative path to the file wrt the workflow or the workflow repository is fixed, than you cab define it as a string flow variable using the knime:// protocol. You can then convert the knime:// protocol into the absolute path on the file system using
https://kni.me/n/U8Z6rygY21_VPiz7

The converrted path will be different locally and on the server and correspond to where the reference point in the used knime:// resides on the system.

Then you can use this absolute path to configure the database reading.

Let me know if you need further info.

Best regards,
Mischa

2 Likes

Hi @lisovyi,
Thank you for your reply.

Yes, the location of the .db file is fixed for both local and server execution. The relative path in both cases is: knime://knime.workflow/…/data/database.db although like you mentioned the absolute path will vary.

So if I understood correctly, I need to have a string flow variable containing the relative path “knime://knime.workflow/…/data/database.db”, pass it to the ‘URL to File Path’ node to convert it into a absolute file path and then pass it as a path variable to the ‘SQLite Connector’ node?

Regards,
Vipul

1 Like

Yes, that’s correct.

You cannot use the SQLite (or H2) Connector on a KNIME Server if the database is outside the workflow. Random access to the database file is required but the KNIME Server cannot provide this. Only sequential access to files on the server is possible.

2 Likes

Hi @thor,
Interesting point. Thank you.

Also @lisovyi, the “URI to File Path” node doesn’t seem to work on the server.
I found this:

1 Like

Thanks @thor for the insight
Thanks @vipul for sharing. You are right. It seems the trick is that the location of the job during execution is detached from the server workflow repository. So if one tries to find a file outside of the workflow there are problems, but it seems one can still use URI to File Path to resolve path to a file inside the workflow.

Hi vipul,
as long as the database is within the workflow folder you can use the knime protocol e.g. knime://knime.workflow/db.sqlite to access it. However this won’t work if the file is located outside of the workflow as mentioned above.
Bye
Tobias

Hi @tobias.koetter,
Thank you for your reply.

However, even when the .db file is in the workflow the SQLite Connector is not able to resolve the relative path – knime://knime-server/flow/database.db

When executing on the server, like I mentioned before, URL to File Path (variable) node doesn’t work on the server. Is there any workaround for this?

Basically, I want to pass relative path of the .db file which is inside the workflow to the SQLite Connector node.

Regards,
Vipul

Hi @vipul,

indeed, on the server knime://knime-server/flow/database.db will not be correctly resolved. But if your .db file lives inside the workflow from which you access it, then you can use knime://knime.workflow/file.db instead. This works both locally and on the server.

Here is a minimal example even without URL to File Path (variable) (which would also work but is not required in this case):
https://kni.me/w/j62q8X4wplbj3l5u

This worked locally in AP as well as on a server.

Cheers,
Mischa

1 Like

Hi @lisovyi,
I tried with the workflow that you shared and it worked on the AP. I had to change the “String configuration” node to “String Input” for executing on the server. But I was getting below error when I tried executing on the server:
KNIME-sqlite-error

KNIME Server - v4.8.2
KNIME AP - v4.0.1

1 Like

Hi @vipul,

the issue is that you use locally AP 4.0, while your server runs AP 3.7 as the executor (4.8 Server uses 3.7 as executor).

In general one should not expect forward compatibility, i.e. in this particular case it is not guaranteed that a workflow created with 4.0 will work in 3.7. However, backward compatibility is provided, i.e. it is possible to run in 4.0 an “old” workflow created with 3.7. It is strongly recommended to use the same version of AP locally and as executor on the server, i.e. AP 3.7 with Server 4.8 or AP 4.0 with Server 4.9.

Here is a 3.7 version of the workflow:
https://kni.me/w/jycN8cF3mk0748LY
As the backward compatibility suggests, you can open this workflow in 3.7 (your server) and in 4.0 (your AP)

Cheers,
Mischa

Hi @lisovyi,
Thank you. I guess I should better off move to 3.7 to avoid such issues.

Although, I’m still facing the same issue with the workflow that you’ve shared. My sample workflow:
https://hub.knime.com/vipul/space/11962_rel372-1

I presume it’s again because of the incompatibility?

Regards,
Vipul Phulphagar

Hi,

ok, we are getting there. What kind of error do you get? The same error about db_url?

  1. If the error is the same as before, then the problem is still the same- you have saved this workflow with 4.0 version and then you try to run it on 3.7. The SQLite Connector node in this 4.0-saved workflow doesn’t have db_url parameter, that is expected by the corresponding SQLite Connector (Labs) node in 3.7. As long as you resave my rel372 example from 4.0 these changes occurs and the workflow is not digestible to 3.7 anymore. You have to modify and save the original rel372 example from 3.7 only if you intend to run it on 3.7 executor.

  2. If the error is something different and new- could you please share the error?

Is there a chance to upgrade the server to 4.9 (and 4.0 executor therefore)? There was a 4.9.1 version released last week. This will allow to use AP 4.0 both locally and on the server and benefit from extensive integration with Hub in 4.0 :slight_smile:

Side note, your shared workflow contains a database file with potentially personal data. I would strongly encourage you to remove it from the example of the hub and replace with some small dummy database file (like the one that i had)

Cheers,
Mischa

1 Like

Hi @vipul,

did that resolve your problem?

Best regards,
Mischa

Hi @lisovyi,
Thank you for your reply.

I updated the Server to 4.9 and executor now is 4.0. I also designed the whole workflow in 4.0 but still I’m facing the same issue (I’m still getting the same error).

Also, the database file is just a dummy sample data file. I’ll share the workflow tomorrow so that it will be easier for you to debug.

Regards,
Vipul

Just one last thing to double-check: could you please check that the last release digit also matches between the executor and the local version that you use to build a workflow. I.e. make sure that you do not use 4.0.0 for one and 4.0.1 for another.

Hi @lisovyi,
I finally got it sorted. As you rightly pointed out, the problem was due to difference in versions.

Thank you so much for your help.

Regards,
Vipul

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.