[R-sig-DB] Fwd: rmysql and strings containg \n

[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 using bank-banque-canada.ca>:
> 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
>
>>-----Original Message-----
>>From: r-sig-db-bounces using stat.math.ethz.ch [mailto:r-sig-db-
>>bounces using stat.math.ethz.ch] On Behalf Of H. Felix Wittmann
>>Sent: October 27, 2009 5:50 PM
>>To: Jeffrey Horner
>>Cc: r-sig-db using stat.math.ethz.ch
>>Subject: Re: [R-sig-DB] Fwd: rmysql and strings containg \n
>>
>>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 using vanderbilt.edu>
>>>
>>> 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:
>>>>
>>>> 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
>>>>
>>>> _______________________________________________
>>>> R-sig-DB mailing list -- R Special Interest Group
>>>> R-sig-DB using stat.math.ethz.ch
>>>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>>
>>>
>>> --
>>> http://biostat.mc.vanderbilt.edu/JeffreyHorner
>>
>>_______________________________________________
>>R-sig-DB mailing list -- R Special Interest Group
>>R-sig-DB using stat.math.ethz.ch
>>https://stat.ethz.ch/mailman/listinfo/r-sig-db
> ====================================================================================
>
> La version française suit le texte anglais.
>
> ------------------------------------------------------------------------------------
>
> This email may contain privileged and/or confidential information, and the Bank of
> Canada does not waive any related rights. Any distribution, use, or copying of this
> email or the information it contains by other than the intended recipient is
> unauthorized. If you received this email in error please delete it immediately from
> your system and notify the sender promptly by email that you have done so.
>
> ------------------------------------------------------------------------------------
>
> Le présent courriel peut contenir de l'information privilégiée ou confidentielle.
> La Banque du Canada ne renonce pas aux droits qui s'y rapportent. Toute diffusion,
> utilisation ou copie de ce courriel ou des renseignements qu'il contient par une
> personne autre que le ou les destinataires désignés est interdite. Si vous recevez
> ce courriel par erreur, veuillez le supprimer immédiatement et envoyer sans délai à
> l'expéditeur un message électronique pour l'aviser que vous avez éliminé de votre
> ordinateur toute copie du courriel reçu.
>
>

-------------- 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>