#title SQL Server 연동
[[TableOfContents]]

==== SQL Server 연동(RODBC) ====
{{{
> library("RODBC")
> conn <- odbcConnect("localhost") #ODBC에 등록된 System DSN명
> sqlQuery(conn, paste("SELECT TOP 5 AddressID, City ",
+ "FROM AdventureWorks.Person.Address"))
  AddressID                 City
1       532               Ottawa
2       497              Burnaby
3     29781            Dunkerque
4     24231 Verrieres Le Buisson
5     19637 Verrieres Le Buisson
> sqlQuery(conn, "create table rodbc_test(id int)")
character(0)
> sqlQuery(conn, "insert rodbc_test values(1)")
character(0)
> sqlQuery(conn, "select * from rodbc_test")
  id
1  1
> close(conn)
}}}


library("RODBC")
conn <- odbcConnect("26")
data <- sqlQuery(conn, "
select 
	accountkey
,	sum(saleamt) amt 
,	log(sum(saleamt)) log_amt 
from cji_eis.PubSales.Fact_Sales 
where datekey between 20100601 and 20100630
and gamekey = 20
group by
	accountkey
")
hist(data$log_amt)

==== SQL Server 연동(TSODBC) ====
{{{
> library("TSodbc")
요구된 패키지 tframePlus를 로드중입니다
요구된 패키지 tframe를 로드중입니다
요구된 패키지 TSdbi를 로드중입니다
요구된 패키지 DBI를 로드중입니다
요구된 패키지 RODBC를 로드중입니다
Warning message:
In namespaceImportFrom(self, asNamespace(ns)) :
  replacing previous import: show
> con <-odbcConnect(dsn = "localhost")
> options(TSconnection = con)
> dbListTables(con)

[1] "AWBuildVersion"                                "DatabaseLog"                                   "ErrorLog"                                     
  [4] "ETL_MetaData"                                  "meta_data"                                     "sysdiagrams"                                  
  [7] "temp"                                          "test00"                                        "TestResult"                                   
 [10] "Department"                                    "Employee"                                      "EmployeeAddress"                              
 [13] "EmployeeDepartmentHistory"                     "EmployeePayHistory"                            "JobCandidate"                                 
 [16] "Shift"                                         "Address"                                       "AddressType"                                  
 [19] "Contact"                                       "ContactType"                                   "CountryRegion"                                
 [22] "StateProvince"                                 "BillOfMaterials"                               "Culture"                                      
 [25] "Document"                                      "Illustration"                                  "Location"                                     
 [28] "Product"                                       "ProductCategory"                               "ProductCostHistory"                           
 [31] "ProductDescription"                            "ProductDocument"                               "ProductInventory"                             
 [34] "ProductListPriceHistory"                       "ProductModel"                                  "ProductModelIllustration"                     
 [37] "ProductModelProductDescriptionCulture"         "ProductPhoto"                                  "ProductProductPhoto"                          
 [40] "ProductReview"           
 
> try(dbGetQuery(con, paste("SELECT City, COUNT(*) Cnt FROM Person.Address  WHERE City LIKE 'A%' ", 
+ " GROUP BY City ORDER BY City")))
                City Cnt
1           Abingdon   1
2             Albany   4
3         Alexandria   2
4           Alhambra   1
5             Alpine   1
6           Altadena   2
7  Altamonte Springs   1
8          Anacortes   3
9          Arlington   1
10           Ascheim   1
11           Atlanta   2
12            Auburn   1
13          Augsburg   2
14           Augusta   1
15    Aujan Mournede   1
16            Aurora   1
17           Austell   1
18            Austin   2

> dbDisconnect(con)
[1] TRUE
> options(TSconnection = NULL)
> odbcCloseAll()
> dbUnloadDriver(m) --> 요건 잘 안되네..
}}}

{{{
## Choose the proper DBMS driver and connect to the server
drv <- dbDriver("ODBC")
con <- dbConnect(drv, "dsn", "usr", "pwd")
## The interface can work at a higher level importing tables
## as data.frames and exporting data.frames as DBMS tables.
dbListTables(con)
dbListFields(con, "quakes")
if(dbExistsTable(con, "new_results"))
dbRemoveTable(con, "new_results")
dbWriteTable(con, "new_results", new.output)
## The interface allows lower-level interface to the DBMS
res <- dbSendQuery(con, paste(
"SELECT g.id, g.mirror, g.diam, e.voltage",
"FROM geom_table as g, elec_measures as e",
"WHERE g.id = e.id and g.mirrortype = 'inside'",
"ORDER BY g.diam"))
out <- NULL
while(!dbHasCompleted(res)){
chunk <- fetch(res, n = 10000)
out <- c(out, doit(chunk))
}
## Free up resources
dbClearResult(res)
dbDisconnect(con)
dbUnloadDriver(drv)
}}}