Implement different data types in fetch and dbWriteTable

[imanuelcostigan]

Currently the fetch method (and Java backend) only supports encoding fields as numeric or character vectors. I'm working around this in RSQLServer by using RJDBC's fetch method but then coercing fields to their corresponding R type.

It would be great if this could be expanded to cover integer, Date, POSIXct, logical and raw vectors. Has this been considered in the past? And would this be a possibility in the future? If so, I am happy to contribute to the solution.

It would also be great if this more granular type handling be reflected in dbWriteTable / dbDataType.

[imanuelcostigan]

Possibly related to #8

[maxmoro]

Looking forward for this update 👍

[daveloyall]

The author (or somebody) wrote this on the RJDBC homepage:

Type-handling is a rather complex issue, especially with JDBC as different databases support different data types. RJDBC attempts to simplify this issue by internally converting all data types to either character or numeric values. When retrieving results, all known numeric types are converted to R's numeric representation and all other types are treated as characters. When assigning parameters in parametrized queries, numeric, integer and character are the types used. Convenience methods like dbReadTable and dbWriteTable can only use the most basic SQL types, because they don't know what DBMS will be used. Therefore dbWriteTable uses only INTEGER, DOUBLE PRECISION or VARCHAR(255) to create the table. For all other types you'll have to use DBML statements directly.

So, now I know why I can't use scale_x_datetime() in a ggplot2 plot.

Damn... If I switch to odbc for R, I'll lose the cross-platform nature of RJDBC. (It doesn't matter that ODBC is supposedly available on Linux; I never got it working for SQL Server...)

[daveloyall]

For what it is worth, I was able to solve the specific problem I was having with code something like this:

resultset <- dbGetQuery(conn,query)
resultset$dayhour <- as.POSIXct(resultset$dayhour)

[s-u]

1c6399c adds support for (custom) conversions from the DBMS.