[R] 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 <jholtman at 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.waller at 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 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<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]]
>
> ______________________________________________
> 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.
>