dBWriteTable无法将NULL写入SQL Server表 [英] dBWriteTable cannot write NULLs into SQL Server table

问题描述

问题

无法将data.frame中的NA值插入数据库表

Cannot insert NA values from a data.frame to a DB Table

STEPS

1. 从SQL Server中将表读取到R data.frame中.表是带一些NULL的十进制. data.frame是带有NA的数字​​.

1. Read table from SQL server into R data.frame. Table is decimal with some NULL. data.frame is numeric with some NA.

dBWriteTable引发以下错误

.local(conn，statement，...)中的错误:在dbSendUpdate中执行JDBC更新查询失败(传入的表格格式数据流(TDS)远程过程调用(RPC)协议流不正确.参数57(") ;):提供的值不是float型数据类型的有效实例.请检查源数据中是否有无效值.一个无效值的示例是标度大于精度的数字类型数据.)

Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate (The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 57 (""): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.)

我用0覆盖了NA，例如dataset$column[is.na(dataset$column)] = 0

dBWriteTable成功写入数据库

R详细信息

> sessionInfo()
R version 3.4.4 (2018-03-15)
Platform: x86_64-redhat-linux-gnu (64-bit)
Running under: Red Hat Enterprise Linux Server 7.5 (Maipo)

RJDBC_0.2-7.1       rJava_0.9-10       DBI_1.0.0 

推荐答案

之所以会出现此问题，是因为R中有三个(可能更多)不同的值，这些值没有具体定义.它是"Inf"，"NaN"或"NA".现在，dbWriteTable可以通过将NA移到SQL时将它们转换为"NULL"来处理NA.但是，无法识别"NaN"和"Inf"，从而出现检查源数据中的无效值"错误.解决方法如下:

The problem occurs because there are three (maybe more) different values in R for values that are not concretely defined. It's either "Inf" , "NaN" or "NA". Now dbWriteTable can handle NA by converting these to "NULL" when moving it to SQL. However "NaN" and "Inf" are not recognized thus giving the "Check the source data for invalid values" error. The fix is as following:

假设这是您要写入SQL的表:

Suppose this is your table that you want to write to SQL:

Tablename: "df"
USER quality
1    Inf
2    NaN
3    0.3

您要做的第一件事是将所有"NaN"都转换为"NA"，因为dbWriteTable可以识别这些内容.只需通过以下方式即可完成:

The first thing you want to do is convert all "NaN" to "NA" because dbWriteTable can recognize these. This can be done simply by:

df[is.na(df)] <- NA

然后，您仍然拥有"Inf"值.不幸的是，我还没有找到一种简单的方法可以整行浏览整个表格.但是请检查哪些列具有"Inf"，并按以下步骤逐一进行操作:

Then you still have your "Inf" values. Unfortunately I haven't found a easy way to go through the entire table in one line. But check which columns have "Inf" and do them one by one as following:

df$quality[is.nan(df$quality)] <- NA

如果您的表被清理，则不应给出这些错误.这是另一个使用dbWriteTable只是为了使事情更清楚的示例:

If your table is cleaned up it shouldn't give these errors. Here is another example of how to use dbWriteTable just to make things more clear:

dbWriteTable(ODBCconnectionname, DBMStablename, YourRtablename,
field.types = c(USER="integer",
quality="float(53)",
))

在此页面上检查所需的数据类型: https://www.w3schools.com/sql/sql_datatypes.asp

Check which data types you need on this page: https://www.w3schools.com/sql/sql_datatypes.asp

如果您需要更多信息，请PM PM.

If you need more info just PM me.

这篇关于dBWriteTable无法将NULL写入SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！