CODEWITHSUNDEEP
sql-serverrrodbc
Nadir Sidi
Nadir Sidi

Reputation: 1886

RODBCext: SQL 42000 402 Error when using sqlExecute()

I'm working on a Shiny app for updating entries maintained in a SQLServer2008 remote DB. I've been connecting to the DB using RODBC and I'm attempting to use parameterized queries through RODBCext to support mass updates of information.

I'm able to get the parameterized queries to work from my Windows 7, RStudio running R 3.2.3, but for some reasons when I try to run the same code from the linux machine running the same version of R and connecting with the same version of the driver, I get the following error:

Error in sqlExecute(Connection, data = dat) :
  42000 402 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The data types char and text are incompatible in the equal to operator.
42000 8180 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared.
[RODBCext] Error: SQLExecute failed
In addition: Warning messages:
1: In sqlExecute(Connection, data = dat) :
  42000 402 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The data types char and text are incompatible in the equal to operator.
2: In sqlExecute(Connection, data = dat) :
  42000 8180 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared.

Here is the simple example code that works properly on my windows machine, but not on the linux machine (I removed the connection string information):

library(RODBCext)

Connection <- odbcDriverConnect(paste('Driver=ODBC Driver 13 for SQL Server', 
'Server=<Server IP>', 'Port=<Port>', 'Database=<Database>', 'UID = <UserID>', 
'PWD=<Password>', sep = ';'))

dat <- data.frame(Node_ID = "999", NodeGUID = "AF213171-201B-489B-B648-F7D289B735B1")

query <- "UPDATE dbo.Nodes SET Node_ID = ? WHERE NodeGUID = ?"

sqlPrepare(Connection, query)

sqlExecute(Connection, data = dat)

In this example, the dataframe is created with the columns as factors. I've tried explicitly casting the columns as characters first, as this seemed to work for the users having trouble with dates, but that still results in the same SQL error. I've also tried casting the Node_ID as numeric to match the SQL table, and I get the same error. The columns in the Nodes table in SQL are defined as:

NodeGUID (PK, char(36), not null)
Node_ID (int, null)

I've tried combining the sqlPrepare and sqlExecute calls by supplying the query argument for sqlExecute, and from what I understand that's a trivial difference and it results in the same error.

I suspect there must be a difference in the drivers and how they implement whatever SQL calls sqlExecute() makes. I also suspect sqlExecute() must handle the data types, as my results don't change regardless of the column types.

Thank you for any help you can provide!

Upvotes: 1

Views: 1080

Answers (1)

Nadir Sidi
Nadir Sidi

Reputation: 1886

Thanks to everyone who took a look at my question.

One of the SQL Server folks at my job was able to solve the issue. They suggested explicitly casting the arguments in the SQL query written for sqlExecute(). Here's the code that works, note I know the GUID will always be 36 characters and I'm confident the rest of the arguments I use this query for will be less than 1000 when converted to strings:

my_query <- "UPDATE dbo.Nodes SET Node_ID = CAST(? As varchar(1000)) WHERE NodeGUID = CAST(? As varchar(36))"
sqlExecute(Connection, data = dat, query = my_query)

I'm guessing the driver for Windows is somehow handling the casting from text to varchar, but the linux driver does not.

I hope this helps others working with RODBCext. Thanks to Mateusz Zoltak and the team of contributors for a great package!

Upvotes: 2

Related Questions