[R] using sprintf to pass a variable to a RMySQL query

Thu Mar 11 17:12:16 CET 2010

Hi all,

I re-installed R and tcltk.  I find some of the documentation misleading
as it indicates that tcltk is included with R.  And when you type
library() it shows tcltk, even though it hasn't been installed.

Anyways, I've decided to go with sprintf.
I am having errors with my query criteria.

I have slightly changed by criteria as I want to match 'MGi.' (so that I
match MG1. and MG10. if I did %MGi% won't I match MG1. and MG10.
I tried to escape the period with a backslash,quotes and double period. 
I think that R is fine with the syntax, but SQL doesn't like it.

Can anyone please help me with the syntax.

thank you,

########## Error##############
Error in mysqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not run statement: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '1' at line 2
)
Calls: dbGetQuery ... .valueClassTest -> is -> is -> mysqlExecStatement
-> .Call
Execution halted
############Script#################

library(RMySQL)
mysql<-dbDriver("MySQL")
con<-dbConnect(mysql,username="u",host="g",password="s",port=,dbname="M")

i<-1
k<-0

while (k<=17) {
 while (i<=72) {

    sqlcmd_ScaffLen<-sprintf('SELECT scaffold.length
    FROM scaffold,scaffold2contig,contig2read
    WHERE scaffold.scaffold_id=scaffold2contig.scaffold_id AND
    scaffold2contig.contig_id=contig2read.contig_id AND
contig2read.read_id LIKE
    \'%%MG%d..%%\'' ,i)

    sqlcmd_contigs<-sprintf('SELECT length FROM contig WHERE external_id
 LIKE\'%%MG%d..%%\'',i)

    sqlcmd_singletons<-paste('SELECT COUNT(*) FROM contig WHERE
    read_count=1 AND external_id LIKE \'%%MG%d..%%\'',i)

    MG_ScaffoldLen<-dbGetQuery(con,sqlcmd_ScaffLen)

    MG_ContigsLen<-dbGetQuery(con,sqlcmd_contigs)

    MG_SingletonsCount<-dbGetQuery(con,sqlcmd_singletons)

MG_ScaffoldLen_Summ<-as.data.frame(c(summary(MG_ScaffoldLen$length),MG_SingletonsCount))
    MG_ContigsLen_Summ<-summary(MG_ContigsLen$length)

write.table(MG_ScaffoldLen_Summ,file="ScaffoldLen_SummStats.txt",append=TRUE,sep='\t')

write.table(MG_ContigsLen_Summ,file="ContigsLen_SummStats.txt",append=TRUE,sep='\t')

    # Keep names for 4 of them so we can do summary plots for each treatment
    # (ie combine all 4 reps)

    MG_ScaffoldLen<-assign(paste('MG_ScaffoldLen',i,sep=''),MG_ScaffoldLen)

    MG_ContigsLen<-assign(paste('MG_ContigsLen',i,sep=''),MGContigsLen)

    i<-i+18
    }
############### Summary Plots For each Treatment ##################

  jpeg(file=sprintf("Boxplots%dSanger_Virus.jpeg",k))

sprintf("boxplot(MG_ScaffoldLen(1+%d)$length,MG_ScaffoldLen(18+%d)$length,MG_ScaffoldLen(36+%d)$length,MG_ScaffoldLen(54+%d)$length)",k)
  dev.off()

  jpeg(file=sprintf("Scaffold_histograms%dSanger_Virus.jpeg",k))
  par(mfrow=c(1,3))
  sprintf("hist(MG_ScaffoldLen(1+%d)$length)",k)
  sprintf("hist(MG_ScaffoldLen(18+%d)$length)",k)
  sprintf("hist(MG_ScaffoldLen(36+%d)$length)",k)
  sprintf("hist(MG_ScaffoldLen(54+%d)$length)",k)
  dev.off()

  jpeg(file=sprintf("Contig_histograms%dSanger_Virus.jpeg",k))
  par(mfrow=c(1,3))
  sprintf("hist(MG_ContigsLen(1+%d)$length)",k)
  sprintf("hist(MG_ContigsLen(18+%d)$length)",k)
  sprintf("hist(MG_ContigsLen(36+%d)$length)",k)
  sprintf("hist(MG_ContigsLen(54+%d)$length)",k)
  dev.off()

  k<-k+1
  i<-1+k
  }

On 03/11/10 16:01, Uwe Ligges wrote:
> On 10.03.2010 12:45, alison waller wrote:
>> Thanks Gabor,
>>
>> As I said I would like to use gsubfn, but I am having problems
>> installing it, which I assume are due to some conflict with the current
>> tcltk package
>>
>> Below is the error I got after issuing install.packages("gsubfn")
>>
>> Any advice?
>
>
> Re-install R including the tcltk package?
>
> Uwe Ligges
>
>
>> ###################
>> * Installing *source* package 'gsubfn' ...
>> ** R
>> ** demo
>> ** inst
>> ** preparing package for lazy loading
>> Warning: S3 methods '$.tclvar', '$<-.tclvar', 'as.character.tclObj',
>> 'as.character.tclVar', 'as.double.tclObj', 'as.integer.tclObj',
>> 'as.logical.tclObj', 'print.tclObj', '[[.tclArray', '[[<-.tclArray',
>> '$.tclArray', '$<-.tclArray', 'names.tclArray', 'names<-.tclArray',
>> 'length.tclArray', 'length<-.tclArray', 'tclObj.tclVar',
>> 'tclObj<-.tclVar', 'tclvalue.default', 'tclvalue.tclObj',
>> 'tclvalue.tclVar', 'tclvalue<-.default', 'tclvalue<-.tclVar' were
>> declared in NAMESPACE but not found
>> Error in namespaceExport(ns, exports) :
>>    undefined exports: addTclPath, as.tclObj, is.tclObj, is.tkwin
>> Error : package 'tcltk' could not be loaded
>> ERROR: lazy loading failed for package 'gsubfn'
>> * Removing '/g/bork3/x86_64/lib64/R/library/gsubfn'
>>
>> The downloaded packages are in
>>          '/tmp/RtmpkfvT5f/downloaded_packages'
>> Updating HTML index of packages in '.Library'
>> Warning message:
>> In install.packages("gsubfn", lib = "/g/bork3/x86_64/lib64/R/library") :
>>    installation of package 'gsubfn' had non-zero exit status
>>
>> ########## this is the error when I tried to install tcltk#
>> install.packages("tcltk")
>> Warning message:
>> In getDependencies(pkgs, dependencies, available, lib) :
>>    package 'tcltk' is not available
>>
>>
>>
>> On 03/09/10 16:26, Gabor Grothendieck wrote:
>>> On Tue, Mar 9, 2010 at 7:10 AM, alison
>>> waller<alison.waller at embl.de>  wrote:
>>>
>>>> Hi all,
>>>>
>>>> Thanks for help with the paste and sprintf syntax.
>>>>
>>>> So I've decided to use paste and or sprintf.  'gsubfn' looks like a
>>>> great package but unfortunately I've had problems installing it, as I
>>>> don't think it likes the version of tcltk that is installed.  I'm
>>>> working on a few unix clusters with many computers and there seems
>>>> to be
>>>> problems with different versions of R and different versions of the
>>>> packages on different computers.
>>>>
>>> The fn$ functionality that I mentioned does not use the tcltk package
>>> so the version of tcltk should not matter.
>>>
>>> The only part of the package that uses tcltk is strapply, which is not
>>> used here, and even in that case there is R code to it as well if you
>>> use strapply(..., engine = "R") or use ostrapply.
>>>
>>> Also the older 0.3-9 version of the gsubfn package did not use tcltk
>>> at all.
>>>
>>
>> ______________________________________________
>> R-help at 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.