Error - Potentially on conversion of fields while fetching.

[jfontestad]

I am assuming that it is trying to convert the fetched fields into another data-type, rather than the data type per the database. In the code below, paidAmt is of data type decimal(19,2).

R Code:

selectQry <- "SELECT TOP 10 claimType, transType, paidAmt FROM tmpRx_01"
res <- dbSendQuery(conn, selectQry)
dsResults <- dbFetch(res)
dbClearResult(res)
dbDisconnect(conn)

Error:

Error in (function ()  : 
  clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL
Error in clrCall(res.Dict, "get_Item", Cnames[i]) : 
  Type:    RDotNet.EvaluationException
Message: Error in (function ()  : 
  clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL

Method:  RDotNet.ProtectedPointer evaluateCall(IntPtr)
Stack trace:
   at RDotNet.Function.evaluateCall(IntPtr call)
   at RDotNet.Function.createCallAndEvaluate(IntPtr argument)
   at Rclr.RDotNetDataConverter.CreateClrObj(Object obj) in c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 603
   at Rclr.RDotNetDataConverter.TryConvertToSexp(Object obj) in c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 320
   at Rclr.RDotNetDataConverter.ConvertToSexp(Object obj) in c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 369
   at Rclr.RDotNetDataConverter.ConvertAll(Object[] objects, Func`2 converter) in c:\Users\per202\AppData\Loca

[jfontestad]

When the paidAmt field is removed from the query, then the results are delivered as expected.

[jfontestad]

I changed the paidAmt field to datatype money, as well as numeric, and still am getting the same error.

[ruaridhw]

This has been covered in #22 and #26. The underlying package rClr doesn't support these datatypes.

This is easily fixed with a CAST statement otherwise see the workaround provided by rsqlserver

selectQry <- "SELECT TOP 10 claimType, transType, CAST(paidAmt AS float) FROM tmpRx_01"

[ghost]

Agreed that casting would fix this. There are 3 solutions besides a manual cast 1) automatic conversion with 3 small tweaks to rsqlserver 2) executing a stored proc that coverts decimal, numeric, and money columns automatically 3) rclr

I've tested the first two successfully for decimal and money, and emailed one of the contributors to see how to get this reviewed for approval and released.

I also am trying to figure out how to modify rClr as well, and work with that package for potentially adding other R functionality in the future.

[ruaridhw]

If you want to get anything added to this package just fork the package, create a new branch and submit a pull request :)

Would be great to have a better solution to this!

[ghost]

Thanks, I just forked the package, uploaded a small tweak to SqlDataHelper.cs, and submitted a pull request.

Since this is my first time forking and submitting a pull request, please let me know if I need to do anything else for the workaround to be included in the project.

On Thu, Jan 4, 2018 at 7:44 PM, Ruaridh Williamson <notifications@github.com

wrote:

If you want to get anything added to this package just fork the package, create a new branch and submit a pull request https://opensource.guide/how-to-contribute/#opening-a-pull-request :)

Would be great to have a better solution to this!

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/agstudy/rsqlserver/issues/32#issuecomment-355465874, or mute the thread https://github.com/notifications/unsubscribe-auth/AhQViogQUBBXTN0xwVpxa735D3cFdpZpks5tHZqNgaJpZM4QGNq4 .