Skip to main content

Bulk Inserts

In addition to parcel by parcel inserts using the scanner it is possible to perform bulk inserts into GramChain.


The preferred method to do this is to format the data into a large Json string in the body of a “post” submission (or via a file) by utilizing the following API: /api/secured/GramChainBulkInsert

Which requires the following parameters:

  • JsonObject – This the Json object (see below)
  • languageid – e.g. “en”, determines potential error message language
  • entityid – if applicable / if data is to be separate from main data
  • callerid – The Account ID of the submitter

The JsonObject must have the following parameters which are nearly identical to the GramChainInsertViaScanner API parameters with exception of the image-url:

  • imageurl: this is the primary deviation from GramChainInsertViaScanner. Instead of requiring the Base64 serialization of a file – which would make the string huge - the public URL of the associated image file is required instead. The system will retrieve the image and convert it into a Base64 automatically as it processes each insert.
  • rfid: A parcel specific RFID is required. Data cannot be insert without an RFID as we need to track the parcel physically. Note that some RFIDs have a programmable “friendly” ID along with their original RFID code. As long as the RFID is scannable with the scanner these friendly IDs would be fine.
  • eventtypecode: assuming this is a new entry always use event “scan”
  • scannerid: must be custom assigned ahead of bulk insert as it will determine vault location and other details
  • itemquantity: self-explanatory, must be an integer
  • brandcode: must be one of the existing brandcodes, for a list of valid brandcodes view API\Dropdownvalues\BrandCodes
  • itemtypecode: must be one of the existing ItemtypeCodes, for a list of valid ItemtypeCodes view API\Dropdownvalues\ItemtypeCodes
  • serialnr: this is either the serial number of the bar or, if multiple items or the item does not have a code, then it is the serial number of a tamper evident bag
  • metalcode: must be one of the existing metalcodes, for a list of valid metalcodes view API\Dropdownvalues\metalcodes
  • measurementcode: normally “g” for grams or “ozt” for troy oz. For a list of valid measurementcodes view API\Dropdownvalues\measurementcodes
  • measurementweight: the amount of mass as expressed by the measurementcode chosen
  • purity: must be between 0 and 1 with up to 4 digits. e.g. “0.9999”

Sample Bulk-Insert Json Generation

The JsonObject can be generated in many ways. The example below illustrates how SQL Server 2017 can generate a valid JsonObject string using T-SQL:

    declare @jsonOutput NVARCHAR(MAX) 

SET @jsonOutput = (

select * from
select top 100
''+ SerialNr + '.jpg' as imageurl,
serialnr as rfid,
'scan' as eventtypecode,
'00:04:3E:94:E0:41' as scannerid,
productqty as itemquantity,
isnull(Minter,'') as brandcode,
productsubgroup as itemtypecode,
serialnr as serialnr,
metalcode as metalcode,
'g' as measurementcode,
(MeasuredRawGram/productqty) as measurementweight,
purity/100 as purity
from [dbo].[fn_StorageStatementByDate] ('SBSG','','','','2019-1-1','','',0,0,'sgd') as g
join tbh_products on g.productid = tbh_products.productid
where metalcode = 'AU'
) as myTable FOR JSON AUTO)

  • The imageurl follows the current system image naming convention / path.
  • RFIDs and Serial nr. Are the same in our system. We use friendly RFID values.
  • ScannerID must be assigned for the import
  • Purity is divided by 100 because the storage convention is different
  • For JSON AUTO generates the JSON string which is assigned to @jsonOutput

Before being returned @jsonOutput is “escaped” so that the string can be pasted into the body of the post field. “Escaping essentially means that the string is made URL Friendly. In T-SQL this is don with the STRNG_ESCAPE function as follows:

    select STRING_ESCAPE(@jsonOutput,'json')  

The resulting string can then be pasted in into the post body using a REST client to call the API.