subject:"\[R\] using sprintf to pass a variable to a RMySQL query"

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

2010-03-11 Thread Uwe Ligges


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 walleralison.wal...@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@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.


__
R-help@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.

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

2010-03-11 Thread alison waller

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

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

2010-03-10 Thread 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 alison.wal...@embl.de 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 alison.wal...@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@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.

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

2010-03-10 Thread alison waller

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 alison.wal...@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@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.

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

2010-03-09 Thread alison waller

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:

 __
 R-help@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.



__
R-help@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.

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

2010-03-09 Thread Gabor Grothendieck

On Tue, Mar 9, 2010 at 7:10 AM, alison waller alison.wal...@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@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.

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

2010-03-08 Thread alison waller

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:

__
R-help@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.

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

2010-03-08 Thread jim holtman

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 alison.wal...@embl.de 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:

 __
 R-help@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.htmlhttp://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]]

__
R-help@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.

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

2010-03-08 Thread 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 jholt...@gmail.com 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 alison.wal...@embl.de 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:

 __
 R-help@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.htmlhttp://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]]

 __
 R-help@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.


__
R-help@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.

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

2010-03-08 Thread Don MacQueen


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:

__
R-help@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.



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

__
R-help@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.

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

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

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

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

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

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

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

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

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

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

10 matches

Site Navigation

Mail list logo

Footer information