I am trying to connect R to database (I just want to point out that i do not have any experience with databases, thats why i was hoping You could give me some hints). At first i tried ROracle package using code from this post:
library(ROracle)
host <- "xxx.xxx.xx.xxx"
port <- 1521
service <- "K" #? not sure exactly what does it mean service, is it name of server?i cannot really find any informations about it
drv <- dbDriver("Oracle")
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", service, ")))", sep = "")
con <- dbConnect(drv, username = "user", password = "pwd", dbname = connect.string)
I didnt get any error message, however when i used command
dbGetInfo(con)
I got following response:
$username
[1] "user"
$dbname
[1] "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xx.xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=K)))"
$serverVersion
[1] "xx.x.x.x.x"
$serverType
[1] "Oracle RDBMS"
$resTotal
[1] 0
$resOpen
[1] 0
$prefetch
[1] FALSE
$bulk_read
[1] 1000
$bulk_write
[1] 1000
$stmt_cache
[1] 0
$results
list()
while checking the documentation about ROracle i found out that:
$resTotal
The number of result sets on this connection
$resOpen
The number of result sets open on this connection
which both in my case are equal 0, does it mean i am connected or not?I just cannot figure out how i can check if i am connected to database/server.
Then i decided to check out package RODBC
:
library(RODBC)
ch <- odbcConnect("K",uid="user", pwd = "pwd") # again question what is the first parameter? should it be server name?
odbcGetInfo(ch)
It ended up worse with an Error:
Warnmeldungen:
1: In odbcDriverConnect("DSN=K;UID=user;PWD=pwd") :
[RODBC] FEHLER: Status IM002, Code 0, Nachricht [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=K;UID=user;PWD=pwd") :
ODBC-Verbindung fehlgeschlagen
>
> odbcGetInfo(ch)
Fehler in odbcGetInfo(ch) : Argument ist kein offener RODBC-Kanal
Can please anyone explain me how i can connect it to database and check the connection? I actually got very confused about this all procedure and I have tried to find solution by myself, even on stackoverflow, however i didnt understand it at all. Thanks in advance
try with this sample
require(Roracle)
Con=dbConnect(dbDriver("Oracle"),"IP/SID",username="xxx",password="xxx")
Q=dbSendQuery(Con,"Select ….")
F=fetch(Q)
head(F)