I’ve been trying to connect my company’s DMS to R using the odbcConnect command, but get the following message:
myConn <-odbcConnect("NZSQL", uid="cejacobson", pwd="password")
Warning messages:
1: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
ODBC connection failed
The thing is, I’m positive the Data source name is NZSQL and my uid and password are correct as well. Any insight as to why R may not be finding my data source / driver (the driver is, by the way, specified and working).
How can I fix this?
cigien
56.8k11 gold badges69 silver badges107 bronze badges
asked Jun 12, 2013 at 15:20
5
I ran across this same problem when I was first trying to connect to an Oracle database. In the end what worked for me was using odbcDriverConnect and a connection string instead of odbcConnect.
myConn <-odbcDriverConnect("Driver={Oracle in OraClient11g_home1};Dbq=NZSQL;Uid=cejacobson;Pwd=password;")
You can check on https://www.connectionstrings.com/ for your specific connection string for your database. Mine happened to be this one.
Hope this helps.
answered Mar 9, 2015 at 12:08
PhilPhil
1537 silver badges14 bronze badges
This is IM02 error which means name of the DSN is incorrect.
GO to ODBC and check the USER/System DSN that you should be using. Once your name of DSN is correct, you might get IM014 state error which is archtecture mismatch. In that case,
The simpler solution is
IN r studio — go to tools and change the version of R to 32 bit.
It should be ready to work
answered Dec 5, 2017 at 16:29
2
I was trying to access SQL Server database and got the same error. After using the correct format of db connection, I got access to my sql server database.
dbhandle <- odbcDriverConnect("Driver={SQL Server};Server=mydbhost;Database=mydbname;Trusted_Connection=Yes")
sschale
5,1383 gold badges29 silver badges36 bronze badges
answered May 26, 2016 at 21:10
ChadChad
433 bronze badges
I know this is old but also make sure that you remove the spaces around the ‘=’ sign. That was my problem.
answered Jan 17, 2017 at 20:58
DanrexDanrex
1,6574 gold badges31 silver badges44 bronze badges
What worked for me was a 32 bit connection instead of a 64 bit connection.
answered Apr 19, 2016 at 9:00
I just spent days on this. If you are using a Microsoft Access database you have to use the full path to the database, it won’t even find the file in the same folder.
So from the above question,
myConn <-odbcConnect("NZSQL", uid="cejacobson", pwd="password")
Would need to be something like
myConn <-odbcConnect("c:\NZSQL", uid="cejacobson", pwd="password")
Phil’s link really helped:
https://www.connectionstrings.com/
answered Mar 18, 2020 at 23:55
I had the same problem, what helped me was to add the driver manually:
- Search for ODBC Data Source Administrator and click the 64 bit option
- Click on «dBASE Files» and then «Add»
- Double click on «Oracle in XE»
- Fill Data Source Name (NZSQL in your example) and TNS Service Name fields.
- Click on Ok.
After that you may go to RStudio again and you will be able to connect using odbcConnect().
answered Jun 7, 2021 at 23:04
I just used R x64 (64-bit) instead of R i386 (32-bit) and it worked
answered Aug 10, 2022 at 17:30
I’ve been trying to connect my company’s DMS to R using the odbcConnect command, but get the following message:
myConn <-odbcConnect("NZSQL", uid="cejacobson", pwd="password")
Warning messages:
1: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
ODBC connection failed
The thing is, I’m positive the Data source name is NZSQL and my uid and password are correct as well. Any insight as to why R may not be finding my data source / driver (the driver is, by the way, specified and working).
How can I fix this?
cigien
56.8k11 gold badges69 silver badges107 bronze badges
asked Jun 12, 2013 at 15:20
5
I ran across this same problem when I was first trying to connect to an Oracle database. In the end what worked for me was using odbcDriverConnect and a connection string instead of odbcConnect.
myConn <-odbcDriverConnect("Driver={Oracle in OraClient11g_home1};Dbq=NZSQL;Uid=cejacobson;Pwd=password;")
You can check on https://www.connectionstrings.com/ for your specific connection string for your database. Mine happened to be this one.
Hope this helps.
answered Mar 9, 2015 at 12:08
PhilPhil
1537 silver badges14 bronze badges
This is IM02 error which means name of the DSN is incorrect.
GO to ODBC and check the USER/System DSN that you should be using. Once your name of DSN is correct, you might get IM014 state error which is archtecture mismatch. In that case,
The simpler solution is
IN r studio — go to tools and change the version of R to 32 bit.
It should be ready to work
answered Dec 5, 2017 at 16:29
2
I was trying to access SQL Server database and got the same error. After using the correct format of db connection, I got access to my sql server database.
dbhandle <- odbcDriverConnect("Driver={SQL Server};Server=mydbhost;Database=mydbname;Trusted_Connection=Yes")
sschale
5,1383 gold badges29 silver badges36 bronze badges
answered May 26, 2016 at 21:10
ChadChad
433 bronze badges
I know this is old but also make sure that you remove the spaces around the ‘=’ sign. That was my problem.
answered Jan 17, 2017 at 20:58
DanrexDanrex
1,6574 gold badges31 silver badges44 bronze badges
What worked for me was a 32 bit connection instead of a 64 bit connection.
answered Apr 19, 2016 at 9:00
I just spent days on this. If you are using a Microsoft Access database you have to use the full path to the database, it won’t even find the file in the same folder.
So from the above question,
myConn <-odbcConnect("NZSQL", uid="cejacobson", pwd="password")
Would need to be something like
myConn <-odbcConnect("c:\NZSQL", uid="cejacobson", pwd="password")
Phil’s link really helped:
https://www.connectionstrings.com/
answered Mar 18, 2020 at 23:55
I had the same problem, what helped me was to add the driver manually:
- Search for ODBC Data Source Administrator and click the 64 bit option
- Click on «dBASE Files» and then «Add»
- Double click on «Oracle in XE»
- Fill Data Source Name (NZSQL in your example) and TNS Service Name fields.
- Click on Ok.
After that you may go to RStudio again and you will be able to connect using odbcConnect().
answered Jun 7, 2021 at 23:04
I just used R x64 (64-bit) instead of R i386 (32-bit) and it worked
answered Aug 10, 2022 at 17:30
Let’s start with saying that importing MS Access files into R can be easy, but it isn’t necessarily so. There’s some compatibility issues that you need to know about. This blog post elaborates on these errors and how I solved them.
Import tables from an .mdb file
Opening a connection to your .mdb access database file and loading in a complete table is fairly easy. A widely used library to perform this task is RODBC, which enables Open Database Connectivity (ODBC) in R.
library(RODBC)
con <- odbcConnectAccess('your_db_file.mdb')
sqlFetch(con, 'your_table')
odbcClose(con)If you have a 32-bit version of MS Office installed on your computer and you use the 32-bit version of R and your Access file has the .mdb extension, this will probably have worked flawlessly. However, that’s quite a lot of requirements.
If you use 64-bit R, you will run into the following error:
Error in odbcConnectAccess(“your_file.mdb”) : odbcConnectAccess is only usable with 32-bit Windows
If your tables aren’t huge (+4GB), you can simply change R to the 32-bit version. In RStudio, this is done via Tools > Global Options. You will have to restart RStudio.
Import tables from an .accdb file
To import .accdb files, the same requirement holds: you need to be working in the 32-bit version of R. The function to create the connection is very similar as with .mdb files.
library(RODBC)
con <- odbcConnectAccess2007('your_db_file.accdb')
sqlFetch(con, 'your_table')
odbcClose(con)However, it is very likely that you’ll run into this error:
1: In odbcDriverConnect(con, …) : [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified2: In odbcDriverConnect(con, …) : ODBC connection failed
That’s because you need the 32-bit MS Access 2007 drivers. Luckily, you don’t need to install another version of MS Office. The drivers have been made available in a stand-alone installer, that you can get here. If you install this file, you’ll be able to access .accdb files in R. You don’t even need to restart after installing these drivers.
By the way, if you’re having trouble understanding some of the code and concepts, I can highly recommend “An Introduction to Statistical Learning: with Applications in R”, which is the must-have data science bible. If you simply need an introduction into R, and less into the Data Science part, I can absolutely recommend this book by Richard Cotton. Hope it helps!
Great success!
Say thanks, ask questions or give feedback
Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.
Connection is failing in R v 3.4.3 (32 bit) Windows 10
`
require(readdst)
project <- system.file(«Stratify», package=»readdst»)
project
[1] «C:/XXXXX/Documents/R/win-library/3.4/readdst/Stratify»
project <- paste0(project,»/Vignette-stratify»)
project
[1] «C:/XXXX/Documents/R/win-library/3.4/readdst/Stratify/Vignette-stratify»
converted <- convert_project(project)
Error in db_get(project_file, «ProjectSettings») :
RODBC is unable to open C:/XXXXX/Documents/R/win-library/3.4/readdst/Stratify/Vignette-stratify.dst
table= ProjectSettings
In addition: Warning messages:
1: In odbcDriverConnect(dsn) :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect(dsn) : ODBC connection failed`
Grant
Here are my findings from running your 5 lines of code on my home Windows 10 machine:
Restarting R session...
> require(readdst)
Loading required package: readdst
> project <- system.file("Stratify", package="readdst")
> project <- paste0(project,"/Vignette-stratify")
> project
[1] "C:/Users/erexstad/Documents/R/win-library/3.4/readdst/Stratify/Vignette-stratify"
> converted <- convert_project(project)
Loading required package: RODBC
> sessionInfo()
R version 3.4.3 (2017-11-30)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows >= 8 x64 (build 9200)
Matrix products: default
locale:
[1] LC_COLLATE=English_United Kingdom.1252 LC_CTYPE=English_United Kingdom.1252 LC_MONETARY=English_United Kingdom.1252
[4] LC_NUMERIC=C LC_TIME=English_United Kingdom.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RODBC_1.3-15 readdst_0.0.4
loaded via a namespace (and not attached):
[1] RColorBrewer_1.1-2 checkmate_1.8.5 cluster_2.0.7-1 rstudioapi_0.7 magrittr_1.5 acepack_1.4.1 gtable_0.2.0
[8] minqa_1.2.4 data.table_1.10.4-3 base64enc_0.1-3 pillar_1.2.1 htmltools_0.3.6 stringr_1.3.0 truncnorm_1.0-8
[15] splines_3.4.3 Formula_1.2-2 lattice_0.20-35 survival_2.41-3 Rvmmin_2017-7.18 setRNG_2013.9-1 htmlwidgets_1.0
[22] testthat_2.0.0 plyr_1.8.4 knitr_1.20 gridExtra_2.3 Matrix_1.2-12 R6_2.2.2 optimx_2013.8.7
[29] numDeriv_2016.8-1 digest_0.6.15 colorspace_1.3-2 Rsolnp_1.16 stringi_1.1.7 yaml_2.1.18 lazyeval_0.2.1
[36] Hmisc_4.1-1 dfoptim_2018.2-1 tibble_1.4.2 mgcv_1.8-23 compiler_3.4.3 Rcgmin_2013-2.21 rpart_4.1-11
[43] backports_1.1.2 htmlTable_1.11.2 munsell_0.4.3 Rcpp_0.12.16 optextras_2016-8.8 BB_2014.10-1 svUnit_0.7-12
[50] parallel_3.4.3 ggplot2_2.2.1 ucminf_1.1-4 mrds_2.2.0 latticeExtra_0.6-28 tools_3.4.3 foreign_0.8-69
[57] nnet_7.3-12 scales_0.5.0 quadprog_1.5-5 rlang_0.2.0 nlme_3.1-137 grid_3.4.3
can you check your version numbers of readdst and RODBC? As I recall there are not 64-bit drivers for RODBC, you you say you are running the 32-bit version of R 3.4.3…
Heya,
Seems that my versions are the same as yours there
`
require(readdst)
project <- system.file(«Stratify», package=»readdst»)
project <- paste0(project,»/Vignette-stratify»)
converted <- convert_project(project)
Error in db_get(project_file, «ProjectSettings») :
RODBC is unable to open C:/xxxxxxx/R/win-library/3.4/readdst/Stratify/Vignette-stratify.dst
table= ProjectSettings
In addition: Warning messages:
1: In odbcDriverConnect(dsn) :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect(dsn) : ODBC connection failed
sessionInfo()
R version 3.4.3 (2017-11-30)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows >= 8 x64 (build 9200)
Matrix products: default
locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C LC_TIME=English_United States.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] RODBC_1.3-15 readdst_0.0.4
loaded via a namespace (and not attached):
[1] RColorBrewer_1.1-2 checkmate_1.8.5 cluster_2.0.6 rstudioapi_0.7 magrittr_1.5 acepack_1.4.1
[7] gtable_0.2.0 minqa_1.2.4 data.table_1.10.4-3 base64enc_0.1-3 pillar_1.1.0 htmltools_0.3.6
[13] stringr_1.3.0 truncnorm_1.0-8 splines_3.4.3 Formula_1.2-2 lattice_0.20-35 survival_2.41-3
[19] Rvmmin_2017-7.18 setRNG_2013.9-1 htmlwidgets_1.0 testthat_2.0.0 plyr_1.8.4 knitr_1.19
[25] gridExtra_2.3 Matrix_1.2-12 R6_2.2.2 optimx_2013.8.7 numDeriv_2016.8-1 digest_0.6.15
[31] colorspace_1.3-2 Rsolnp_1.16 stringi_1.1.6 yaml_2.1.16 lazyeval_0.2.1 Hmisc_4.1-1
[37] dfoptim_2017.12-1 tibble_1.4.2 mgcv_1.8-22 compiler_3.4.3 Rcgmin_2013-2.21 rpart_4.1-11
[43] backports_1.1.2 htmlTable_1.11.2 munsell_0.4.3 Rcpp_0.12.15 optextras_2016-8.8 BB_2014.10-1
[49] svUnit_0.7-12 parallel_3.4.3 ggplot2_2.2.1 ucminf_1.1-4 mrds_2.1.18 latticeExtra_0.6-28
[55] tools_3.4.3 foreign_0.8-69 nnet_7.3-12 scales_0.5.0 quadprog_1.5-5 rlang_0.1.6
[61] nlme_3.1-131 grid_3.4.3 `
Have a look in your C:windowssystem32 directory for a file odbcad32.exe per
https://knowledgebase.progress.com/articles/Article/3352
Be sure to use the correct ODBC Administrator tool. On 32-bit and 64-bit Windows, the default ODBC Administrator tool is in c:WindowsSystem32odbcad32.exe. However, on a 64-bit Windows machine, the default is the 64-bit version. If the 32-bit ODBC Administrator tool is needed on a 64-bit Windows system, run the one found here: C:WindowsSysWOW64odbcad32.exe
I tried to rename my copy of that file to see if I could break readdst, but Windows didn’t want me mucking with that file.
Looked at it, but didn’t make a difference which tool I used.
Afraid I’m at a dead end. My present conclusion is that a) the problem lies outside readdst, b) perhaps within RODBC, or c) even outside R (in the ODBC configuration on your system).
Testing to refute a) would be to see if you can run the examples that ship with the RODBC package.
Grant, these connections are setup by readdst when project file is supplied to convert_project, so I doubt this is the issue. Please do try out @erex’s suggestions to see if the issue is with your particular ODBC setup.
RODBC installation requires setting the DSN up ahead of time, which readdst supposedly does itself (if I’m understanding what you’re saying about convert_project), so it’s not clear where the breakdown is. My RODBC connection works fine… yet…
require(RODBC)
channel <- odbcConnect("Golftees")`
> channel
RODBC Connection 3
Details:
case=nochange
DSN=Golftees
DBQ=C:UsershumphriesgDocumentsRwin-library3.4readdstGolftees-exampleGolftees.datDistData.mdb
DriverId=281
FIL=MS Access
MaxBufferSize=2048
PageTimeout=5
> library(readdst)
> project <- system.file("Golftees-example", package="readdst")
> project <- paste0(project,"/Golftees")
> project
[1] "C:/Users/humphriesg/Documents/R/win-library/3.4/readdst/Golftees-example/Golftees"
> converted <- convert_project(project)
Error in db_get(project_file, "ProjectSettings") :
RODBC is unable to open C:/Users/humphriesg/Documents/R/win-library/3.4/readdst/Golftees-example/Golftees.dst
table= ProjectSettings
In addition: Warning messages:
1: In odbcDriverConnect(dsn) :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect(dsn) : ODBC connection failed
>
Convert_project is making RODBC look for Golftees.dst — but Golftees.dst is not something RODBC can connect to.
I’ve played with the db_get function and doing this:
dsn <- paste0("Driver={Microsoft Access Driver (*.mdb)};Dbq=",
#"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",
file, ";Uid=;Pwd=;")
made a difference in that it seems to connect (there is no {Microsoft Access Driver (*.mdb, *.accdb)} in the ODBC driver list in a clean Windows 10 install).
X <- 'C:/Users/humphriesg/Documents/R/win-library/3.4/readdst/Golftees-example/Golftees.dst'
> db_get(X,'ProjectSettings')
Section Key Setting
1 CDSProperties CurrentDFTab Models
2 DSMProperties CurrentDSTab Factors
3 General DistanceVersion 6.0
4 Geo DataPrj None
5 Geo DataPrjParams
6 Geo DataUnits Meter
7 Geo GeoCoordSys None
8 Geo MapPrj None
9 Geo MapPrjParams
10 Geo MapUnits Meter
11 MRDSProperties CurrentDFTab Diagnostics
However, this then gives the error:
> converted <- convert_project(project)
Error in eval(e, x, parent.frame()) : object 'Key' not found
If the problem were as you suggest then I this code would not run on my or @erex’s machine, so I doubt that this is the case.
Have you tried removing C:/Users/humphriesg/Documents/R/win-library/3.4/readdst/ and installing readdst from scratch using devtools::install_github("DistanceDevelopment/readdst")?
Sorry — I was editing that comment when you responded — but see the above message for where I’ve got to so far. I have tried removing and re-installing a few times now.
Okay — I’ve finally made headway. First, I had to change the code as I mentioned above
dsn <- paste0("Driver={Microsoft Access Driver (*.mdb)};Dbq=",
#"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",
file, ";Uid=;Pwd=;")
And then I had to clone the package and source the R directory into my project. For some reason, readdst was not installing all of the functions. I.E.
And several other functions were not able to be found after installing with install_github
Aha! Okay, thanks for investigating this! We’ll try this out locally but it looks like should be a trivial change that is helpful.
Thanks for your input!
Background: it seems like we switched that code as there were issues previously with spaces in the path (see issue #19, change made in fcf5bce), BUT it looks like ODBC might deal with that (see 63c8747).
@erex: I’ll put together a patch and if you can see whether that works on your machine that would be great.
dill
added a commit
that referenced
this issue
Apr 16, 2018
Thanks to Grant Humphries for suggesting a fix. Thread: #35
Can’t keep up with you guys. Grant has a) changed a line of db_get() and b) switched to the golftees project.
Here’s what happens when I run native db_get() code on the golftees example shipped with readdst
> library(readdst)
> project <- system.file("Golftees-example", package="readdst")
> project <- paste0(project,"/Golftees")
> converted <- convert_project(project)
Warning message: In create_bins(data, cuts) : Some distances were outside bins and have been removed. > str(converted, max=1)
List of 10
$ FI - Petersen :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - MR dist :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - MR dist+size+sex+exp:List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ ds (single platform) :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ trial :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ io.fi :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ io.pi :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ test intervals :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - Petersen 1 :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - Petersen 2 :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
- attr(*, "class")= chr "converted_distance_analyses""converted_distance_analysis" $ test intervals :List of 13 ..- attr(*, "class")= chr "converted_distance_analysis" $ FI - Petersen 1 :List of 13 ..- attr(*, "class")= chr "converted_distance_analysis" $ FI - Petersen 2 :List of 13 ..- attr(*, "class")= chr "converted_distance_analysis" - attr(*, "class")= chr "converted_distance_analyses"
--
so no problem reading the golftee project either
I think the issue here is that the vanilla ODBC setup in Windows 10 is
different from the options we set in readdst. If we can keep the ODBC
setup assumptions to a minimum we should have something that works for
everyone. At the moment it’s not clear to me that this fix doesn’t break
other functionality (namely spaces in filenames, which seems to have
been the reason for the previous change), so we need to check that.
…
On 16/04/2018 16:15, erex wrote:
Can’t keep up with you guys. Grant has a) changed a line of db_get() and
b) switched to the golftees project.
Here’s what happens when I run native db_get() code on the golftees
example shipped with readdst
| > library(readdst) > project <- system.file(«Golftees-example»,
package=»readdst») > project <- paste0(project,»/Golftees») > converted
<- convert_project(project) Warning message: In create_bins(data, cuts)
: Some distances were outside bins and have been removed. >
str(converted, max=1) List of 10 $ FI — Petersen :List of 13 ..- attr(*,
«class»)= chr «converted_distance_analysis» $ FI — MR dist :List of 13
..- attr(*, «class»)= chr «converted_distance_analysis» $ FI — MR
dist+size+sex+exp:List of 13 ..- attr(*, «class»)= chr
«converted_distance_analysis» $ ds (single platform) :List of 13 ..-
attr(*, «class»)= chr «converted_distance_analysis» $ trial :List of 13
..- attr(*, «class»)= chr «converted_distance_analysis» $ io.fi :List of
13 ..- attr(*, «class»)= chr «converted_distance_analysis» $ io.pi :List
of 13 ..- attr(*, «class»)= chr «converted_distance_analysis» $ test
intervals :List of 13 ..- attr(*, «class»)= chr
«converted_distance_analysis» $ FI — Petersen 1 :List of 13 ..- attr(*,
«class»)= chr «converted_distance_analysis» $ FI — Petersen 2 :List of
13 ..- attr(*, «class»)= chr «converted_distance_analysis» — attr(*,
«class»)= chr «converted_distance_analyses»»converted_distance_analysis»
$ test intervals :List of 13 ..- attr(*, «class»)= chr
«converted_distance_analysis» $ FI — Petersen 1 :List of 13 ..- attr(*,
«class»)= chr «converted_distance_analysis» $ FI — Petersen 2 :List of
13 ..- attr(*, «class»)= chr «converted_distance_analysis» — attr(*,
«class»)= chr «converted_distance_analyses» — |
so no problem reading the golftee project either
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
<#35 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AAAHoRzk2DCg6WyU2q6DIuAvSJVyy6Slks5tpLWVgaJpZM4TMsMt>.
OK, install Dave’s latest patch of readdst:
> require(readdst)
> project <- system.file("Stratify", package="readdst")
> project <- paste0(project,"/Vignette-stratify")
> project
[1] "C:/Users/erexstad/Documents/R/win-library/3.4/readdst/Stratify/Vignette-stratify"
> converted <- convert_project(project)
> str(converted, max=1)
List of 3
$ Half-normal cosine no stratification exact:List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ Half-normal cosine strat-specific detfn :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ Half-normal cosine pooled detfn :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
- attr(*, "class")= chr "converted_distance_analyses"
happy with stratify example
> project <- system.file("Golftees-example", package="readdst")
> project <- paste0(project,"/Golftees")
> converted <- convert_project(project)
Warning message:
In create_bins(data, cuts) :
Some distances were outside bins and have been removed.
> str(converted, max=1)
List of 10
$ FI - Petersen :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - MR dist :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - MR dist+size+sex+exp:List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ ds (single platform) :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ trial :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ io.fi :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ io.pi :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ test intervals :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - Petersen 1 :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ FI - Petersen 2 :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
- attr(*, "class")= chr "converted_distance_analyses"
happy with golftees; haven’t done the ‘path with spaces’ experiment
Yeah, that’s what it seems, @dill @erex. Thanks for the help there! I’ll give this a go.
Aye — seems to be working there now with the patch. Thanks again folks
Running this code (notice space in path)
devtools::install_github("DistanceDevelopment/readdst@mdbwoes")
library(readdst)
project <- paste0("C:/Users/erexstad/Documents/My Distance Projects/Stratify","/Vignette-stratify")
project
converted <- convert_project(project)
str(converted, max=1)
sessionInfo()
works fine
> library(readdst)
> project <- paste0("C:/Users/erexstad/Documents/My Distance Projects/Stratify","/Vignette-stratify")
> project
[1] "C:/Users/erexstad/Documents/My Distance Projects/Stratify/Vignette-stratify"
> converted <- convert_project(project)
Loading required package: RODBC
> str(converted, max=1)
List of 3
$ Half-normal cosine no stratification exact:List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ Half-normal cosine strat-specific detfn :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
$ Half-normal cosine pooled detfn :List of 13
..- attr(*, "class")= chr "converted_distance_analysis"
- attr(*, "class")= chr "converted_distance_analyses"
> sessionInfo()
R version 3.4.3 (2017-11-30)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows >= 8 x64 (build 9200)
Aye, @erex — that also works for me on this end. Same output.
Thanks folks, closing this having merged 77ff4ba.
In my previous HANA and R blogs, I have been forced to create .csv files from HANA and read them on R…an easy but also boring procedure…specially if your R report is supposed to be run on a regular basis…having to create an .csv file every time you need to run your report it’s not a nice thing…
After spending some time reading and researching R…I finally came to a library that can read data from any relational database and being HANA, ODBC capable, the work is just a piece of cake -;)
For this examples, we must install two libraries: RODBC and Plotrix and create the DSN connection as shown here…
HANA already provides us a driver, so we’re cool
Here we’re going to “Add…” a new “System DSN”
Assign a name for the “Data Source Name”, “Description” is optional and “Server:Port” should be of course filled.
Now…we’re ready to go to our HANA studio an create a table and a stored procedure…
| GetTicketsByYearMonth |
|---|
|
CREATE PROCEDURE GetTicketsByYearMonth(IN var_year NVARCHAR(4),IN var_month NVARCHAR(2)) LANGUAGE SQLSCRIPT AS BEGIN select count(bookid), carridfrom sflight.snvoice where year(fldate) = VAR_YEAR and month(fldate) = VAR_MONTH group by carrid into TICKETS_BY_YEAR_MONTH; END; |
After we run our Stored Procedure…we have all the information in the table…Ok…only two fields…today was a hard day…I’m tired -:P
Finally…we can code some R! First, we’re going to create a Fan Plot (The Plotix library is needed for that one) and then a Bar Plot…I used the same code for both, so just replace the comment on one by the other one and run it again…I know…I’m being lazy again…but at least I’m not reinveting the wheel -;) Two codes with only 1 different line? No thanks…
| Fan_Plot_Graph.R |
|---|
|
library(“plotrix”) library(“RODBC”) ch<-odbcConnect(“HANA”,uid=”P075400″,pwd=”***”) res<-sqlFetch(ch,”P075400.TICKETS_BY_YEAR_MONTH”) fan.plot(res$TICKETS,labels=res$CARRIER, main=”Tickets for December 2011″) #barplot(res$TICKETS,names.arg=res$CARRIER) odbcClose(ch) |
The code is very simple…we call the libraries we need, we stablish a communication to our DSN, we fetch the data from the table, we create the graphics and finally we close the connection.
And here come the graphics…
I will keep investigating on this way to connect HANA and R…more blogs should be on the way -;)