SqlSave pads numeric / integers with space

Question

I have some data that I am trying to load into a table in a database. For instance:

df <- data.frame(name = c("Fred", "George", "David"), data = c(10, 100, 1000)) 

When I use the RODBC::sqlSave (with fast = FALSE to insert one row at a time), it fills in numeric or integer values ​​with spaces. Thus, in the database, the entries in the data column will be " 10", " 100", "1000" . For example, if I downloaded the above data framework and then requested that data using the following

 tmp = RODBC::sqlQuery(ch, query = "SELECT * FROM My_Tbl") 

then the output will look something like this:

 > str(tmp) 'data.frame': 3 obs. of 2 variables: $ name : chr "Fred" "George" "David" $ data : chr " 10" " 100" "1000" 

The database data type for the data column is of type varchar(10) , because data sometimes has a leading letter. In case I work, however, there are only numbers.

My question is why does the sqlSave function populate numeric data and is there anything I can do to stop it?

NOTE. I know this is the sqlSave problem causing the problem, because when I use verbose = TRUE , I can see the output of the INSERT and really have padded data. In addition, this is compiled data for the purposes of the example.

Answer 1

Using sqlSave:

If fast = FALSE, all data is sent as character strings. If fast = TRUE, integer and double vectors are sent as types SQL_C_SLONG and SQL_C_DOUBLE respectively.

So try without the fast=FALSE option. You can also try the typeInfo option.