I'm trying to analyse information from a large database using R. I have some problems with quotes during the generation of strings.
Setting: I extract data from a lot of sampling location (chemical data on water quality)
and for each location I have a date of sampling and a value. I want to put everything in a list such that for each sampling point (say OS1) I have two columns (date, time). I use the function sqlQuery from the RODBC package.
Here is an example that works:
OxygenMeasurements$OS1 <- sqlQuery(channel, "SELECT saDateTime, sdValue FROM dbo_Sample WHERE (paID = 213) AND (stCode ='OS1');")
To keep the code "elegant" and, above all, short I try to regenerate the same
function call with a different query statement for each sampling point by using
the paste() function.
(1) sqlquery <- paste("SELECT saDateTime, sdValue FROM dbo_Sample WHERE (paID = 213) AND (stCode ='" , paste(stations$stCode, sep=",") , "');" , sep="")
This gives a vector with the sql syntax. The following line generates the whole sqlQuery-statement.
This is the result for the first two elements of 'queries':
"OxygenMeasurements$OS1 <- sqlQuery(channel, \"SELECT saDateTime, sdValue FROM dbo_Sample WHERE (paID = 213) AND (stCode ='OS1'); \")"
"OxygenMeasurements$OS2 <- sqlQuery(channel, \"SELECT saDateTime, sdValue FROM dbo_Sample WHERE (paID = 213) AND (stCode ='OS2'); \")"
If these are evaluated ( eval(queries)) it should fill the datastructure "OxygenMeasurements" with the desired data.
The query statement containts a string and is a string in itself. In addition elements of 'queries' are strings. R doesn't seem to be able to cope with this, since it generates \" on locations where a " has to be put. Is there a way to let the paste function generate
a string with " in it without the \ ?
In a way there are 3 nested strings and R can only cope with 2 levels (by using ' and ") in my view.
Does anyone know how to put " IN the string instead of \"?
Thanks for the feedback.
Win a BlackBerry device from O2 with Yahoo!. Enter now.
[[alternative HTML version deleted]]