SQL Server
SQL Server
Generate an E-way Bill
See more HTTP Misc Examples
Demonstrates how to send an HTTP POST request to generate an e-way bill.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- This example uses the previously obtained access token that was retrieved
-- in this example: Get EWAY Auth Token using Gstin, username, password, and app_key
DECLARE @jsonAuth int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonAuth OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @jsonAuth, 'LoadFile', @success OUT, 'qa_data/tokens/ewayAuth.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jsonAuth, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonAuth
RETURN
END
-- The jsonAuth contains something like this:
-- {
-- "authToken": "IBTeFtxNfVurg71LTzZ2r0xK7",
-- "decryptedSek": "5g1TyTie7yoslU3DrbYATa7mWyPazlODE7cEh5Vy4Ho="
-- }
-- Generate the JSON data for the e-way bill.
-- The following code can be generated by pasting representative JSON into this online tool:
-- Generate JSON Code
DECLARE @jsonData int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonData OUT
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'supplyType', 'O'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'subSupplyType', '1'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'docType', 'INV'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'docNo', 'AW1234-2'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'docDate', '05/04/2018'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromGstin', '09ABDC24212B1FK'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromTrdName', 'willy'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromAddr1', '3RD CROSS NO 200 19 A'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromAddr2', 'GROUND FLOOR OZZY ROAD'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'fromPlace', 'BUSY TOWN'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'fromPincode', '640033'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'actFromStateCode', '05'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'fromStateCode', '05'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toGstin', '01AAAASCC10BBBB'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toTrdName', 'mthustra'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toAddr1', 'Shrek Ogre'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toAddr2', 'Basadronsil'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'toPlace', 'Grifl Blagar'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'toPincode', '699988'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'actToStateCode', '29'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'toStateCode', '02'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'totalValue', '5609889'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'cgstValue', '0'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'sgstValue', '0'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'igstValue', '168296.67'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'cessValue', '224395.56'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transporterId', '09ABDC24212B1FK'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transporterName', ''
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transDocNo', '12332'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'transMode', '1'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transDistance', '656'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'transDocDate', '10/04/2018'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'vehicleNo', 'PBN4567'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'vehicleType', 'R'
EXEC sp_OASetProperty @jsonData, 'I', 0
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'itemList[i].productName', 'Wheat'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'itemList[i].productDesc', 'Wheat'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].hsnCode', '1001'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].quantity', '4'
EXEC sp_OAMethod @jsonData, 'UpdateString', @success OUT, 'itemList[i].qtyUnit', 'BOX'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].cgstRate', '0'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].sgstRate', '0'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].igstRate', '3'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].cessRate', '4'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].cessAdvol', '0'
EXEC sp_OAMethod @jsonData, 'UpdateNumber', @success OUT, 'itemList[i].taxableAmount', '5609889'
-- The body of the HTTP POST will contain JSON that looks like this:
-- {
-- "action":"GENEWAYBILL",
-- "data": " iJiJGXq ... oUZp/25Y "
-- }
-- The "data" is the encrypted jsonData using our previously agreed-upon symmetric encryption key.
-- Let's begin build the JSON request body..
DECLARE @jsonRequestBody int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonRequestBody OUT
EXEC sp_OAMethod @jsonRequestBody, 'UpdateString', @success OUT, 'action', 'GENEWAYBILL'
-- Setup the encryptor using the decryptedSek from the jsonAuth
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
EXEC sp_OASetProperty @crypt, 'CipherMode', 'ecb'
EXEC sp_OASetProperty @crypt, 'KeyLength', 256
EXEC sp_OAMethod @jsonAuth, 'StringOf', @sTmp0 OUT, 'decryptedSek'
EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'base64'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
-- Encrypt the jsonData and add it to our JSON request body
EXEC sp_OAMethod @jsonData, 'Emit', @sTmp1 OUT
EXEC sp_OAMethod @crypt, 'EncryptStringENC', @sTmp0 OUT, @sTmp1
EXEC sp_OAMethod @jsonRequestBody, 'UpdateString', @success OUT, 'data', @sTmp0
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
-- Add the authtoken to the request header.
-- Be careful to be precise with uppercase/lowercase ("authtoken" vs "authToken")
EXEC sp_OAMethod @jsonAuth, 'StringOf', @sTmp0 OUT, 'authToken'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'authtoken', @sTmp0
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Gstin', '09ABDC24212B1FK'
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
-- POST the request to generate an e-way bill:
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'http://ewb.wepgst.com/api/EWayBill', @jsonRequestBody, 'application/json', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @jsonData
EXEC @hr = sp_OADestroy @jsonRequestBody
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT
PRINT 'response status code =' + @respStatusCode
PRINT 'response body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
IF @respStatusCode <> 200
BEGIN
PRINT 'Failed in some unknown way.'
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @jsonData
EXEC @hr = sp_OADestroy @jsonRequestBody
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- When the response status code = 200, we'll have either
-- success response like this:
-- {"status":"1","data":"..."}
--
-- or a failed response like this:
--
-- {"status":"0","error":"eyJlcnJvckNvZGVzIjoiMTA4In0="}
-- Load the response body into a JSON object.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
DECLARE @status int
EXEC sp_OAMethod @json, 'IntOf', @status OUT, 'status'
PRINT 'status = ' + @status
IF @status <> 1
BEGIN
-- Failed. Base64 decode the error
-- {"status":"0","error":"eyJlcnJvckNvZGVzIjoiMTA4In0="}
-- For an invalid password, the error is: {"errorCodes":"108"}
DECLARE @sbError int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbError OUT
EXEC sp_OAMethod @json, 'StringOfSb', @success OUT, 'error', @sbError
EXEC sp_OAMethod @sbError, 'Decode', @success OUT, 'base64', 'utf-8'
EXEC sp_OAMethod @sbError, 'GetAsString', @sTmp0 OUT
PRINT 'error: ' + @sTmp0
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @jsonData
EXEC @hr = sp_OADestroy @jsonRequestBody
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbError
RETURN
END
EXEC sp_OASetProperty @json, 'EmitCompact', 0
PRINT 'JSON response:'
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @bdData int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdData OUT
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'data'
EXEC sp_OAMethod @bdData, 'AppendEncoded', @success OUT, @sTmp0, 'base64'
EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @bdData
-- Decrypts to
-- {"ewayBillNo":331001121234,"ewayBillDate":"24/05/2018 04:38:00 PM","validUpto":"31/05/2018 11:59:00 PM"}
DECLARE @jsonBill int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonBill OUT
EXEC sp_OAMethod @bdData, 'GetString', @sTmp0 OUT, 'utf-8'
EXEC sp_OAMethod @jsonBill, 'Load', @success OUT, @sTmp0
DECLARE @ewayBillNo int
EXEC sp_OAMethod @jsonBill, 'IntOf', @ewayBillNo OUT, 'ewayBillNo'
PRINT 'ewayBillNo = ' + @ewayBillNo
DECLARE @ewayBillDate nvarchar(4000)
EXEC sp_OAMethod @jsonBill, 'StringOf', @ewayBillDate OUT, 'ewayBillDate'
PRINT 'ewayBillDate = ' + @ewayBillDate
DECLARE @validUpto nvarchar(4000)
EXEC sp_OAMethod @jsonBill, 'StringOf', @validUpto OUT, 'validUpto'
PRINT 'validUpto = ' + @validUpto
-- Sample output:
-- ewayBillNo = 331001121234
-- ewayBillDate = 24/05/2018 04:55:00 PM
-- validUpto = 31/05/2018 11:59:00 PM
EXEC @hr = sp_OADestroy @jsonAuth
EXEC @hr = sp_OADestroy @jsonData
EXEC @hr = sp_OADestroy @jsonRequestBody
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbError
EXEC @hr = sp_OADestroy @bdData
EXEC @hr = sp_OADestroy @jsonBill
END
GO