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
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.
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?
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.
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.
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.
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.
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:
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)
ok, we are getting there. What kind of error do you get? The same error about db_url?
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.
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
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)
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.