Re: [R] RODBC: how to set the data-source?
Dumblauskas, Jerry credit-suisse.com> writes: [...] > The alloc error means you are close... [...] Ah, ok, I see... ...then you are right that Driver has to be the path to the shared library. :-) Oliver __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
Dumblauskas, Jerry credit-suisse.com> writes:
>
> OK
>
> Is your PostGres server on the same Linux box you are running R on?
> Sample values
> Server = 169.49.30.69 (localhost host means you are on the same
> box)
> Port= 2700 (make sure your port is correct)
>
> Also, I am not seeing your id and password
>
> I use the signature
> CONNREAD <- odbcConnect(READDBID, uid = READDBUSER, pwd = READDBPWD,
> believeNRows=FALSE)
>
> So for you this would be
> channel <- odbcConnect("rtestdb", uid="rtest", pwd="???",
> believeNRows=FALSE)
As I do all stuff locally I have not configures for necessity of login
username/passwd.
So i assume I can omit it. With psql I don't need one.
"???" means my password I assume...
Well, I have configured it, so I maybe need it?
With psql not, but I have set it. I think it will not be checked from the db.
Why did you make uid="rtest"?
rtest is the name of the database.
I use "oliver" as db-login-name.
>
> The alloc error means you are close...
>
> Make sure you can connect to your DB outside of R as well
With psql I can login.
And I need no passwd.
Maybe it's different for the R-connection, instead of starting psql from the
shell? But I don't think so.
Ciao,
Oliver
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
Dumblauskas, Jerry credit-suisse.com> writes:
>
> OK
>
> Is your PostGres server on the same Linux box you are running R on?
> Sample values
> Server = 169.49.30.69 (localhost host means you are on the same
> box)
yes, all is local here.
> Port= 2700 (make sure your port is correct)
to be honest, I'm not sure... I just used the port-number that was used in the
documentation (the example), assuming it is correct. ;-)
I did'nt checked the port-number...
>
> Also, I am not seeing your id and password
well is this necessary?
I just used the examples, and all that is different, I changed.
If the examples are missing the user/passwd, then I also did not used it.
BTW: here did you miss it? In /etc/odbc.ini?
>
> I use the signature
> CONNREAD <- odbcConnect(READDBID, uid = READDBUSER, pwd = READDBPWD,
> believeNRows=FALSE)
??
>
> So for you this would be
> channel <- odbcConnect("rtestdb", uid="rtest", pwd="???",
> believeNRows=FALSE)
???
Van you explain me the "???" and the READDBID / READDBUSER / READDBPWD ?!
Looks like env-vars included here in the configs?!
> The alloc error means you are close...
?? what?!
...you mean allocHandle?!
>
> Make sure you can connect to your DB outside of R as well
With psql I can connect.
Was this your question?
Or do you mean other tools?
Ciao,
Oliver
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
OK
Is your PostGres server on the same Linux box you are running R on?
Sample values
Server = 169.49.30.69 (localhost host means you are on the same
box)
Port= 2700 (make sure your port is correct)
Also, I am not seeing your id and password
I use the signature
CONNREAD <- odbcConnect(READDBID, uid = READDBUSER, pwd = READDBPWD,
believeNRows=FALSE)
So for you this would be
channel <- odbcConnect("rtestdb", uid="rtest", pwd="???",
believeNRows=FALSE)
The alloc error means you are close...
Make sure you can connect to your DB outside of R as well
hth
-Original Message-
From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org]
On Behalf Of Oliver Bandel
Sent: Friday, August 28, 2009 2:22 PM
To: r-h...@stat.math.ethz.ch
Subject: Re: [R] RODBC: how to set the data-source?
Hi,
in
/usr/share/doc/r-cran-rodbc/README.gz
I found this example:
==
[testpg]
Description = testpg
Driver = PostgreSQL
Trace = No
TraceFile =
ServerName = localhost
UserName= ripley
Port= 5432
Socket =
Database= testdb
ReadOnly= 0
==
I changed it for me to:
==
[rtest]
Description = rtest
Driver = PostgreSQL
Trace = No
TraceFile =
ServerName = localhost
UserName= oliver
Port= 5432
Socket =
Database= rtest
ReadOnly= 0
==
With your hint on the Driver-value:
==
[rtest]
Description = rtest
Driver = /usr/lib/odbc/libodbcpsqlS.so
Trace = No
TraceFile =
ServerName = localhost
UserName= oliver
Port= 5432
Socket =
Database= rtest
ReadOnly= 0
==
Maybe I should give different names for the section and the database?!
==
[rtestdb]
Description = rtest
Driver = /usr/lib/odbc/libodbcpsqlS.so
Trace = No
TraceFile =
ServerName = localhost
UserName= oliver
Port= 5432
Socket =
Database= rtest
ReadOnly= 0
==
x > channel <- odbcConnect("rtestdb", "rtest")
x Warning messages:
x 1: In odbcDriverConnect(st, ...) :
x [RODBC] ERROR: state IM004, code 0, message [unixODBC][Driver x
xManager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
x 2: In odbcDriverConnect(st, ...) : ODBC connection failed
So this is not working correctly.
Here my settings for the ENV-vars:
oli...@siouxsie:~$ echo $ODBCINI
/etc/odbc.ini
oli...@siouxsie:~$ echo $ODBCSYSINI
/etc/
oli...@siouxsie:~$ echo $LD_LIBRARY_PATH /usr/lib/odbc/libodbcpsqlS.so
oli...@siouxsie:~$
I have no clue what's the problem now.
Oliver
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
===
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
... I also tried this one: r...@siouxsie:~# odbcinst -q -d odbcinst: SQLGetPrivateProfileString failed with . r...@siouxsie:~# "failed with ." ... what a verbose and detailed error message :-( Oliver __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
Hi,
in
/usr/share/doc/r-cran-rodbc/README.gz
I found this example:
==
[testpg]
Description = testpg
Driver = PostgreSQL
Trace = No
TraceFile =
ServerName = localhost
UserName= ripley
Port= 5432
Socket =
Database= testdb
ReadOnly= 0
==
I changed it for me to:
==
[rtest]
Description = rtest
Driver = PostgreSQL
Trace = No
TraceFile =
ServerName = localhost
UserName= oliver
Port= 5432
Socket =
Database= rtest
ReadOnly= 0
==
With your hint on the Driver-value:
==
[rtest]
Description = rtest
Driver = /usr/lib/odbc/libodbcpsqlS.so
Trace = No
TraceFile =
ServerName = localhost
UserName= oliver
Port= 5432
Socket =
Database= rtest
ReadOnly= 0
==
Maybe I should give different names for the section and the database?!
==
[rtestdb]
Description = rtest
Driver = /usr/lib/odbc/libodbcpsqlS.so
Trace = No
TraceFile =
ServerName = localhost
UserName= oliver
Port= 5432
Socket =
Database= rtest
ReadOnly= 0
==
x > channel <- odbcConnect("rtestdb", "rtest")
x Warning messages:
x 1: In odbcDriverConnect(st, ...) :
x [RODBC] ERROR: state IM004, code 0, message [unixODBC][Driver x
xManager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
x 2: In odbcDriverConnect(st, ...) : ODBC connection failed
So this is not working correctly.
Here my settings for the ENV-vars:
oli...@siouxsie:~$ echo $ODBCINI
/etc/odbc.ini
oli...@siouxsie:~$ echo $ODBCSYSINI
/etc/
oli...@siouxsie:~$ echo $LD_LIBRARY_PATH
/usr/lib/odbc/libodbcpsqlS.so
oli...@siouxsie:~$
I have no clue what's the problem now.
Oliver
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
So now R can see your odbc ini file -- but at runtime you are still
having issues...
So it should either be:
1) you have a path not set (for the oracle example below I use
LD_LIBRARY_PATH -- not sure about PostGres)
2) your ODBC.ini file doesn't have a line like
Driver = /some_random_path/instantclient_11_1/libsqora.so.11.1
Send over your odbc.ini entry
-Original Message-
From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org]
On Behalf Of Oliver Bandel
Sent: Friday, August 28, 2009 1:22 PM
To: r-h...@stat.math.ethz.ch
Subject: Re: [R] RODBC: how to set the data-source?
Dumblauskas, Jerry credit-suisse.com> writes:
>
> Looks like Unix
...well, near by: Linux.
>
> If so, make sure your script has the proper paths set
>
> Here is my set up for an oracle DB
>
> export
> LD_LIBRARY_PATH=/../../instantclient_11_1:/../../unixODBC64_1/lib
> export ODBCINI=/../..//unixODBC64_1
> export ODBCSYSINI=/../..//unixODBC64_1
>
> And of course you need your db defined in ODBC.ini and this will link
> to your driver code...
OK, this was a good hint.
After looking around for that ODBC-configuration stuff I also found:
/usr/share/doc/r-cran-rodbc/README.gz
There is an example-config mentioned, which I changed to my needs.
Now I get a result from odbcDataSources() but can't connect to the db:
x> library(RODBC)
x> odbcDataSources()
x rtest
x"PostgreSQL"
x> channel <- odbcConnect("rtest")
xWarning messages:
x1: In odbcDriverConnect(st, ...) :
x [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver
xManager]Data source name not found, and no default driver specified
x2: In odbcDriverConnect(st, ...) : ODBC connection failed
x> channel <- odbcConnect("PostgreSQL", "rtest")
xWarning messages:
x1: In odbcDriverConnect(st, ...) :
x [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver
xManager]Data source name not found, and no default driver specified
x2: In odbcDriverConnect(st, ...) : ODBC connection failed
x>
Any further ideas?
Oliver
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
===
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
Dumblauskas, Jerry credit-suisse.com> writes:
>
> Looks like Unix
...well, near by: Linux.
>
> If so, make sure your script has the proper paths set
>
> Here is my set up for an oracle DB
>
> export LD_LIBRARY_PATH=/../../instantclient_11_1:/../../unixODBC64_1/lib
> export ODBCINI=/../..//unixODBC64_1
> export ODBCSYSINI=/../..//unixODBC64_1
>
> And of course you need your db defined in ODBC.ini and this will link to
> your driver code...
OK, this was a good hint.
After looking around for that ODBC-configuration stuff I also found:
/usr/share/doc/r-cran-rodbc/README.gz
There is an example-config mentioned, which I changed to my needs.
Now I get a result from odbcDataSources() but can't connect to the db:
x> library(RODBC)
x> odbcDataSources()
x rtest
x"PostgreSQL"
x> channel <- odbcConnect("rtest")
xWarning messages:
x1: In odbcDriverConnect(st, ...) :
x [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver
xManager]Data source name not found, and no default driver specified
x2: In odbcDriverConnect(st, ...) : ODBC connection failed
x> channel <- odbcConnect("PostgreSQL", "rtest")
xWarning messages:
x1: In odbcDriverConnect(st, ...) :
x [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver
xManager]Data source name not found, and no default driver specified
x2: In odbcDriverConnect(st, ...) : ODBC connection failed
x>
Any further ideas?
Oliver
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
Looks like Unix
If so, make sure your script has the proper paths set
Here is my set up for an oracle DB
export LD_LIBRARY_PATH=/../../instantclient_11_1:/../../unixODBC64_1/lib
export ODBCINI=/../..//unixODBC64_1
export ODBCSYSINI=/../..//unixODBC64_1
And of course you need your db defined in ODBC.ini and this will link to
your driver code...
hth
-Original Message-
From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org]
On Behalf Of Oliver Bandel
Sent: Friday, August 28, 2009 10:22 AM
To: r-h...@stat.math.ethz.ch
Subject: [R] RODBC: how to set the data-source?
Hello,
I want to try to connect R to PostgreSQL.
I have found that there are three ways:
DBI or RODBC or RPostgreSQL.
I tried around with DBI and RODBC but didn't get it running.
In both cases I'm not clear about how to set the driver/data source.
I get this error:
x > channel <- odbcConnect("rtest", uid="oliver") x Warning messages:
x 1: In odbcDriverConnect(st, ...) :
x[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver
Manager]Data
x source name not found, and no default driver specified x 2: In
odbcDriverConnect(st, ...) : ODBC connection failed x >
So I somehow have to set the Driver.
But how?
I didn't find it out.
Ciao,
Oliver
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
===
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC: how to set the data-source?
Oliver Bandel first.in-berlin.de> writes: > odbcDataSources() named character(0) > This looks like nothing is available via RODBC interface. Any idea what's missing? How can I get all names of available drivers? Ciao, Oliver __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
