DBI statement in progress error, in a Shiny Reactive Environment

I have a shiny application in RStudio on Amazon SageMaker. I connect the application to two databases (one with customer addresses and one with customer IDs) on Redshift.

In my ui, there is a conditional panel where the user either gets to search by customer ID textInput(inputId ="cid") or customer address textInput(inputId ="cad"). Depending on the choice selected, the user also gets an actionButton to submit the ID actionButton("go_cid") or the address actionButton("go_cad"), and a query is sent to the corresponding table.

My connections work, and look like this:

con_cad <- dbConnect(odbc::odbc(),
Driver='redshift', 
Server='***********.redshift.amazonaws.com', 
Port='5439', 
Database='*******', 
UID='****************', 
PWD= '**************')

con_cid <- dbConnect(odbc::odbc(),
Driver='redshift', 
Server='***********.redshift.amazonaws.com', 
Port='5439', 
Database='*******', 
UID='****************', 
PWD= '**************')

In the server I want to either query the addresses or the IDs table depending on the user selection. Either way, I save the query results in a corresponding dataframe which I utilize in later compuations.

Suppose the user chooses to search by address. I wish to create a reactive dataframe with addresses matching the input address. My code below achieves this:

df_cad <- eventReactive(input$go_cad,{
qry_cad <- dbSendQuery(con_cad, paste0("SELECT * FROM customer_ad WHERE address = '" , toupper(input$cad) , "'")) 
cad_result <- dbFetch(qry_cad , n =3) 
onStop(function(){dbClearResult(qry_cad) }) 
cad_result
})

When I search for an address for the first time in the application, everything works just fine. However, while the application is running, when I search for a second address (or the same address for the second time), I get the following error.

Error: nanodbc/nanodbc.cpp:1509: 00000: [RStudio][Amazon Redshift] (140) Error occurred while trying to run statement: a statement is already in progress

Please note, the application is structured such that the redshift connections are on top of my script followed by the ui and the server. I tried to put the connections inside the reactive environment but that did not work either.

I would like it if someone can help me get around this issue. Thanks

Solution

I was able to get around this issue by ussing pool package to manage connections.

pool_cad <- pool::dbPool(drv = odbc::odbc(), 
                         Driver='redshift',
                         Database= '******', 
                         Port='5439',
                         Server = '*******.redshift.amazonaws.com',
                         UID = '*******',
                         PWD = '*******')

df_cad <- eventReactive(input$go_cad,{
    
    qry_cad <- "SELECT * FROM FROM customer_ad WHERE address =?addr;"
    sql_cad_statement <- sqlInterpolate(pool_cad, qry_cad, addr = toupper(input$cad) )
    dbGetQuery(pool_cad, sql_cad_statement)
    })