using sprintf to pass a variable to a RMySQL query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

using sprintf to pass a variable to a RMySQL query

alison waller-2
Hello,

I am using RmySQL and would like to iterate through a few queries.

I would like to use sprintf but I think I'm having problems mixing and
matching the sprintf syntax and the SQL regex.

I have checked my sqlcmd and it works when I wan to match %MG1% but how
do I iterate for i 1-72?  Escape characters,?

thanks in advance

i<-1
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%s%' ,i)

========= Here is my vague error message

Error: unexpected input in:

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

jholtman
Try this:

i<-1
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_ScaffLen

Your problem:
1. Need %% to create % when using sprintf
2. Need to use %d and not %s for integer values
3. Need to escape the quote marks.

On Mon, Mar 8, 2010 at 8:06 AM, alison waller <[hidden email]> wrote:

> Hello,
>
> I am using RmySQL and would like to iterate through a few queries.
>
> I would like to use sprintf but I think I'm having problems mixing and
> matching the sprintf syntax and the SQL regex.
>
> I have checked my sqlcmd and it works when I wan to match %MG1% but how
> do I iterate for i 1-72?  Escape characters,?
>
> thanks in advance
>
> i<-1
> 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%s%' ,i)
>
> ========= Here is my vague error message
>
> Error: unexpected input in:
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html<http://www.r-project.org/posting-guide.html>
> and provide commented, minimal, self-contained, reproducible code.
>



--
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

Gabor Grothendieck
Another possibility is to use fn$ in the gsubfn package. Just preface
any command with fn$ to enable a quasi-perl-like string interpolation.
In this example $i is replaced with 1:

> library(gsubfn)
> library(sqldf)
> i <- 1
> fn$sqldf("select count(*) from CO2 where Plant like '%n$i%'")
  count(*)
1       14

> # as seen here:
> fn$identity("select count(*) from CO2 where Plant like '%n$i%'")
[1] "select count(*) from CO2 where Plant like '%n1%'"

See http://gsubfn.googlecode.com for more.



On Mon, Mar 8, 2010 at 11:08 AM, jim holtman <[hidden email]> wrote:

> Try this:
>
> i<-1
> 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_ScaffLen
>
> Your problem:
> 1. Need %% to create % when using sprintf
> 2. Need to use %d and not %s for integer values
> 3. Need to escape the quote marks.
>
> On Mon, Mar 8, 2010 at 8:06 AM, alison waller <[hidden email]> wrote:
>
>> Hello,
>>
>> I am using RmySQL and would like to iterate through a few queries.
>>
>> I would like to use sprintf but I think I'm having problems mixing and
>> matching the sprintf syntax and the SQL regex.
>>
>> I have checked my sqlcmd and it works when I wan to match %MG1% but how
>> do I iterate for i 1-72?  Escape characters,?
>>
>> thanks in advance
>>
>> i<-1
>> 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%s%' ,i)
>>
>> ========= Here is my vague error message
>>
>> Error: unexpected input in:
>>
>> ______________________________________________
>> [hidden email] mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html<http://www.r-project.org/posting-guide.html>
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>
>
> --
> Jim Holtman
> Cincinnati, OH
> +1 513 646 9390
>
> What is the problem that you are trying to solve?
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

Don MacQueen
In reply to this post by alison waller-2
I always use paste()

i <- 1
sqlcmd_ScaffLen <- paste("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", i ,"%'", sep='')

That should create bits like
    LIKE '%MG1%'
    LIKE '%MG2%'
and so on.

You just have to get the nesting of the single and double quotes
correct - the SQL requires single quotes, so use double quotes for
the fixed character strings insidte paste(). That, and use sep='' to
get rid of unwanted space characters.

Using paste is also effective for constructs like
   IN (3,4,5)
or
   IN ('a','b','c')
though it can be necessary to nest one paste within another

-Don

At 2:06 PM +0100 3/8/10, alison waller wrote:

>Hello,
>
>I am using RmySQL and would like to iterate through a few queries.
>
>I would like to use sprintf but I think I'm having problems mixing and
>matching the sprintf syntax and the SQL regex.
>
>I have checked my sqlcmd and it works when I wan to match %MG1% but how
>do I iterate for i 1-72?  Escape characters,?
>
>thanks in advance
>
>i<-1
>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%s%' ,i)
>
>========= Here is my vague error message
>
>Error: unexpected input in:
>
>______________________________________________
>[hidden email] 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.


--
--------------------------------------
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
Livermore, CA, USA
925-423-1062

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

alison waller-2
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.

So, the other problem is that I want to rename the data.frames and names
of the output jpeg files resulting from the queries.  I've tried a few
different approaches but none seem to work, using sprintf and paste
turns the data frame into just a string of the name.

I have a complicated loop here as I'd like to do some summary output
after every 4 queries (ie. after MG1, MG 19, MG 37, MG 54) then I want
to start again and do for MG2, MG20 etc..

Here's my code below, there are probably error in the loop structure
that I can work out, but I need help with renaming the data frames based
on the parameters i and j

thanks

################################
i<-1
j<-1

for (i<=72 and j<=4){{

sqlcmd_ScaffLen<- paste("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", i ,"%'", sep='')

sqlcmd_contigs<-paste("SELECT length FROM contig WHERE external_id LIKE
'%MG",i,"%'",sep='' )

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

MGi_ScaffoldLen<-dbGetQuery(con,sqlcmd_ScaffLen)
MGi_ContigsLen<-dbGetQuery(con,sqlcmd_contigs)
MGi_SingletonsCount<-dbGetQuery(con,sqlcmd_singletons)

MGi_ScaffoldLen_Summ<-as.data.frame(c(summary(MGi_ScaffoldLen$length),MGi_SingletonsCount))
MGi_ContigsLen_Summ<-summary(MGi_ContigsLen$length)

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

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

i<-i+18
j<-j+1

}

############### Summary Plots For each Treatment ##################

jpeg(file=sprintf("Boxplots_%d.jpeg",i)
boxplot(MGi_ScaffoldLen$length,MG(i+18*j)_ScaffoldLen$length,MG(i+_ScaffoldLen$length,MG59_ScaffoldLen$length,Main="400spec_10virus")
dev.off()

jpeg(file=sprintf("Scaffold_histograms_%d.jpeg",i)
hist(MGi_ScaffoldLen$length)
hist(MG(i+j*18)_ScaffoldLen$length)
hist(MG(i+j*18_ScaffoldLen$length)
hist(MG(i+j*18_ScaffoldLen$length)

dev.off()

jpeg(file=sprintf("Contig_histograms_%d.jpeg",i)
hist(MGi_ContigsLen$length)
hist(MG(i+j*18)_ContigsLen$length)
hist(MG(i+j*18_ContigsLen$length)
hist(MG(i+j*18_ContigsLen$length)

dev.off()

j<-1
i<-2
}


On 03/08/10 21:02, Don MacQueen wrote:

> I always use paste()
>
> i <- 1
> sqlcmd_ScaffLen <- paste("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", i ,"%'", sep='')
>
> That should create bits like
>    LIKE '%MG1%'
>    LIKE '%MG2%'
> and so on.
>
> You just have to get the nesting of the single and double quotes
> correct - the SQL requires single quotes, so use double quotes for the
> fixed character strings insidte paste(). That, and use sep='' to get
> rid of unwanted space characters.
>
> Using paste is also effective for constructs like
>   IN (3,4,5)
> or
>   IN ('a','b','c')
> though it can be necessary to nest one paste within another
>
> -Don
>
> At 2:06 PM +0100 3/8/10, alison waller wrote:
>> Hello,
>>
>> I am using RmySQL and would like to iterate through a few queries.
>>
>> I would like to use sprintf but I think I'm having problems mixing and
>> matching the sprintf syntax and the SQL regex.
>>
>> I have checked my sqlcmd and it works when I wan to match %MG1% but how
>> do I iterate for i 1-72?  Escape characters,?
>>
>> thanks in advance
>>
>> i<-1
>> 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%s%' ,i)
>>
>> ========= Here is my vague error message
>>
>> Error: unexpected input in:
>>
>> ______________________________________________
>> [hidden email] 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.
>
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

Gabor Grothendieck
On Tue, Mar 9, 2010 at 7:10 AM, alison waller <[hidden email]> 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

alison waller-2
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?

###################
* 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 <[hidden email]> 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

Gabor Grothendieck
There is likely something wrong with your R installation.

tcltk is a recommended package and normally comes bundled with R.  You
should not have to install it separately.

Try reinstalling R.

Alternately, try the older version of gsubfn at:

   http://cran.r-project.org/src/contrib/Archive/gsubfn/gsubfn_0.3-8.tar.gz

It does not use tcltk at all.


On Wed, Mar 10, 2010 at 6:45 AM, alison waller <[hidden email]> 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?
>
> ###################
> * 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 <[hidden email]> 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.
>>
>
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

Uwe Ligges-3
In reply to this post by alison waller-2
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<[hidden email]>  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.
>>
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: using sprintf to pass a variable to a RMySQL query

alison waller-2
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<[hidden email]>  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.
>>>
>>
>> ______________________________________________
>> [hidden email] 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.

______________________________________________
[hidden email] 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.