Back to Collection Items
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
DECLARE @queryParams int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @queryParams OUT
EXEC sp_OAMethod @queryParams, 'UpdateInt', @success OUT, 'list', 10
DECLARE @resp int
EXEC sp_OAMethod @http, 'QuickRequestParams', @resp OUT, 'GET', 'https://api.simplekart.com/orders', @queryParams
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @queryParams
RETURN
END
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
DECLARE @jarrResp int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonArray', @jarrResp OUT
EXEC sp_OAMethod @jarrResp, 'LoadSb', @success OUT, @sbResponseBody
EXEC sp_OASetProperty @jarrResp, 'EmitCompact', 0
PRINT 'Response Body:'
EXEC sp_OAMethod @jarrResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @queryParams
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jarrResp
RETURN
END
EXEC @hr = sp_OADestroy @resp
-- Sample JSON response:
-- (Sample code for parsing the JSON response is shown below)
-- [
-- {
-- "orderId": 674,
-- "items": [
-- {
-- "itemId": 1657,
-- "name": "Smart WiFi Router",
-- "price": 2096,
-- "description": "Wifi router/wireless router. One of routers for wireless internet, wireless routers for home. Dual band router and gigabit router. Ideal as internet router also gaming router. Long range coverage with high speed. Compatible with all 802.11ac devices and below. 5ghz router/5g router Frequency Range: 2.4GHz and 5GHz; Interface Available: 4 x 10/100/1000Mbps LAN Ports, 1 10/100/1000Mbps WAN Port, 1 USB 2.0 Ports; Protocols Supported: Supports IPv4 and IPv6; System Requirement: Microsoft Windows 98SE NT 2000 XP Vista, or Windows 7 Windows 8/8.1/10, MAC OS NetWare UNIX or Linux; Wireless Standards: IEEE 802.11ac/n/a 5GHz, IEEE 802.11b/g/n 2.4GH"
-- }
-- ],
-- "arrivalDate": "2020-03-03T13:07:45.962Z",
-- "orderDate": "2020-03-01T19:27:26.733Z",
-- "orderTotal": 2096,
-- "address": "95 Tarkiln Hill St.Laurel, MD 20707"
-- },
-- {
-- "orderId": 675,
-- "items": [
-- {
-- "itemId": 1658,
-- "name": "VR Gaming Headset",
-- "price": 79990,
-- "description": "VR Gaming Headset puts you at the center of virtual reality's most pulse-pounding, PC-powered gameplay. See the action unfold all around you through our sharpest lenses and next-generation optics. Lose yourself in the experience with a comfortable, improved design that lets you start playing faster and keep playing longer. Insight tracking gives you the power to move throughout your playspace in any direction. No external sensors. No complicated setup. Just hands-on action and interaction with our updated Oculus Touch controllers right out of the box. Step into the game and the future of PC VR."
-- }
-- ],
-- "arrivalDate": "2020-03-03T15:00:56.787Z",
-- "orderDate": "2020-03-02T21:25:22.096Z",
-- "orderTotal": 79990,
-- "address": "7405 Arcadia St.Rockville, MD 20850"
-- },
-- {
-- "orderId": 676,
-- "items": [
-- {
-- "itemId": 1665,
-- "name": "Smart Watch",
-- "price": 1805,
-- "description": "Built-in Multi-Sports Mode, record your Running, Cycling, Football, Swimming, Table Tennis, Badminton, Climbing, recording your daily sports data."
-- }
-- ],
-- "arrivalDate": "2020-03-05T17:30:15.156Z",
-- "orderDate": "2020-03-03T01:10:27.512Z",
-- "orderTotal": 1805,
-- "address": "9791 Lawrence Dr.Marlton, NJ 08053r"
-- },
-- {
-- "orderId": 677,
-- "items": [
-- {
-- "itemId": 1659,
-- "name": "Classic Loop Top Shaker Bottle, 20-Ounce",
-- "price": 578,
-- "description": "Easy to maintain proper nutrition and hydration on-the-go.The wide mouth makes it easy to add scoops of powdered mixes, meal ingredients, and liquids, and embossed markings show both ounces and milliliters for easy measuring anywhere (note that markings on the 20-ounce capacity shaker cup only go up to 12 ounces). Bottle contents and liquids are securely contained thanks to a tightly threaded screw-on lid that creates a leakproof seal, and a flip cap that snaps securely shut for shaking and transportation."
-- }
-- ],
-- "arrivalDate": "2020-03-03T18:16:24.911Z",
-- "orderDate": "2020-03-01T06:09:23.007Z",
-- "orderTotal": 578,
-- "address": "822 Clay St.Tampa, FL 33604"
-- },
-- {
-- "orderId": 678,
-- "items": [
-- {
-- "itemId": 1663,
-- "name": "Hair Dryer",
-- "price": 1400,
-- "description": "Hair dryer is engineered to protect hair from extreme heat damage, with the fastest drying+ and controlled styling to help increase smoothness by 75%, increase shine by up to 132% and decrease frizz and flyaways by up to 61%."
-- }
-- ],
-- "arrivalDate": "2020-03-04T12:09:07.955Z",
-- "orderDate": "2020-03-02T21:50:44.173Z",
-- "orderTotal": 1400,
-- "address": "7444 SW. State Ave.Philadelphia, PA 19111"
-- },
-- {
-- "orderId": 679,
-- "items": [
-- {
-- "itemId": 1660,
-- "name": "4-Port USB 2.0 Hub",
-- "price": 505,
-- "description": "Easily add up to 4 devices with the USB 2.0 Hub. Particularly great for recent notebooks that provide a limited number of USB ports. Achieve full 480 Mbps on each port, or daisy-chain multiple hubs to a maximum of 127 devices.Fully backwards compatible with the USB 1.1 products."
-- }
-- ],
-- "arrivalDate": "2020-03-04T11:29:56.99Z",
-- "orderDate": "2020-03-03T00:51:26.19Z",
-- "orderTotal": 505,
-- "address": "307 Purple Finch Ave.New Lenox, IL 60451"
-- },
-- {
-- "orderId": 680,
-- "items": [
-- {
-- "itemId": 1661,
-- "name": "Wipes Flushable Wet Wipes Dispenser, Mint Chill, 48 Count (Pack of 3)",
-- "price": 938,
-- "description": "For a superior clean that’s fresh, gentle, and effective, choose Wipes with the CleaningRipples Texture. Wipes are 100 percent flushable, remove odor causing bacteria and offer a large, dual layer wipe to clean better than when using dry toilet paper alone."
-- }
-- ],
-- "arrivalDate": "2020-03-04T02:06:58.886Z",
-- "orderDate": "2020-03-02T04:33:52.652Z",
-- "orderTotal": 938,
-- "address": "946 Holly StreetHamilton, OH 45011"
-- },
-- {
-- "orderId": 681,
-- "items": [
-- {
-- "itemId": 1663,
-- "name": "Hair Dryer",
-- "price": 1400,
-- "description": "Hair dryer is engineered to protect hair from extreme heat damage, with the fastest drying+ and controlled styling to help increase smoothness by 75%, increase shine by up to 132% and decrease frizz and flyaways by up to 61%."
-- }
-- ],
-- "arrivalDate": "2020-03-04T17:26:07.706Z",
-- "orderDate": "2020-03-02T04:17:23.689Z",
-- "orderTotal": 1400,
-- "address": "8924 East Birchwood St.Hamburg, NY 14075"
-- },
-- {
-- "orderId": 682,
-- "items": [
-- {
-- "itemId": 1665,
-- "name": "Smart Watch",
-- "price": 1805,
-- "description": "Built-in Multi-Sports Mode, record your Running, Cycling, Football, Swimming, Table Tennis, Badminton, Climbing, recording your daily sports data."
-- }
-- ],
-- "arrivalDate": "2020-03-05T14:07:01.688Z",
-- "orderDate": "2020-03-03T04:58:18.504Z",
-- "orderTotal": 1805,
-- "address": "10 Wilson LaneBay City, MI 48706"
-- },
-- {
-- "orderId": 683,
-- "items": [
-- {
-- "itemId": 1666,
-- "name": "Learning Thermostat, 3rd Gen, Smart Thermostat",
-- "price": 15746,
-- "description": "Learning Thermostat is a smart thermostat that learns what temperatures you like, turns itself down when you’re away and connects to your phone. It has a big, sharp display. And it’s proven to help save energy. In independent studies, the Thermostat saved an average of 10% to 12% on heating bills and 15% on cooling bills."
-- }
-- ],
-- "arrivalDate": "2020-03-05T10:43:23.713Z",
-- "orderDate": "2020-03-03T08:34:21.21Z",
-- "orderTotal": 15746,
-- "address": "m81 West Shirley Dr.Greenfield, IN 46140"
-- },
-- {
-- "orderId": 656,
-- "items": [
-- {
-- "itemId": 16247,
-- "name": "Postman Merch",
-- "price": 15,
-- "description": "Official T-shirt for the Postman community."
-- }
-- ],
-- "arrivalDate": "2020-3-04T13:07:45.962Z",
-- "orderDate": "2020-03-06T19:27:26.733Z",
-- "orderTotal": 15,
-- "address": "SimpleKart HQ, 221B Baker street"
-- }
-- ]
-- Sample code for parsing the JSON response...
-- Use this online tool to generate parsing code from sample JSON: Generate JSON Parsing Code
DECLARE @json int
DECLARE @orderId int
DECLARE @arrivalDate nvarchar(4000)
DECLARE @orderDate nvarchar(4000)
DECLARE @orderTotal int
DECLARE @address nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @itemId int
DECLARE @name nvarchar(4000)
DECLARE @price int
DECLARE @description nvarchar(4000)
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAGetProperty @jarrResp, 'Size', @count_i OUT
WHILE @i < @count_i
BEGIN
EXEC sp_OAMethod @jarrResp, 'ObjectAt', @json OUT, @i
EXEC sp_OAMethod @json, 'IntOf', @orderId OUT, 'orderId'
EXEC sp_OAMethod @json, 'StringOf', @arrivalDate OUT, 'arrivalDate'
EXEC sp_OAMethod @json, 'StringOf', @orderDate OUT, 'orderDate'
EXEC sp_OAMethod @json, 'IntOf', @orderTotal OUT, 'orderTotal'
EXEC sp_OAMethod @json, 'StringOf', @address OUT, 'address'
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, 'items'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
EXEC sp_OAMethod @json, 'IntOf', @itemId OUT, 'items[j].itemId'
EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'items[j].name'
EXEC sp_OAMethod @json, 'IntOf', @price OUT, 'items[j].price'
EXEC sp_OAMethod @json, 'StringOf', @description OUT, 'items[j].description'
SELECT @j = @j + 1
END
EXEC @hr = sp_OADestroy @json
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @queryParams
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jarrResp
END
GO
Curl Command
curl -G -d "list=10"
https://api.simplekart.com/orders
Postman Collection Item JSON
{
"name": "Get orders from the database ",
"request": {
"method": "GET",
"header": [
],
"url": {
"raw": "{{baseUrl}}/orders?list=10",
"host": [
"{{baseUrl}}"
],
"path": [
"orders"
],
"query": [
{
"key": "list",
"value": "10",
"description": "Number of orders to show"
}
]
},
"description": "Obtain order details from orders database"
},
"response": [
{
"name": "OK - Successful pull of orders",
"originalRequest": {
"method": "GET",
"header": [
],
"url": {
"raw": "{{baseUrl}}/orders?list=10",
"host": [
"{{baseUrl}}"
],
"path": [
"orders"
],
"query": [
{
"key": "list",
"value": "10"
}
]
}
},
"status": "OK",
"code": 200,
"_postman_previewlanguage": "json",
"header": [
{
"key": "Content-Type",
"value": "application/json"
}
],
"cookie": [
],
"body": "[\n {\n \"orderId\": 674,\n \"items\": [\n {\n \"itemId\": 1657,\n \"name\": \"Smart WiFi Router\",\n \"price\": 2096,\n \"description\": \"Wifi router/wireless router. One of routers for wireless internet, wireless routers for home. Dual band router and gigabit router. Ideal as internet router also gaming router. Long range coverage with high speed. Compatible with all 802.11ac devices and below. 5ghz router/5g router Frequency Range: 2.4GHz and 5GHz; Interface Available: 4 x 10/100/1000Mbps LAN Ports, 1 10/100/1000Mbps WAN Port, 1 USB 2.0 Ports; Protocols Supported: Supports IPv4 and IPv6; System Requirement: Microsoft Windows 98SE NT 2000 XP Vista, or Windows 7 Windows 8/8.1/10, MAC OS NetWare UNIX or Linux; Wireless Standards: IEEE 802.11ac/n/a 5GHz, IEEE 802.11b/g/n 2.4GH\"\n }\n ],\n \"arrivalDate\": \"2020-03-03T13:07:45.962Z\",\n \"orderDate\": \"2020-03-01T19:27:26.733Z\",\n \"orderTotal\": 2096,\n \"address\": \"95 Tarkiln Hill St.Laurel, MD 20707\"\n },\n {\n \"orderId\": 675,\n \"items\": [\n {\n \"itemId\": 1658,\n \"name\": \"VR Gaming Headset\",\n \"price\": 79990,\n \"description\": \"VR Gaming Headset puts you at the center of virtual reality's most pulse-pounding, PC-powered gameplay. See the action unfold all around you through our sharpest lenses and next-generation optics. Lose yourself in the experience with a comfortable, improved design that lets you start playing faster and keep playing longer. Insight tracking gives you the power to move throughout your playspace in any direction. No external sensors. No complicated setup. Just hands-on action and interaction with our updated Oculus Touch controllers right out of the box. Step into the game and the future of PC VR.\"\n }\n ],\n \"arrivalDate\": \"2020-03-03T15:00:56.787Z\",\n \"orderDate\": \"2020-03-02T21:25:22.096Z\",\n \"orderTotal\": 79990,\n \"address\": \"7405 Arcadia St.Rockville, MD 20850\"\n },\n {\n \"orderId\": 676,\n \"items\": [\n {\n \"itemId\": 1665,\n \"name\": \"Smart Watch\",\n \"price\": 1805,\n \"description\": \"Built-in Multi-Sports Mode, record your Running, Cycling, Football, Swimming, Table Tennis, Badminton, Climbing, recording your daily sports data.\"\n }\n ],\n \"arrivalDate\": \"2020-03-05T17:30:15.156Z\",\n \"orderDate\": \"2020-03-03T01:10:27.512Z\",\n \"orderTotal\": 1805,\n \"address\": \"9791 Lawrence Dr.Marlton, NJ 08053r\"\n },\n {\n \"orderId\": 677,\n \"items\": [\n {\n \"itemId\": 1659,\n \"name\": \"Classic Loop Top Shaker Bottle, 20-Ounce\",\n \"price\": 578,\n \"description\": \"Easy to maintain proper nutrition and hydration on-the-go.The wide mouth makes it easy to add scoops of powdered mixes, meal ingredients, and liquids, and embossed markings show both ounces and milliliters for easy measuring anywhere (note that markings on the 20-ounce capacity shaker cup only go up to 12 ounces). Bottle contents and liquids are securely contained thanks to a tightly threaded screw-on lid that creates a leakproof seal, and a flip cap that snaps securely shut for shaking and transportation.\"\n }\n ],\n \"arrivalDate\": \"2020-03-03T18:16:24.911Z\",\n \"orderDate\": \"2020-03-01T06:09:23.007Z\",\n \"orderTotal\": 578,\n \"address\": \"822 Clay St.Tampa, FL 33604\"\n },\n {\n \"orderId\": 678,\n \"items\": [\n {\n \"itemId\": 1663,\n \"name\": \"Hair Dryer\",\n \"price\": 1400,\n \"description\": \"Hair dryer is engineered to protect hair from extreme heat damage, with the fastest drying+ and controlled styling to help increase smoothness by 75%, increase shine by up to 132% and decrease frizz and flyaways by up to 61%.\"\n }\n ],\n \"arrivalDate\": \"2020-03-04T12:09:07.955Z\",\n \"orderDate\": \"2020-03-02T21:50:44.173Z\",\n \"orderTotal\": 1400,\n \"address\": \"7444 SW. State Ave.Philadelphia, PA 19111\"\n },\n {\n \"orderId\": 679,\n \"items\": [\n {\n \"itemId\": 1660,\n \"name\": \"4-Port USB 2.0 Hub\",\n \"price\": 505,\n \"description\": \"Easily add up to 4 devices with the USB 2.0 Hub. Particularly great for recent notebooks that provide a limited number of USB ports. Achieve full 480 Mbps on each port, or daisy-chain multiple hubs to a maximum of 127 devices.Fully backwards compatible with the USB 1.1 products.\"\n }\n ],\n \"arrivalDate\": \"2020-03-04T11:29:56.99Z\",\n \"orderDate\": \"2020-03-03T00:51:26.19Z\",\n \"orderTotal\": 505,\n \"address\": \"307 Purple Finch Ave.New Lenox, IL 60451\"\n },\n {\n \"orderId\": 680,\n \"items\": [\n {\n \"itemId\": 1661,\n \"name\": \"Wipes Flushable Wet Wipes Dispenser, Mint Chill, 48 Count (Pack of 3)\",\n \"price\": 938,\n \"description\": \"For a superior clean that’s fresh, gentle, and effective, choose Wipes with the CleaningRipples Texture. Wipes are 100 percent flushable, remove odor causing bacteria and offer a large, dual layer wipe to clean better than when using dry toilet paper alone.\"\n }\n ],\n \"arrivalDate\": \"2020-03-04T02:06:58.886Z\",\n \"orderDate\": \"2020-03-02T04:33:52.652Z\",\n \"orderTotal\": 938,\n \"address\": \"946 Holly StreetHamilton, OH 45011\"\n },\n {\n \"orderId\": 681,\n \"items\": [\n {\n \"itemId\": 1663,\n \"name\": \"Hair Dryer\",\n \"price\": 1400,\n \"description\": \"Hair dryer is engineered to protect hair from extreme heat damage, with the fastest drying+ and controlled styling to help increase smoothness by 75%, increase shine by up to 132% and decrease frizz and flyaways by up to 61%.\"\n }\n ],\n \"arrivalDate\": \"2020-03-04T17:26:07.706Z\",\n \"orderDate\": \"2020-03-02T04:17:23.689Z\",\n \"orderTotal\": 1400,\n \"address\": \"8924 East Birchwood St.Hamburg, NY 14075\"\n },\n {\n \"orderId\": 682,\n \"items\": [\n {\n \"itemId\": 1665,\n \"name\": \"Smart Watch\",\n \"price\": 1805,\n \"description\": \"Built-in Multi-Sports Mode, record your Running, Cycling, Football, Swimming, Table Tennis, Badminton, Climbing, recording your daily sports data.\"\n }\n ],\n \"arrivalDate\": \"2020-03-05T14:07:01.688Z\",\n \"orderDate\": \"2020-03-03T04:58:18.504Z\",\n \"orderTotal\": 1805,\n \"address\": \"10 Wilson LaneBay City, MI 48706\"\n },\n {\n \"orderId\": 683,\n \"items\": [\n {\n \"itemId\": 1666,\n \"name\": \"Learning Thermostat, 3rd Gen, Smart Thermostat\",\n \"price\": 15746,\n \"description\": \"Learning Thermostat is a smart thermostat that learns what temperatures you like, turns itself down when you’re away and connects to your phone. It has a big, sharp display. And it’s proven to help save energy. In independent studies, the Thermostat saved an average of 10% to 12% on heating bills and 15% on cooling bills.\"\n }\n ],\n \"arrivalDate\": \"2020-03-05T10:43:23.713Z\",\n \"orderDate\": \"2020-03-03T08:34:21.21Z\",\n \"orderTotal\": 15746,\n \"address\": \"m81 West Shirley Dr.Greenfield, IN 46140\"\n },\n {\n \"orderId\": 656,\n \"items\": [\n {\n \"itemId\": 16247,\n \"name\": \"Postman Merch\",\n \"price\": 15,\n \"description\": \"Official T-shirt for the Postman community.\"\n }\n ],\n \"arrivalDate\": \"2020-3-04T13:07:45.962Z\",\n \"orderDate\": \"2020-03-06T19:27:26.733Z\",\n \"orderTotal\": 15,\n \"address\": \"SimpleKart HQ, 221B Baker street\"\n }\n]"
}
]
}
