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.

Procedure​

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 
    'https://www.silverbullion.com.sg/uploads/parcelphotos/'+ 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)

note
  • 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.