sqlSave:將dataframe時間戳映射到SQL Server時間戳 - sqlSave: Mapping dataframe timestamps to SQL Server timestamps -开发者知识库

sqlSave:將dataframe時間戳映射到SQL Server時間戳 - sqlSave: Mapping dataframe timestamps to SQL Server timestamps -开发者知识库,第1张

I am trying to upload a data frame to a table in sql server using sqlSave(). This dataframe has a timestamp in it and I'd like to map the timestamp col to a datetime col in sqlserver.

我正在嘗試使用sqlSave()將數據幀上載到sql服務器中的表中。這個dataframe包含一個時間戳,我希望將時間戳col映射到sqlserver中的datetime col。

I am getting two problems.


1. It maps the data frame's timestamp to a float. 2. It creates a table, but no data is uploaded and I get an error.


Here's an example data frame, mdf:


mdf <- structure(list(run = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label = c("run_00", 
"run_01", "run_02", "run_03", "run_04"), class = "factor"), slot = structure(c(1L, 
1L, 1L, 1L, 1L, 1L), .Label = c("slot 3", "slot 4", "slot 5", 
"slot 6"), class = "factor"), timestamp = structure(c(1320774563, 
1320774624, 1320774686, 1320774747, 1320774809, 1320774871), class = c("POSIXct", 
"POSIXt"), tzone = ""), channel = structure(c(1L, 1L, 1L, 1L, 
1L, 1L), .Label = c("och01", "och02", "och09", "och10"), class = "factor"), 
    variable = structure(c(2L, 2L, 2L, 2L, 2L, 2L), .Label = c("num_blocks", 
    "num_collection", "num_corr_0", "num_corr_1", "num_uncorr_srow", 
    "post_fec_err_rate", "pre_fec_err_rate"), class = "factor"), 
    value = c(1, 62, 124, 185, 247, 309)), .Names = c("run", 
"slot", "timestamp", "channel", "variable", "value"), row.names = c(NA, 
6L), class = "data.frame")

> mdf
     run   slot           timestamp channel       variable value
1 run_00 slot 3 2011-11-08 12:49:23   och01 num_collection     1
2 run_00 slot 3 2011-11-08 12:50:24   och01 num_collection    62
3 run_00 slot 3 2011-11-08 12:51:26   och01 num_collection   124
4 run_00 slot 3 2011-11-08 12:52:27   och01 num_collection   185
5 run_00 slot 3 2011-11-08 12:53:29   och01 num_collection   247
6 run_00 slot 3 2011-11-08 12:54:31   och01 num_collection   309

Here's what happens when I try sqlSave to a sql server database...


> sqlSave(dbandle,mdf,tablename="mdf")
Error in sqlSave(dbandle, mdf, tablename = "mdf") : 
  [RODBC] Failed exec in Update
22018 0 [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Also, when I look at the data types of the table, I don't get "datetime" for the timestamp. It doesn't make sense to me why RODBC would map a POSIXct timetamp to anything other than datetime.

另外,當我查看表的數據類型時,我沒有得到時間戳的“datetime”。我不理解為什么RODBC會將一個POSIXct timetamp映射到datetime之外的任何東西。

[rownames] [varchar](255) NULL,
[run] [varchar](255) NULL,
[slot] [varchar](255) NULL,
[timestamp] [float] NULL,
[channel] [varchar](255) NULL,
[variable] [varchar](255) NULL,
[value] [float] NULL

How do I get around this?


1 个解决方案



Two options:


1) Lazy one: let the error occur, the table will be created, and change the column(s) to datetime manually in your database. It will work the next time.

1) Lazy one:讓錯誤發生,將創建表,並在數據庫中手動將列更改為datetime。下次還行。

2) Correct: use varTypes


Note that your problem can be stripped down by removing unnecessary stuff. As an aside, I probably would not use the column name timestamp in an sql server, because I have seen confusions because of the internal timestamp data type is totally different.


mdf = data.frame(timestamp=as.POSIXct(Sys.time()))

varTypes = c(timestamp="datetime")
channel = odbcConnect("test")