R help - Oct 2006 - Variables in RODBC environment

Hello Experts,
how can I use variables in the RODBC environment.

Example which does not work:

Thanks for your help.

Thorsten

pn <-  '39R5238';

library(RODBC);
odbcobj <-
odbcConnect("SQUIT21C",uid="muehge",pwd="xxx");
sql <- "select
u.unitid,
from test
where part in ('pn')
";
parameter <- sqlQuery(odbcobj,sql);
odbcClose(odbcobj);

hi, use paste:
# a string/number/date
pn <-  '39R5238';
sql <- paste("select u.unitid from test where part =",  pn)
                                                                        ^^

# an array of strings/numbers/dates
pn=c(1,2,3,4)
sql <- paste("select u.unitid from test where part in (", 
paste(pn,
collapse=','), ")")
                                                                        ^^
regards soren
obs the use of '=' and in

----- Original Message ----- 
From: "Thorsten Muehge" <MUEHGE at de.ibm.com>
To: <r-help at stat.math.ethz.ch>
Sent: Thursday, October 05, 2006 9:35 AM
Subject: [R] Variables in RODBC environment

>
> Hello Experts,
> how can I use variables in the RODBC environment.
>
> Example which does not work:
>
> Thanks for your help.
>
> Thorsten
>
> pn <-  '39R5238';
>
> library(RODBC);
> odbcobj <-
odbcConnect("SQUIT21C",uid="muehge",pwd="xxx");
> sql <- "select
> u.unitid,
> from test
> where part in ('pn')
> ";
> parameter <- sqlQuery(odbcobj,sql);
> odbcClose(odbcobj);
>
> ______________________________________________
> R-help at stat.math.ethz.ch 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.

> how can I use variables in the RODBC environment.
> 
> Example which does not work:
> 
> Thanks for your help.
> 
> Thorsten
> 
> pn <-  '39R5238';
> 
> library(RODBC);
> odbcobj <-
odbcConnect("SQUIT21C",uid="muehge",pwd="xxx");
> sql <- "select
> u.unitid,
> from test
> where part in ('pn')
> ";
> parameter <- sqlQuery(odbcobj,sql);
> odbcClose(odbcobj);
> 
You can "compose" your query simply using paste.

In your example it would be

pn <-  '39R5238';

sql <- paste("select
u.unitid,
from test
where part in ('", 
pn, 
"')");

or, to avoid problems with the newline character:

sql <- paste("select",
"u.unitid,",
"from test",
"where part in ('", 
pn, 
"')");

In this case you'd have:
> sql
[1] "select u.unitid, from test where part in (' 39R5238 ')"

It's clear that, if the spaces in (' 39R5238 ') are a problem for
you ,
you can use 

sql <- paste("select ",
"u.unitid, ",
"from test ",
"where part in ('", 
pn, 
"')", sep = "");

which lends to 
> sql
[1] "select u.unitid, from test where part in ('39R5238')"

HTH

  Sandro Bonfigli