-1

I have a matrix called "final_set" which gets created in my R code. Using some of the columns from this final set, I want to create a database table. I'm using the RJDBC package to make this happen. However, for some reason its not recognizing "final_set" . I know my connection is okay because I've tried out other sql queries using that. Could anyone help me with this please?

 SqlStr = paste("SELECT",
                 S_Vars, ",",     #Global Variable 
                 final_set$pub_idx, ",",
                 final_set$qty_idx
                 into dbo.temp1
                 "from",
                 final_set,
                 sep = ""
               )

  dbSendUpdate(conn,SqlStr)

Error: Error in paste(**): object final_set not found

Cyang
  • 379
  • 1
  • 8
  • 18
  • Your error is in `paste`, not in any RJDBC function. (Sounds like you never defined `final_set`._ Rather than blindly passing `SqlStr` to `dbSendUpdate`, can you inspect `SqlStr` to see if is properly created? What do you see when you enter `final_set` in the console? When you enter `SqlStr` in the console, do you see the query you expect? – Gregor Thomas May 08 '14 at 20:32
  • I can see final_set when I enter that into the console. Sqlstr is not getting created so I cant see that – Cyang May 08 '14 at 20:46
  • Do I have the syntax correct though? Like you would use the $ sign to grab the column from the matrix, correct? – Cyang May 08 '14 at 20:49
  • @Prachi $ does not work with named matrices. It only works with data.frames and lists. Plus you're missing quotes around the line "into dbp.temp" and an ending comman for that line – MrFlick May 08 '14 at 21:04
  • Instead of pasting things together not knowing if they work, you can check each piece in your console. Put `final_set$pub_idx` in your console by itself, and you'll see if it works or not. (Then try `final_set[, "pub_idx"]`). – Gregor Thomas May 08 '14 at 21:07
  • How do I get the column name from final_set instead of the column elements? Eg, if pub_idx has values 1:400, its giving me those values instead of the string that I want "pub_idx" – Cyang May 08 '14 at 21:19
  • If you already know the name is `"pub_idx"`, then you don't need to get the name from the matrix. Just use `"pub_idx"`, in quotes. – Gregor Thomas May 08 '14 at 23:42

1 Answers1

0

I think you want your query to be:

SqlStr = paste("SELECT",
                S_Vars,     #Global Variable 
                ", pub_idx, qty_idx into dbo.temp1 from final_set")

R objects that are not strings shouldn't go in your query as R objects, as their values (not their names) will be coerced to strings.

I'm still a little confused as to your goal. If you're trying to create a table out of an R object, (i.e., final_set is not already a table in your database), then the database won't be able to select columns from final_set. Rather, you should use a function like dbWriteTable. If I'm correct about that, then I think what you're looking for is

dbWriteTable(conn, "temp1", final_set[, c(S_Vars, "pub_idx", "qty_idx")]
Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
  • What I want to do is create a subset of the final_set. That is, if iris were my final_set, I want to get a table with only the columns Petal.Length and Species. 2nd question: How do I aggregate values? Eg, I want to get the sum of the petal length by Species. So like a "group by" statement in sql, what is the parallel in R? – Cyang May 09 '14 at 14:09
  • That's a whole different question, with lots of answers. `aggregate` in base will work, `group_by` together with `summarize` in `dplyr` will work... [this](http://stackoverflow.com/q/3505701/903061) should get you started. – Gregor Thomas May 11 '14 at 20:55