SQL Server
SQL Server
eBay -- Get Inventory Items
See more eBay Examples
Retrieves up to 100 inventory items. If an eBay account has more than 100 inventory items, then the Nth page of inventory items can be retrieved, until there are no more pages. (The first page of records has a value of 0, the second page of records has a value of 1, etc.)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
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.
-- Use a previously obtained user token. The token should look something like this:
-- "v^1.1#i^1#r^0#p^3#I^3#f^0#t^H4sIAAAAAAAAAOVXa2wUVRTu9k ... 89xuCWYREAAA=="
DECLARE @accessToken nvarchar(4000)
SELECT @accessToken = 'EBAY_ACCESS_TOKEN'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- This example uses the sandbox.
-- Change "api.sandbox.ebay.com" to "api.ebay.com" to use the production system.
DECLARE @sbUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT
DECLARE @success int
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://api.sandbox.ebay.com/sell/inventory/v1/inventory_item?limit=_LIMIT&offset=_OFFSET'
DECLARE @numReplaced int
EXEC sp_OAMethod @sbUrl, 'Replace', @numReplaced OUT, '_LIMIT', '100'
-- The offset indicates the page number. Offset 0 gets items 0-99 (if limit=100),
-- Offset 1 gets items 100-199, etc.
EXEC sp_OAMethod @sbUrl, 'Replace', @numReplaced OUT, '_OFFSET', '0'
-- Add our access token to the request, which is a header
-- having the following format:
-- Authorization: Bearer <userAccessToken>
DECLARE @sbAuth int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAuth OUT
EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, 'Bearer '
EXEC sp_OAMethod @sbAuth, 'Append', @success OUT, @accessToken
EXEC sp_OAMethod @sbAuth, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Authorization', @sTmp0
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
EXEC sp_OASetProperty @http, 'AllowGzip', 1
DECLARE @strJson nvarchar(4000)
EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'QuickGetStr', @strJson OUT, @sTmp0
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @sbAuth
RETURN
END
-- The response should be JSON, even if an error.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @strJson
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'Response status code = ' + @iTmp0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @sbAuth
EXEC @hr = sp_OADestroy @json
RETURN
END
-- See the sample JSON response below..
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- To iterate over the inventory items..
DECLARE @numItems int
EXEC sp_OAMethod @json, 'SizeOfArray', @numItems OUT, 'inventoryItems'
DECLARE @i int
SELECT @i = 0
DECLARE @j int
WHILE @i < @numItems
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'inventoryItems[i].sku'
PRINT @i + ') sku: ' + @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'inventoryItems[i].product.title'
PRINT ' title: ' + @sTmp0
EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'inventoryItems[i].product.aspects.Brand'
IF @iTmp0 = 1
BEGIN
DECLARE @brands int
EXEC sp_OAMethod @json, 'ArrayOf', @brands OUT, 'inventoryItems[i].product.aspects.Brand'
DECLARE @numBrands int
EXEC sp_OAGetProperty @brands, 'Size', @numBrands OUT
SELECT @j = 0
WHILE @j < @numBrands
BEGIN
EXEC sp_OAMethod @brands, 'StringAt', @sTmp0 OUT, @j
PRINT ' brand: ' + @sTmp0
SELECT @j = @j + 1
END
END
EXEC sp_OAMethod @json, 'HasMember', @iTmp0 OUT, 'inventoryItems[i].product.aspects.Feature'
IF @iTmp0 = 1
BEGIN
DECLARE @features int
EXEC sp_OAMethod @json, 'ArrayOf', @features OUT, 'inventoryItems[i].product.aspects.Feature'
DECLARE @numFeatures int
EXEC sp_OAGetProperty @features, 'Size', @numFeatures OUT
SELECT @j = 0
WHILE @j < @numFeatures
BEGIN
EXEC sp_OAMethod @features, 'StringAt', @sTmp0 OUT, @j
PRINT ' feature: ' + @sTmp0
SELECT @j = @j + 1
END
END
SELECT @i = @i + 1
END
-- The output of the above loop:
-- 0) sku: GP-Cam-01
-- title: GoPro Hero4 Helmet Cam
-- brand: GoPro
-- 1) sku: AppleWatch
-- title: Test listing - do not bid or buy - awesome Apple watch test 2
-- feature: Water resistance
-- feature: GPS
--
--
-- A sample JSON response with 2 inventory items.
-- {
-- "total": 2,
-- "size": 2,
-- "href": "/sell/inventory/v1/inventory_item?offset=0&limit=100",
-- "limit": 100,
-- "inventoryItems": [
-- {
-- "sku": "GP-Cam-01",
-- "product": {
-- "title": "GoPro Hero4 Helmet Cam",
-- "aspects": {
-- "Brand": [
-- "GoPro"
-- ],
-- "Optical Zoom": [
-- "10x"
-- ],
-- "Type": [
-- "Helmet/Action"
-- ],
-- "Recording Definition": [
-- "High Definition"
-- ],
-- "Media Format": [
-- "Flash Drive (SSD)"
-- ],
-- "Storage Type": [
-- "Removable"
-- ]
-- },
-- "description": "New GoPro Hero4 Helmet Cam. Unopened box.",
-- "imageUrls": [
-- "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1000.jpg",
-- "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1001.jpg",
-- "http://i.ebayimg.com/images/i/182196556219-0-1/s-l1002.jpg"
-- ]
-- },
-- "condition": "NEW",
-- "availability": {
-- "pickupAtLocationAvailability": [
-- ],
-- "shipToLocationAvailability": {
-- "quantity": 50
-- }
-- }
-- },
-- {
-- "sku": "AppleWatch",
-- "product": {
-- "title": "Test listing - do not bid or buy - awesome Apple watch test 2",
-- "aspects": {
-- "CPU": [
-- "Dual-Core Processor"
-- ],
-- "Feature": [
-- "Water resistance",
-- "GPS"
-- ]
-- },
-- "description": "Test listing - do not bid or buy\n Built-in GPS. Water resistance to 50 meters.1 A new lightning-fast dual-core processor. And a display that?s two times brighter than before. Full of features that help you stay active, motivated, and connected, Apple Watch Series 2 is designed for all the ways you move ",
-- "upc": [
-- "888462079525"
-- ],
-- "imageUrls": [
-- "http://store.storeimages.cdn-apple.com/4973/as-images.apple.com/is/image/AppleInc/aos/published/images/S/1/S1/42/S1-42-alu-silver-sport-white-grid?wid=332&hei=392&fmt=jpeg&qlt=95&op_sharpen=0&resMode=bicub&op_usm=0.5,0.5,0,0&iccEmbed=0&layer=comp&.v=1472247758975",
-- "http://store.storeimages.cdn-apple.com/4973/as-images.apple.com/is/image/AppleInc/aos/published/images/4/2/42/stainless/42-stainless-sport-white-grid?wid=332&hei=392&fmt=jpeg&qlt=95&op_sharpen=0&resMode=bicub&op_usm=0.5,0.5,0,0&iccEmbed=0&layer=comp&.v=1472247760390",
-- "http://store.storeimages.cdn-apple.com/4973/as-images.apple.com/is/image/AppleInc/aos/published/images/4/2/42/ceramic/42-ceramic-sport-cloud-grid?wid=332&hei=392&fmt=jpeg&qlt=95&op_sharpen=0&resMode=bicub&op_usm=0.5,0.5,0,0&iccEmbed=0&layer=comp&.v=1472247758007"
-- ]
-- },
-- "condition": "NEW",
-- "packageWeightAndSize": {
-- "dimensions": {
-- "width": 15.0,
-- "length": 10.0,
-- "height": 5.0,
-- "unit": "INCH"
-- },
-- "weight": {
-- "value": 2.0,
-- "unit": "POUND"
-- }
-- },
-- "availability": {
-- "pickupAtLocationAvailability": [
-- ],
-- "shipToLocationAvailability": {
-- "quantity": 10
-- }
-- }
-- }
-- ]
-- }
--
--
--
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @sbAuth
EXEC @hr = sp_OADestroy @json
END
GO