Shiny app trying to access SQLite db fails

[Bilahari Akkiraju]

Can access database from R command prompt

"select  Employment  from  OES_2002  where Occupation  like  'Computer programmer%'"
> rs=dbSendQuery(db,q)
> df=fetch(rs,n=-1)
> df
  Employment
1     457320

But below shiny app throws the error shown at bottom

server.R

output$summary <-renderPrint({ 
    #connect to the Database
    db=dbConnect(SQLite(),dbname="MyTest.sqlite");
    
  
    q=myQueryBuilder("OES_2002",input$jf,input$job);
    
    rs=dbSendQuery(db,q);
    df=fetch(rs,n=-1);
    
    #y=do.call(getYData,args=list(db,input$jf,input$job))
    print(df);
    
    dbDisconnect(db);
  })

RS-DBI driver: (error in statement: no such table: OES_2002)

[Joe Cheng]

When a Shiny app runs, the current directory is set to the application's root directory. Is that where your SQLite database file is located?

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Bilahari Akkiraju]

Thanks. I realized the RSQLite dbConnect was creating a new table in the working directory if the table did not already exist.

It works now.

--
You received this message because you are subscribed to a topic in the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/shiny-discuss/-8S9PKeXLNw/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to shiny-discus...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
My other computer is a data center
(408) 483 2780

[Xia Lu]

I got the same error message. I can run 

a<-sqldf("select * from joined limit 5",dbname="test.db")

without problem in the console but if I put the command in a server.R, it shows "RS-DBI driver: (error in statement: no such table: joined)". wonder what's wrong. 

[Xia Lu]

anyone tells me whether sqldf can be used on RStudio server? thanks a lot!!!

[Bilahari Akkiraju]

Make sure you are loading

library(RSQLite) 

I used RSQLite

--

You received this message because you are subscribed to a topic in the Google Groups "Shiny - Web Framework for R" group.

To unsubscribe from this topic, visit https://groups.google.com/d/topic/shiny-discuss/-8S9PKeXLNw/unsubscribe.

[Y David]

I'm having the same problem. Everything works fine in local machine but when I upload it to shiny server I get the problem.


The error:

Error in sqliteSendQuery(con, statement, bind.data) : 

error in statement: no such table: TblResult

My Code:
#OPEN LIBRARIES
library("shiny")  #SHINY LIBRARY
library("RSQLite")	#SQLITE LIBRARY
library("DBI")

#SET CONNECTION TO EXISTING SQLITE DATABASE 
sqlite <- dbDriver("SQLite")	#LOAD DRIVER
candb <- dbConnect(sqlite, dbname= "WaterQuality_STEP_V8.sqlite")	#LOAD DB

#IMPORT ALL DISTINCT CONSTITUENT NAMES FROM DATABASE
AllCanConst <- data.frame(dbGetQuery(candb, "SELECT DISTINCT TblResult.Constituent_ID FROM TblResult ORDER BY TblResult.Cons tituent_ID")) 

ListAllCanConst <- AllCanConst[,1]	#SELECTS THE FIRST COLUMN ONLY

#DISCONNECT WITH SQLITE DATABASE  
dbDisconnect(candb) 

Can anyone tell me what's wrong with the code?
It is possible to use SQLite database within the shiny web framework right?

Thanks!

[Y David]

I found my mistake. Shiny apps on your local machine is not case sensitive but the shinyapps.io web server is. The case of one the letters in my database file name was different in my code. This resulted in the app not looking through the database and thus giving "no such table" error.

[Pre Rec]

My database file is larger than 4Gb, so cannot keep the file in application directory. I need R shiny to access the file(db) on separate folder on my computer.