After successfully publishing to Connect Cloud, my app fails when executing a dbGetQuery call to a SQLite db with the following error: "Error: no such table: cnty"
The app runs fine in RStudio Desktop and on the open source Shiny Server. A previous version was published to Connect Cloud and ran fine. The only significant difference with the new version is that it creates two DBConnect connections to two SQLite databases.
Is there a limit on connections? I'm stumped.
Hi @jbannon
Hmm, no, we won't limit number of SQLite databases or connections you can set up within your application.
How do the SQLite databases get into Connect Cloud? Are you publishing from a github repo or from the Publisher IDE extension?
The two SQLite databases (EEOTAB2018_sm.sqb and Nation24.sqb) were in the bundle published to Connect Cloud. I published from RStudio (Although I had previously published from Positron with same results). My app.R includes the following code:
con <- dbConnect(RSQLite::SQLite(), "EEOTAB2018_sm.sqb")
states <- dbGetQuery(con, "select distinct code, name from states")
Pcon <- dbConnect(RSQLite::SQLite(), "Nation24.sqb")
Pstates <- dbGetQuery(Pcon, "select distinct state_code, state_name from cnty")
'cnty' is a table in Nation24.sqb
Well that all sounds right to me. My best guess was that the sqlite database actually uploaded wasn't the one you expected.
Since the app runs elsewhere and interfacing with SQLite isn't an especially unusual thing to do I'll try to find time to investigate further - this could impact many over time. Can you share the URL of the app on Connect Cloud that is not working as expected?
The URL is https://019d9198-36a0-ef30-c66e-75500ec11cc8.share.connect.posit.cloud
I'd appreciate your looking into this. I doubt the issue is the DB file in question. The 'cnty' table is in DB in the project directory from which I published. Parenthetically, it would be helpful if Connect Cloud could give access to the names and dates of the files included in the 'bundle.'
New information: While waiting for guidance, I moved all the tables in the second DB file into a new DB with all the tables, and it published and ran successfully.
The new URL is https://019d979f-1bab-3f64-ab89-1f0855e70ca3.share.connect.posit.cloud
The original app is called 'eeostat census' and the new one-DB on is 'One_DB.'