R help - Jun 2010 - Can RMySQL be used for a paramterized query?

I have not found anything about this except the following from the DBI
documentation :

Bind variables: the interface is heavily biased towards queries, as
opposed
> to general
> purpose database development. In particular we made no attempt to define
> “bind
> variables”; this is a mechanism by which the contents of R/S objects are
> implicitly
> moved to the database during SQL execution. For instance, the following
> embedded SQL statement
> /* SQL */
> SELECT * from emp_table where emp_id = :sampleEmployee
> would take the vector sampleEmployee and iterate over each of its elements
> to get the result. Perhaps the DBI could at some point in the future
> implement
> this feature.
>
I can connect, and execute a SQL query such as "SELECT id FROM
my_table",
and display a frame with all the IDs from my_table.  But I need also to do
something like "SELECT * FROM my_other_table WHERE t1_id = x"  where
'x' is
one of the IDs returned by the first select statement.  Actually, I have to
do this in two contexts, one where the data are not ordered by time and one
where it is (and thus where I'd have to use TSMySQL to execute something
like "SELECT record_datetime,value FROM my_ts_table WHERE t2_id = x").

I'd like to embed this in a loop where I iterate over the IDs returned by
the first select, get the appropriate data from the second for each ID,
analyze that data and store results in another table in the DB, and then
proceed to the next ID in the list.  I suppose an alternative would be to
get all the data at once, but the resulting resultset would be huge, and I
don't (yet) know how to take a subset of the data in a frame based on a
given value in one ot the fields and analyze that.  Can you point me to an
example of how this is done, or do I have to use a mix of perl (to get the
data) and R (to do the analysis)?

Any insights on how to proceed would be appreciated.  Thanks.

Ted

	[[alternative HTML version deleted]]

I think you can do this:

ids <- dbGetQuery(conn, "SELECT id FROM my_table")
other_table <- dbGetQuery(conn, sprintf("SELECT * FROM my_other_table
WHERE
t1_id in (%s)", paste(ids, collapse = ",")))

On Wed, Jun 9, 2010 at 11:24 PM, Ted Byers <r.ted.byers@gmail.com> wrote:
> I have not found anything about this except the following from the DBI
> documentation :
>
> Bind variables: the interface is heavily biased towards queries, as opposed
> > to general
> > purpose database development. In particular we made no attempt to
define
> > “bind
> > variables”; this is a mechanism by which the contents of R/S objects
are
> > implicitly
> > moved to the database during SQL execution. For instance, the
following
> > embedded SQL statement
> > /* SQL */
> > SELECT * from emp_table where emp_id = :sampleEmployee
> > would take the vector sampleEmployee and iterate over each of its
> elements
> > to get the result. Perhaps the DBI could at some point in the future
> > implement
> > this feature.
> >
>
> I can connect, and execute a SQL query such as "SELECT id FROM
my_table",
> and display a frame with all the IDs from my_table.  But I need also to do
> something like "SELECT * FROM my_other_table WHERE t1_id = x" 
where 'x' is
> one of the IDs returned by the first select statement.  Actually, I have to
> do this in two contexts, one where the data are not ordered by time and one
> where it is (and thus where I'd have to use TSMySQL to execute
something
> like "SELECT record_datetime,value FROM my_ts_table WHERE t2_id =
x").
>
> I'd like to embed this in a loop where I iterate over the IDs returned
by
> the first select, get the appropriate data from the second for each ID,
> analyze that data and store results in another table in the DB, and then
> proceed to the next ID in the list.  I suppose an alternative would be to
> get all the data at once, but the resulting resultset would be huge, and I
> don't (yet) know how to take a subset of the data in a frame based on a
> given value in one ot the fields and analyze that.  Can you point me to an
> example of how this is done, or do I have to use a mix of perl (to get the
> data) and R (to do the analysis)?
>
> Any insights on how to proceed would be appreciated.  Thanks.
>
> Ted
>
>        [[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.
>
>

-- 
Henrique Dallazuanna
Curitiba-Paraná-Brasil
25° 25' 40" S 49° 16' 22" O

	[[alternative HTML version deleted]]