NISHIYAMA Tomoaki | 9 Oct 11:21 2012
Picon

Insufficient PROTECT in RMySQL code

Hi,

We recently identified errors in RPostgreSQL that PROTECT is not
issued where needed.  The behaviour reported at
http://code.google.com/p/rpostgresql/issues/detail?id=42
is similar in RMySQL.

The fix is seeking every C code returning a pointer to s_object or 
its aliases (i.e., Mgr_Handle, Con_Handle, Res_Handle, Db_Handle),
and carefully ensure the structure returned is properly constructed in
PROTECTed block.

We do not have test for every places, but the following example
suggests that RMySQL probably has at least one similar error.

> library(RMySQL)
Loading required package: DBI
> con <- dbConnect('MySQL')
> rs <-dbSendQuery(con,'SELECT 1;')
> dbColumnInfo(rs)
  name  Sclass                type len precision scale nullOK
1    1 integer FIELD_TYPE_LONGLONG   1         1     0  FALSE
> dbColumnInfo(rs)
  name  Sclass                type len precision scale nullOK
1    1 integer FIELD_TYPE_LONGLONG   1         1     0  FALSE
> gctorture(TRUE)
> dbColumnInfo(rs)
  name  Sclass                type len precision scale nullOK
1    1 integer FIELD_TYPE_LONGLONG   1         1     0  FALSE
> dbColumnInfo(rs)
(Continue reading)

David | 7 Oct 23:16 2012

DB updates with ROracle - ORA-01000

I have an R script that makes an ROracle connection to a DB and pulls
data.. analyzes and then updates approx 7,000 records.

I have two functions defined in my script that look like this:

       # Create the updateTC function
        updateTC <- function(dfrm) {
                with(dfrm, {
                updateDB(paste("update SIMETRA.RTA_MARKET_STATS set
CLOSEST_LOC_TRAVEL_COEFFICIENT = '",format(GEOGRAPHIC_COEFFICIENT,
scientific=FALSE, digits=6),"' where RTA_MARKET_STATS_ID = '",MSID,"'", sep
= ""))
                })
        }

        # Create the updateDB function
        updateDB <- function(stmt) {
                for (update in stmt) {
                        res <- dbSendQuery(conn, update)
                        res <- dbCommit(conn)
                        summary(drv)
                }
        }

and I call it like so (cr is a dataframe with my results):

        # Update the DB with the new geographic coefficients
        updateTC(cr)
        res <- dbCommit(conn)

(Continue reading)

David | 4 Oct 22:44 2012

Intermittent connectivity issues for RJDBC / Oracle

I will forward more details like versions of xyz when i can, but any
thoughts on what I can focus on to debug the problem described below?

Thank you, David
---------- Forwarded message ----------
From: "dh_ss" <david@...>
Date: Oct 4, 2012 1:29 AM
Subject: [R] Intermittent connectivity issues for JDBC / Oracle
To: <r-help@...>

I have been experiencing issues with an R script hanging when connecting to
an Oracle database.  To help debug, I found a function here in the nabble
forum and made a small test script:

  #!/usr/bin/Rscript --no-restore --no-save --no-init-file
  library("RJDBC")
  drv<-JDBC("oracle.jdbc.OracleDriver","/home/oracle/lib/ojdbc6.jar", "'")

  dbCheck = function(url, user='', pwd='') {
    j = .jcall("java/sql/DriverManager", "Ljava/sql/Connection;",
  "getConnection", url, user, pwd, check=FALSE)
    x = .jgetEx()
    .jcheck()
    x
  }
  dbCheck("jdbc:oracle:thin: <at> 192.168.3.10:1521:psrndpt1","myuser","mypass")

When I run the file - sometimes it works, returning NULL, and sometimes it
hangs for between 1 and 2.5 minutes before throwing an error.  The database
is new and stable, on the same network - with no connection issues from any
(Continue reading)

James David Smith | 28 Sep 16:14 2012
Picon

R and PostgreSQL - Writing data?

Dear all,

Sorry if this isn't quite the right place, but it's the first time
I've posted here. My issue is to do with writing to a PostgreSQL
database from within R. My situation is best explained by some R code
to start:

#Connect to the database
con <- dbConnect(PostgreSQL(), user="postgres", password="password",
dbname="database")

#Get some data out of the database.
ucam_no2$original_data <- dbGetQuery(con, "select ucam_no2 FROM table")

This returns say 10000 rows of data, but there is only data in about
half of those rows. What I want to do is interpolate the missing data
so I do this:

#Generate some data
ucam_no2$interpolated_data <- na.approx(ucam_data$ucam_no2, na.rm = FALSE)

This works well and I now have 10000 rows of data with no empty cells.
I now want to write this back into my PostgresSQL database. Into the
same row that I took the data from in the first place. But I don't
know how. I can write to a new table with something like the below,
but what I'd really like to do is put the data back into the table I
got it from.

# Try to write the data back
dbWriteTable(con, "new_data", ucam_no2$interpolated_data)
(Continue reading)

Paul Gilbert | 18 Sep 17:40 2012
Picon

RMySQL and RPostgreSQL interaction

I am doing some comparison for which I load both RMySQL and RPostgreSQL 
from my own packages TSMySQL and TSPostgreSQL and I am getting the 
following warning building a vignette. I think with package namespaces / 
S4 definitions it is a bit difficult to generate a self contained 
example, at least I have not yet managed to do that, but I would 
appreciate any suggestions about what needs to be done.

Loading required package: TSPostgreSQL
Loading required package: RPostgreSQL
Warning in .simpleDuplicateClass(def, prev) :
   A specification for class "dbObjectId" in package 'RPostgreSQL' seems 
equivalent to one from package 'RMySQL' and is not turning on duplicate 
class definitions for this class

(I'm using  R version 2.15.1 on Ubuntu.)

Thanks,
Paul

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@...
https://stat.ethz.ch/mailman/listinfo/r-sig-db

Luis Ridao | 7 Sep 15:26 2012
Picon

RODBC connection linux (ubuntu)

HI,

i think this problem has come up pretty often on this and other lists
and still have not managed to solved it.

i need to connect to a an Oracle database through R.

I have installed the oracle install client and the package RODBC in R
but still get the same error:

[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data
source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=MAGNUS;UID=my_uid;PWD=my_pwd") :
  ODBC connection failed

/etc/odbc.ini looks like:

odbc.ini
---------------------------------------
[ORACLE_SAMPLE]
driver = Easysoft ODBC-Oracle WP
description = Easysoft Oracle ODBC WP driver
server = oserver
port = 1521
sid = 192.168.20.129/MAGNUS
user = luisr
password =my_pwd
logging = No
logfile =
enable_user_catalog = yes
(Continue reading)

Kate Dresh | 25 Aug 01:10 2012
Picon

data frame remove column

*Hi all,*

I'm trying to filter a file through the columns. This file below is a
example of my data frame. My true data frame has seven hundred thousand
columns and 500 hundred lines. I need to identify and to remove all columns
that all elements equal a number 1. In this my case, columns were deleted
are number 1,5 and 10.

file <-read.table(text="

1  0  2  2  1  1  5  1  1  1

1  0  2  2  1  1  5  1  1  1

1  0  2  2  1  2  5  2  2  1

1  0  2  2  1 1  5  1  1  1

1  0 2  2   1  0  5  0  2  1

1  0  2 2   1  1  5  1  0  1

",sep="",header=FALSE)

the result after the filter will be

0  2  2  1  5  1  1

 0  2  2  1  5  1  1

(Continue reading)

julien cuisinier | 25 Jun 13:46 2012
Picon

FW: Connect R to MySQL DB installed via XAMPP


Hi List,

On windows XP & R2.15. Session info below. 

I installed XAMPP on my machine - I can't install MYSQL on the C:\Program file dir because no admin rights, so
I installed XAMPP onto a personal directory...which I guess makes things slightly harder, but I am
hopeful I am not thhe first one struggling on this!

Any feedback  redirection / hints hugely appreciated

(1) I tried to install RMySQL which I used successsfully on Linux in the past but it seems to be a trouble on
Windows, no binary available on CRAN (anymore?) & when trying the "type = 'source' " option I get the
following: 

> install.packages("RMySQL",type="source")
trying URL 'http://cran.ma.imperial.ac.uk/src/contrib/RMySQL_0.9-3.tar.gz'
Content type 'application/x-gzip' length 165363 bytes (161 Kb)
opened URL
downloaded 161 Kb

'\\UKLonHome.HDS.Int\Home$\julien.cuisinier'
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.
* installing *source* package 'RMySQL' ...
** package 'RMySQL' successfully unpacked and MD5 sums checked
ERROR: configuration failed for package 'RMySQL'
* removing 'P:/PersoServer/Programs/R/R-2.15.0/library/RMySQL'

The downloaded source packages are in
(Continue reading)

Jim Jones | 17 Jun 11:59 2012
Picon

Database R Package

Gentlemen,

Last winter at university in the R Programming course I developed an R
Package (so far called rdba) for dealing with some DBA tasks from R
scripts. The package basically avoids the need of the user to have
knowledge of database (PostgreSQL + PostGIS) scripts to manage their
databases. It aims to facilitate the scripts development when "on the fly"
you need to handle databases and tablespaces in a physical level.

The package contains the following functions:

Create Database (With or without a template)
Drop Databse
List Database
Database Exists

Create Tablespace
Drop Tablespace
List Tablespace
Rename Tablespace
Tablespace Exists
Change Owner Tablespace

ListUsers
UserExists

I would like to hear your opinion about submitting it to CRAN, since I'm
new in the list and you are the experts. Initially I just wrote the
functions for PostgreSQL, but it is already in my plans to extend it to
Oracle and SQL Server. What do you think?
(Continue reading)

Slawin, Scott | 7 Jun 15:52 2012

Using LDAP connection

Hello,

I am trying to find an R package that will allow me to connect to Oracle
using LDAP.  Can someone help direct me to such a package or
documentation.  

RCURL does mention LDAP, but is not very specific on how to use LDAP
with sql statements.

Thanks in advance.

Scott Slawin
AFT-Business Intelligence Delivery (BID) Team
Allstate Insurance Company
3100 Sanders Road, Suite J3
Northbrook, IL 60062 
Phone 847-402-3649 
sslaw@...
<mailto:emailname@...>  

	[[alternative HTML version deleted]]

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@...
https://stat.ethz.ch/mailman/listinfo/r-sig-db

Idit Buch | 3 Jun 14:04 2012

calloc error issued by sqlQuery

Hi,

I'm using R version 2.14.1 on Linux (Centos) and trying to issue a "select * from ..." query using sqlQuery(...).
I then receive the following error:
Error in odbcQuery(channel, query, rows_at_time) :
  Calloc could not allocate memory (18446744073709551616 of 22816 bytes)

The function odbcGetInfo(channel) returns the following:

                  DBMS_Name                    DBMS_Ver
     "Microsoft SQL Server"                "09.00.4035"
            Driver_ODBC_Ver            Data_Source_Name
                    "03.52"                       "DSN"
                Driver_Name                  Driver_Ver
"libsqlncli-11.0.so.1790.0"                "11.00.1790"
                   ODBC_Ver                 Server_Name
                    "03.52"                    "RGNSQL"

The function sessionInfo() returns the following:
R version 2.14.1 (2011-12-22)
Platform: x86_64-redhat-linux-gnu (64-bit)

locale:
[1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C
[3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8
[5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8
[7] LC_PAPER=C                 LC_NAME=C
[9] LC_ADDRESS=C               LC_TELEPHONE=C
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C

(Continue reading)


Gmane