R help - Aug 2002 - variables inside an Rdi-select statement?!

Dear R-users,

I would like to use an environment variable inside an SELECT-statement
from R to a postgres-db, but it will not work and so I need some advice
from you.
<code>
ezg <- Sys.getenv("EZG")
connect <- dbConnect.PgSQL(host="localhost",
user="username",
dbname="dbname")
query <-  dbSendQuery(connect, "SELECT ezg.s FROM ezg  WHERE
ezg.s!='12'")
</code>
the error is, that SELECT searches for the relation ezg, but it should
use value stored in ezg.

Any suggestions?

thanks in advance

stephan


-- 
Linux: the operating system with a CLUE...
         Command Line User Environment.
--------------------------------------
Stephan Holl
GnuPG Key-ID: 11946A09
ICQ# 117277975 
--------------------------------------
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
Url :
https://stat.ethz.ch/pipermail/r-help/attachments/20020802/90badc64/attachment.bin

On Fri, 2 Aug 2002, Stephan Holl wrote:
> Dear R-users,
>
> I would like to use an environment variable inside an SELECT-statement
> from R to a postgres-db, but it will not work and so I need some advice
> from you.
> <code>
> ezg <- Sys.getenv("EZG")
> connect <- dbConnect.PgSQL(host="localhost",
user="username",
> dbname="dbname")
> query <-  dbSendQuery(connect, "SELECT ezg.s FROM ezg  WHERE
> ezg.s!='12'")
> </code>
> the error is, that SELECT searches for the relation ezg, but it should
> use value stored in ezg.
In short, it did what you asked it to, which is not an error.

Use paste() to assemble the string you want to pass.  Nothing you did
tells R that `ezg' in the last line is a reference to an R variable. 
I'd
give you an example except I don't see the point of your SQL.  I think you
may mean

paste("SELECT s FROM", ezg, "WHERE s!='12'")


-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272860 (secr)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at
stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._

hi,

use paste() to build your query-string.

Stephan Holl wrote:
> 
> Dear R-users,
> 
> I would like to use an environment variable inside an SELECT-statement
> from R to a postgres-db, but it will not work and so I need some advice
> from you.
> <code>
> ezg <- Sys.getenv("EZG")
> connect <- dbConnect.PgSQL(host="localhost",
user="username",
> dbname="dbname")
> query <-  dbSendQuery(connect, "SELECT ezg.s FROM ezg  WHERE
> ezg.s!='12'")
> </code>
> the error is, that SELECT searches for the relation ezg, but it should
> use value stored in ezg.
> 
> Any suggestions?
> 
> thanks in advance
> 
> stephan
> 
> --
> Linux: the operating system with a CLUE...
>          Command Line User Environment.
> --------------------------------------
> Stephan Holl
> GnuPG Key-ID: 11946A09
> ICQ# 117277975
> --------------------------------------
> 
>   ------------------------------------------------------------------------
>    Part 1.2Type: application/pgp-signature
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at
stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._