Import Data from SQL Server To shiny app

[carolyn]

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.

[andresrcs]

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)

[carolyn]

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

[andresrcs]

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.

[carolyn]

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

[andresrcs]

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:

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …