R help - Mar 2010 - using sprintf to pass a variable to a RMySQL query

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:

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@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.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]]

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

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