R DBI, ODBC and SQL Server data types

In order to conduct the test, we need to connect to an SQL Server database:

library(odbc   )

sqlServer = dbConnect(odbc(),
            driver   ='SQL Server' ,
            server   = Sys.getenv('COMPUTERNAME'),
            database ='tq84_db'
);

We then create a table with hopefully some interesting data types:

if (dbExistsTable(sqlServer, 'r_dbi_test')) DBI::dbExecute(sqlServer, 'drop table r_dbi_test');

DBI::dbExecute (sqlServer, "
create table r_dbi_test (
   col_integer       integer    ,
   col_float         float      ,
   col_real          real       ,
   col_varchar       varchar(10),
   col_nvarchar     nvarchar(10),
   col_date          date       ,
   col_datetime      datetime   ,
   col_datetime2     datetime2  ,
   col_time          time
)
");

The table is filled with one record:

DBI::dbExecute(sqlServer, "
insert into r_dbi_test values (
   42                            ,  -- integer
   42.42                         ,  -- float
   42.42                         ,  -- real
  'René'                         ,  -- varchar
 N'René'                         ,  -- nvarchar
  '2005-05-25'                   ,  -- date
  '2006-06-26T06:07:08.123'      ,  -- datetime
  '2007-07-27T07:08:09.123'      ,  -- datetime2
  '09:10:11'                        -- time
)
");

src_data <- dbSendQuery(sqlServer, 'select * from r_dbi_test');

Although dbColumnInfo is supposed to return data types (column type) as character, they are returned as numbers.

Also, the data types nvarchar, date, datetime2 and time are not distinguished, their value is -9.

print(dbColumnInfo(src_data));
#
#              name type
#   1   col_integer    4
#   2     col_float    6
#   3      col_real    7
#   4   col_varchar   12
#   5  col_nvarchar   -9
#   6      col_date   -9
#   7  col_datetime   93
#   8 col_datetime2   -9
#   9      col_time   -9

We now iterate over the result set (the one record) and use str to print the data types and values of the individual columns:

while (! dbHasCompleted(src_data)) {
   src_rec <- dbFetch(src_data, 1); 
   print(str(src_rec));
}
#
#   'data.frame':	1 obs. of  9 variables:
#    $ col_integer  : int 42
#    $ col_float    : num 42.4
#    $ col_real     : num 42.4
#    $ col_varchar  : chr "Ren<e9>"
#    $ col_nvarchar : chr "René"
#    $ col_date     : chr "2005-05-25"
#    $ col_datetime : POSIXct, format: "2006-06-26 06:07:08"
#    $ col_datetime2: chr "2007-07-27 07:08:09.1230000"
#    $ col_time     : chr "09:10:11.0000000"

dbClearResult(src_data)
dbDisconne(sqlServer)