3

I'm in the process of learning R, to wave SAS goodbye, I'm still new to this and I somehow have difficulties finding exactly what I'm looking for.

But for this specific case, I read: Pass R variable to RODBC's sqlQuery? and made it work for myself, as long as I'm only inserting one variable in the destination table.

Here is my code:

library(RODBC)
channel <- odbcConnect("test")
b <- sqlQuery(channel,
  "select top 1 Noinscr
   FROM table
   where PrixVente > 100
   order by datevente desc")

sqlQuery(channel,
   paste("insert into TestTable (UniqueID) Values (",b,")", sep = "")

When I replace the top 1 by any other number, let's say top 2, and run the exact same code, I get the following errors:

[1] "42000 195 [Microsoft][SQL Server Native Client 10.0][SQL Server]
    'c' is not a recognized built-in function name."      
[2] "[RODBC] ERROR: Could not SQLExecDirect 
    'insert into TestTable  (UniqueID) Values (c(8535735, 8449336))'"

I understand that it is because there is an extra c that is generated, I assume for column when I give the command: paste(b).

So how can I get "8535735, 8449336" instead of "c(8535735, 8449336)" when using paste(b)? Or is there another way to do this?

Community
  • 1
  • 1
Felixthecat
  • 89
  • 2
  • 6

2 Answers2

3

Look into the collapse argument in the paste() documentation. Try replacing b with paste(b, collapse = ", "), as shown below.

Edit As Joshua points out, sqlQuery returns a data.frame, not a vector. So, instead of paste(b, collapse = ", "), you could use paste(b[[1]], collapse = ", ").

library(RODBC)
channel <- odbcConnect("test")
b <- sqlQuery(channel,
  "select top 1 Noinscr
   FROM table
   where PrixVente > 100
   order by datevente desc")

sqlQuery(channel,
   ## note paste(b[[1]], collapse = ", ") in line below
   paste("insert into TestTable (UniqueID) Values (", paste(b[[1]], collapse = ", "),")", sep = "")
jthetzel
  • 3,603
  • 3
  • 25
  • 38
  • And just FYI here is a somewhat more elaborate version, as I did not know the collapse parameter (and was too late answering): `Reduce(function(u, v) paste(u, ", ", v, sep=""), b)` – ROLO Nov 14 '11 at 21:09
  • Added the [[1]] and it works like a charm! the c( ) is gone! – Felixthecat Nov 15 '11 at 13:58
2

Assuming b looks like this:

b <- data.frame(Noinscr=c("8535735", "8449336"))

Then you only need a couple steps:

# in case Noinscr is a factor
b$Noinscr <- as.character(b$Noinscr)
# convert the vector into a single string
# NOTE that I subset to get the vector, since b is a data.frame
B <- paste(b$Noinscr, collapse=",")
# create your query
paste("insert into TestTable (UniqueID) Values (",B,")", sep="")
# [1] "insert into TestTable (UniqueID) Values (8535735,8449336)"

You got odd results because sqlQuery returns a data.frame, not a vector. As you learned, using paste on a data.frame (or any list) can provide weird results because paste must return a character vector.

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
  • I still run into an error, this time, I assume its because I transformed it into text and trying to put back text in a sql field that is a float. now I get the following error: cannot coerce type 'closure' to vector of type 'character' – Felixthecat Nov 14 '11 at 21:17
  • @Felixthecat: I doubt that's it because `paste` is going to convert `b$Noinscr` to a character vector even if it were a numeric or integer vector. The error most likely means you're trying to convert a function to a character vector. Check your syntax carefully. – Joshua Ulrich Nov 14 '11 at 21:46
  • @Felixthecat: To troubleshoot, could you post the output of `print(b)` in your question? – jthetzel Nov 14 '11 at 22:09