2

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

Community
  • 1
  • 1
Mal_a
  • 3,670
  • 1
  • 27
  • 60
  • with RODBC you have to also put the jar file (drivers) in a particular directory. Have you done that? – CuriousBeing Feb 09 '16 at 10:24
  • i haven't done that, as i wrote, unfortunatelly my knowledge about databases at this moment is poor. Jar file with drivers? Could You explain to me bit more? and about ROracle, have I done it right?Is the conenction done? – Mal_a Feb 09 '16 at 10:33
  • Firstly, do you have Oracle installed on your computer? Is your username user and password pwd? – CuriousBeing Feb 09 '16 at 10:53
  • Well I do not have Oracle installed on my computer, I am connected to server, which has connection to Oracle. Well for the username and password, thats an example – Mal_a Feb 09 '16 at 10:55
  • `ch <- odbcConnect("K",uid="user", pwd = "pwd")`. Here "K" is your database name. Try this first using package `RODBC` – CuriousBeing Feb 09 '16 at 10:56
  • Hey MaxPD, it didn't work ...:( same error! – Mal_a Feb 09 '16 at 11:20
  • Do you have a DB client that you can use to connect to the DB? – CuriousBeing Feb 09 '16 at 11:22
  • Yes I do (http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html) – Mal_a Feb 09 '16 at 11:48

2 Answers2

2

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)
Hossein Vatani
  • 1,381
  • 14
  • 26
  • Does "Select ..." means name of the column? – Mal_a Feb 10 '16 at 06:23
  • It seems that connection works, but dbSendQuery(--) is not working. I have used code `rs <- dbSendQuery(con, "Select name of the column.")` and i got an error `Fehler in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, : ORA-01747: invalid user.table.column, table.column, or column specification`. – Mal_a Feb 10 '16 at 06:57
  • 1
    (Con,"Select ….") means `(Con,"Select * From Schema.Table")` or like this, _any SQL_ – Hossein Vatani Feb 10 '16 at 07:07
  • I have just tried it and got the same error `Fehler in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, : ORA-00942: table or view does not exist`. And i am sure the table exists – Mal_a Feb 10 '16 at 07:19
  • first: try your query in Oracle with other schema, this message maybe means you forgot _schame_ name for some table or function.if it works, send R result complet – Hossein Vatani Feb 10 '16 at 07:24
  • well i have tried `tabellen <- dbListTables(con)`, and it gave me an empty list, so it means something is wrong with connection – Mal_a Feb 10 '16 at 07:46
1

try with RJdbc

Note: install RJdbc and rjava packages.

library(RJDBC)

driver <- JDBC("oracle.jdbc.OracleDriver",
            classPath="C:/app/../product/12.1.0/client_1/jdbc/lib/ojdbc7.jar"," ")

connection <-dbConnect(driver,"jdbc:oracle:thin:@host:port:SID","USERNAME","PASSWORD")

results <- dbGetQuery(connection, "SELECT COUNT(*) FROM TABLENAME")

print(results)
dbDisconnect(connection)
Virb
  • 1,639
  • 1
  • 16
  • 25
Raviteja Gujjula
  • 11
  • 1