Skip to content

rmysql and strings containg \n

6 messages · H. Felix Wittmann, Jeffrey Horner, Paul Gilbert

Original
H. Felix Wittmann
# 
let me start by saying RMySQL is a wonderful package of a superb
language:R.? One particular feature I cant seem to get rid of easily
is the hadling of  \n in strings. Is there an easy solution to this?

H. Felix Wittmann

PS : What follows is some code, demonstrating the problem

require(RMySQL)

m <- dbDriver("MySQL")
myCon <- dbConnect(m,dbname = "myDatabase"))? ## replace myDatabase by
what is appropriate for you


test <- data.frame(id=1,myString='beginning1 \n end1')
[1] TRUE
????? id??? myString
1????? 1 beginning1
?end1 NA??????? <NA>
[1] 2 2
[1] 1 2
6 days later
H. Felix Wittmann
# 
I' ve noticed a few typos in my previous post therefore I send a
cleaned up version


require(RMySQL)

m <- dbDriver("MySQL")
myCon <- dbConnect(m,group = 'whateverGroup')
# replace this (ie 'whateverGroup') by the appropriate group specified
in your my.cnf file
# on a typical UBUNTU installation you can
# modify this using the command line editor (eg bash) by this command
# sudo nano /etc/mysql/my.cnf

test <- data.frame(id=1,myString='beginning1 \n end1')
# the above line produces (this is what the output should like like) :
# > test
#   id           myString
# 1  1 beginning1 \n end1

dbWriteTable(myCon,name='test', value=test)
dbReadTable(myCon,name='test')
# the above line produces (here we can see that the output is not the
same as above) :
#       id    myString
# 1      1 beginning1
#  end1 NA        <NA>


H. Felix Wittmann
5 days later
Jeffrey Horner
# 
Hi H. Felix Wittmann,

We require that you tell us R's version, RMySQL's version, and MySQL's 
version upon which you are running your code. Then we can better 
determine if your problem is a bug or a feature.

Best,

Jeff

H. Felix Wittmann wrote on 10/20/2009 04:37 PM:

  
    
  


  


      

    

    
1 day later

    

      
      

        


  

        

      H. Felix Wittmann
    

    

      
      #
    

  


  

      
Hi Jeff,

thanks for your answer. At the moment I've got the following

R version 2.9.2 (2009-08-24)
?RMySQL? version 0.7-4
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using
readline 5.2

That said, similar things have consistently shown up in pretty much
all the environments that I've tested.

Felix


2009/10/26 Jeffrey Horner <jeff.horner at vanderbilt.edu>

            

      
      
      
    

        

      
      
    

            

      
      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Paul Gilbert
    

    

      
      #
    

  


  

      
I've not had much luck myself with \n in MySQL strings. I suspect you get the same problem using the mysql client directly, which is usually a good test to see if the problem is R related or not. This could also have something to do with type of the field, and possibly with the character encoding.

Paul

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

        
====================================================================================

La version fran?aise suit le texte anglais.

------------------------------------------------------------------------------------

This email may contain privileged and/or confidential in...{{dropped:26}}

  
  


  


      

    

    
1 day later

    

      
      

        


  

        

      H. Felix Wittmann
    

    

      
      #
    

  


  

      
Paul,

thank you for your message. I agree with you that things are
interconnected with character encoding.

I attach a modified version of a function (dbWriteTable.2) I posted
earlier on this mailing list which may represent a hint towards a
solution. With this fucnction I managed (and manage) to write strings
containing \n into mysql databases. In particular it doesn't fail the
test I gave at the start of thread.

I consider the function dbWriteTable.2 not a full solution since it
requires a directory with write access, by.default it is set to /tmp/.

for ease of reference I give this example below (bottom line is
dbWriteTable.2 works, dbWriteTable doesnt)

maybe, if there is no better solution, we could include this in the package?

Felix

Example below

m <- dbDriver("MySQL")
myCon <- dbConnect(m,group = 'test')
# replace this (ie 'whateverGroup') by the appropriate group specified
# in your my.cnf file
# on a typical UBUNTU installation you can
# modify this using the command line editor (eg bash) by this command
# sudo nano /etc/mysql/my.cnf

test <- data.frame(id=1,myString='beginning1 \n end1')
# the above line produces (this is what the output should like like) :
# > test
#   id           myString
# 1  1 beginning1 \n end1

dbWriteTable(myCon,name='test', value=test , overwrite = TRUE)
dbReadTable(myCon,name='test')

# [1] TRUE
#       id    myString
# 1      1 beginning1
#  end1 NA        <NA>

dbWriteTable.2 (myCon,name='test2', value=test, overwrite = TRUE)
dbReadTable(myCon,name='test2')
# [1] TRUE
#   id           myString
# 1  1 beginning1 \n end1







2009/10/28 Paul Gilbert <pgilbert at bank-banque-canada.ca>:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        
-------------- next part --------------
A non-text attachment was scrubbed...
Name: dbWriteTable.2.r
Type: application/octet-stream
Size: 782 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20091029/39d1d6e2/attachment.obj>