Convert SQL Server Data to Json with R

Question

I want to select 2 related tables ( invoice , invoice_detail ) from sql server - and nest the invoice_detail data in each record - outputting in json format ( like the format at the bottom of this page ) using R ( I can't use SQL Server because it is an older version - and I need to do some other things with R as well) .

It would seem that I need to use packages RODBC and rjson or similar.

library(rjson)
library(RODBC, quietly = TRUE)
sql.conn.string <- "Driver={SQL Server};Server=server;Uid=username;Pwd=password;"
sqlOdbc = odbcDriverConnect(sql.conn.string)
orders <- sqlFetch(sqlOdbc, "orders", max = 20)
toJSON(orders)
odbcClose(sqlOdbc)

Gives the Error: Error in toJSON(orders) : unable to convert R type 24 to JSON . I'm a little bit lost. Is there another package / method - or some interim conversion to make this happen?

Format needed:

{
"invoiceID": "970e858b-416f-4ed3-b244-3e32b6baa3a1",
"invoiceNumber": "100129115",
"date": "2017-06-12T12:00:00Z",
"lineItems": [{
        "description": "Product 1",
    "quantity": 1.0000,
    "unitAmount": 0.0000,
    "itemCode": "lmAM001ESC",
    "taxAmount": 0.00,
    "lineAmount": 0.00,
    "discountRate": 0.00,
    "tracking": [],
    "lineItemID": "e044a3a9-31d6-4619-a1c4-d50c2565abda"
},
{
    "description": "Product 2",
    "quantity": 1.0000,
    "unitAmount": 0.0000,
    "itemCode": "lmAD050HDC",
    "taxAmount": 0.00,
    "lineAmount": 0.00,
    "discountRate": 0.00,
    "tracking": [],
    "lineItemID": "835be120-1362-4b8a-b9b2-2b3cd2c69d9c"
}]
}

Looks like your invoice number might be leading to an int64 conversion issue, like here: https://stackoverflow.com/questions/28949729/ability-to-json-serialize-and-deserialize-int64-with-precision-in-r — David Klotz, Nov 07 '17 at 01:41

Convert SQL Server Data to Json with R

0 Answers0