-4

I asked this question yesterday and it was answered but I don't think they really understood my question. I need to extract data from a database. But the user provides the list of variables. I need my code to loop through each requested variable, pull the data associated with it and drop it in a data.frame, ready for analysis. 

f.extractVariables<-
  structure(function 
(dbPath,dbName,table,variables
   ){
# LOAD LIBRARIES
require(RODBC)
require(xlsx)

 setwd(dbPath)
 db <- odbcConnectAccess2007(dbName)

 #This was my idea on how to loop through the variable list but it won't run
 for (i in 1:length(variables))
  {
 dataCollection <- sqlQuery(db, 'SELECT table.variables[[i]]
                    FROM table;')
 }

#This piece was the solution I was given which runs but all it does is parrot back 
#the variable list it doesn't retrieve anything.
 variables=paste(variables,collapse=",")
 query<-paste(paste("SELECT",variables),
 "FROM table",sep='\n')
 cat(query)

odbcClose(db)
 }
 )
#And the user provided input looks something like this. I can change the way the
 #variable list comes in to make it easier.
            dbPath = 'z:/sites/'
    dbName = 'oysterSites.accdb'
    table  = 'tblDataSiteOysterSamplingPlan'
    variables= 'nwLon,nwLat,neLon,neLat'

    f.extractVariables(dbPath,dbName,table,variables)
user3009978
  • 1
  • 1
  • 1
    and also this time nobody will understand your question because you fail to give a simple and to the point reproducible example of your problem - which by the way lacks a "question mark" ... – Raffael Nov 26 '13 at 14:18
  • " I need my code to loop through each requested variable, pull the data associated with it and drop it in a data.frame, ready for analysis." Then inside the code I mention "This is how I think it should be but it wont run" and also "This is what was given to me and it runs but only parrots back variables" This is the question with or without a question mark. Maybe I could add the word "How?" but I think smart people get it without that. – user3009978 Nov 26 '13 at 14:34
  • I see nothing meaningfully different between this and your original question other than some clarifications (of dubious worth), so I'm voting to close as a duplicate. – joran Nov 27 '13 at 00:48
  • It's **you** that don not understand (very simple stuff). I just checked the other question and you have been told already that the problem is in the `for` loop, written against any R syntax. Which means you **already** have the solution, but you don't understand it... – Michele Nov 27 '13 at 08:42
  • Yes. I was told to work on the for loop. Brilliant help, guys. I tell you the problem is in my for loop and you tell me the problem is in my for loop and to go work on it. I hope they aren't paying you. Just because I am new to coding doesn't give you the right to be jerks. – user3009978 Nov 27 '13 at 14:15
  • @user3009978 Are you trolling???? I **told** you how to fix it. You were **not** looping trough anything, cause the body of the for loop was **not** changing in each cycle (but you don't actually need it). **STEP1** change that ugly `'nwLon,nwLat,neLon,neLat'` in a **real** `R` vector and **STEP2** use the method agstudy gave you in the other question. it MUST WORK – Michele Nov 28 '13 at 08:07

1 Answers1

0

In which language you think you are coding? This is not .NET...

Remove this

 for (i in 1:length(variables))
  {
 dataCollection <- sqlQuery(db, 'SELECT table.variables[[i]]
                    FROM table;')
 }

in which 'SELECT table.variables[[i]] FROM table;' will always be the SAME for all the cycles of the loop, amd change with (at least my guess of what you were trying to do)

 for (i in 1:length(variables))
  {
 dataCollection <- sqlQuery(db, paste0('SELECT table.variables[[', i, ']]
                    FROM table;'))
 }

You say that the above does not work. No point to keep reviewing your code, fix the above and then come back

Michele
  • 8,563
  • 6
  • 45
  • 72
  • In R. Sorry but re-posting the question, I forgot to be specific. – user3009978 Nov 26 '13 at 14:40
  • @user3009978 well, then you should document a bit more before coming here, you think `'nwLon,nwLat,neLon,neLat'` is a vector?? I believe you mean `c('nwLon','nwLat','neLon','neLat')`. it's very simple stuff, **first** chapter of any R book – Michele Nov 26 '13 at 16:39
  • I never said it was a vector. I said I don't know the best way to get the information from a user. And I thought this site was for people who needed help with their code not for uptight experts. I have "The R Book" and I read it. What gives you the right to be so smug anyhow? I am new to coding and every person who answers my questions acts like I don't belong here because I have less experience. I think you need to remember what it is like to be new at something and need support. You guys give this site a bad reputation as a place for coding jerks to harass people. – user3009978 Nov 27 '13 at 14:12
  • @user3009978 because you provide very few to make us understand. And that `for` makes me thinking you may have the book but you did't read it – Michele Nov 28 '13 at 08:00