0
data <- sqlQuery(connection, "SELECT col1, col2, col3 FROM table1")

d <- data.frame(data, stringsAsFactors = FALSE)

or

transform(d, col1 = as.character(col1), stringsAsFactors = FALSE)

d in this cases still has factor type of columns.

str(d)

'd': 1943 obs. of 4 variables:
$ col1: Factor w/ 5 levels ....

But when I first state;

options(stringsAsFactors = FALSE)

it works, but it sets it globally. Why do inside-statements has no effect?

burakongun
  • 241
  • 4
  • 6
  • 17
  • `data` itself was a `data.frame` with the `col1` as `factor` i suppose. add separately the `col1` variable to `d` with `stringsAsFactors = FALSE` – joel.wilson Dec 28 '16 at 08:05
  • What is `data`? Please consult the examples [here](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) in order to improve your question. I'm pretty sure that while creating a reproducible example you will understand that this is highly unlikely that *stringsAsFactors in data.frame() does not work* – David Arenburg Dec 28 '16 at 08:15
  • You should carefully read `?data.frame`. Every single argument is coerced to a `data.frame`. So, if any of the argument is already a `data.frame`, nothing changes. New character column are coerced according to `stringsAsFactors`. For the `transform` part, remember that it doesn't change function arguments *by reference*, so you should assign the result back: `d<-transform(d,col1=as.character(col1))` (note that the `stringsAsFactors` argument here is not necessary). – nicola Dec 28 '16 at 08:20
  • `data` is a query result from an ODBC channel. I don't know if it's the right way to find out but `typeof(data)` gives `"list"` @DavidArenburg Thanks for the link. I'll read. @nicola I forgot to assign the result of `transform()`. Rookie mistake :) – burakongun Dec 28 '16 at 08:30
  • Very likely `data` is already a `data.frame`. Try `class(data)` or `is.data.frame(data)`. Remember that `data.frame`s are `list` objects. – nicola Dec 28 '16 at 08:33
  • 2
    A `data.frame` is a list. Why are you wrapping `data` into `data.frame`? You can just specify `stringsAsFactors = FALSE` within `sqlQuery` or use `as.is`. Try `data <- sqlQuery(connection, "SELECT col1, col2, col3 FROM table1", as.is = TRUE) ; str(data)` – David Arenburg Dec 28 '16 at 08:48
  • It would have been helpful if you would have included the call `library(RODBC)` in your code snippet as `sqlQuery` is no function of base R -Thank you. – Uwe Dec 28 '16 at 09:25

1 Answers1

3

Elaborating the comment of David Ahrenburg, please try

data <- sqlQuery(connection, "SELECT col1, col2, col3 FROM table1", stringsAsFactors = FALSE)

The help page ?sqlQuery of package RODBC says:

sqlQuery is the workhorse function of RODBC. It sends the SQL statement query to the server, [...] and retrieves (some or all of) the results via sqlGetResults.

sqlQuery has a ... parameter for additional arguments to be passed to sqlGetResults.

Looking at the usage section of sqlGetResults (on the same help page) you will find that there is an argument stringsAsFactors which uses the following default:

stringsAsFactors = default.stringsAsFactors()

Uwe
  • 41,420
  • 11
  • 90
  • 134