Import Data from SQL Server To shiny app

shiny
sql
#1

hello,
i'm trying to build an application through r shiny. i'm having problems with converting sql syntax into syntax that is available in r.
Variables can be passed to SQL statements using paste. Below is how sql syntax looks:

SELECT BRKNo,SEX,BIRTHDAY,LAST_DATE,[ADDRESS],TEL_H_1,INVEST,C_Type,WRNT_REG_DATE,WRNT_CNCL_DATE,APL_FLAG,datediff(year,BIRTHDAY,getdate()) AS age
FROM dbo.SCUST
WHERE dbo.SCUST.CustNo not in (select custNo FROM dbo.WCKEY) AND [ADDRESS] not like '%testing%' AND [ADDRESS] not like '%XX%'

I've tried to convert a part and it does work. But i'm stuck when converting the WHERE part. How can I solve this?
Below is how it looks like in r currently:

library(shiny)
library(RODBC)

conn <- odbcDriverConnect("driver={SQL Server};server=;database=;uid=;pwd=")


shinyServer(function(input, output) {
  
  output$dataset1 <- renderTable ({
    
    conn <- odbcDriverConnect("driver={SQL Server};server=;database=;uid=;pwd=")    
    queried <- sqlQuery(channel = conn, query = "select 
       BRKNo
      ,SEX
      ,BIRTHDAY
      ,LAST_DATE
      ,[ADDRESS]
      ,TEL_H_1
      ,INVEST
      ,C_Type
      ,WRNT_REG_DATE
      ,WRNT_CNCL_DATE
      ,APL_FLAG
      ,datediff(year,BIRTHDAY,getdate()) AS age
      FROM dbo.SCUST")
    
    
    dataset1 = queried
    
  })
})

Any help is appreciated.

0 Likes

#2

Hi, Carolyn, welcome!

I don't understand what you mean by "converting", because as far as I can see you are just executing a sql query from R, and that's ok, so you can just paste your complete sql code as it is rigth now including the filtering part (WHERE).

conn <- odbcDriverConnect("driver={SQL Server};server=;database=;uid=;pwd=")
query <- "SELECT BRKNo,SEX,BIRTHDAY,LAST_DATE,[ADDRESS],TEL_H_1,INVEST,C_Type,WRNT_REG_DATE,WRNT_CNCL_DATE,APL_FLAG,datediff(year,BIRTHDAY,getdate()) AS age
FROM dbo.SCUST
WHERE dbo.SCUST.CustNo not in (select custNo FROM dbo.WCKEY) AND [ADDRESS] not like '%testing%' AND [ADDRESS] not like '%XX%'"
queried <- sqlQuery(channel = conn, query = query)
0 Likes

#3

Thank you for the solution! It's really helpful :grinning:
However, since the data includes chinese characters, when adding syntax like '%測試%' the result turned into error.Is there anyway to fix it?

0 Likes

#4

I don't know exactly how to do it with your example because I don't use RODBC package but you need to specify your encoding when sending the sql query, in my case with Spanish I use utf8 or latin1 depending on the operating system I'm working on.

0 Likes

#5

Got it,Thank you for Sharing!!
I just solved it by setting the encoding in r.

1 Like

#6

If your question's been answered (even by you!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

0 Likes

closed #7

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

0 Likes