Call Web API (not using browser)

[jguengerich]

Is there any help or examples available for using Gold's Web API? I can successfully use it via the built-in API help pages, but I would like to access it from a custom program (C#, .NET) and I can't get a response. I thought I remembered seeing an example on this forum or in the Wiki, but now I can't find it.

It would help if the search function of this forum didn't ignore 3-letter words (searching for "api" or "web api" doesn't return any results, so I used Google with the site: parameter).

The Wiki page (http://wiki.ablecommerce.com/index.php/ ... ld_Web_Api) doesn't really give implementation help.

[Katie]

Hi Jay,

I don't know if this will help, but we have a search page that will go through the website, help site, forums, and wiki. I use it all time when I'm digging for answers.

http://ablecommerce.net/

If you still can't find the information you're looking for, please post back. This is important since we have a product that appeals to developers. I'll see what I can do to get you a better answer.

Katie

[jguengerich]

Thanks for the search info Katie.

What I was really trying to do was call the web API from an SQL Server stored procedure using OLE Automation (sp_OA....). I was using some code I found on the internet (for calling services in general, not specific to AbleCommerce) as a guide. I decided since that was also a new topic for me and added a layer of complexity, I would try a .NET project instead. However, I didn't get very far, and over the weekend I thought of a few more things to check.

Here is what I discovered. I had modified the site to prevent anonymous access(<deny users="?"), with some exceptions (<allow users="*">) for the App_Themes path and the PasswordHelp page. However, since I didn't have an exception for the api path, requests to the api were resulting in a redirect response to the login page. Once I added an exception to allow anonymous access to the api path, I was able to place an api call and get a correct response from within my SQL Server stored procedure. Some sample code that works (at least with the GET APIs) is posted below.

I have 2 more questions:
1. Is the Admin API available to a user in any of the built-in admin groups, or just certain groups? What about groups I add?
2. I may still eventually want to use a .NET project instead of a stored procedure. Does anyone have some sample code for calling the Admin API (for example all of the AdminProducts functions) in C# or VB?

Sample code for calling Web API from a stored procedure. It works for GET, I haven't tried it for PUT/POST/DELETE. You will need OLE Automation enabled on the SQL server (it is disabled by default).

Code: Select all

-- Modified version of code found at http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-%28Stored-Procedure%29-using-MSXML.aspx
CREATE proc [dbo].[CallWebAPI_SP]
      @URI varchar(2000) = '',     
      @methodName varchar(50) = '',
      @requestBody varchar(8000) = '',
      @UserName nvarchar(100) = '',
      @Password nvarchar(100) = ''
as
SET NOCOUNT ON
IF    @methodName = ''
BEGIN
      select FailPoint = 'Method Name must be set'
      return
END
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
                  source = @source,
                  description = @desc,
                  FailPoint = 'Create failed',
                  MedthodName = @methodName
      goto destroy
      return
END
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'Open failed',
            MedthodName = @methodName
      goto destroy
      return
END
-- set request headers
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'SetRequestHeader failed',
            MedthodName = @methodName
      goto destroy
      return
END
declare @len int
set @len = len(@requestBody)
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'SetRequestHeader failed',
            MedthodName = @methodName
      goto destroy
      return
END
-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF    @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'Send failed',
            MedthodName = @methodName
      goto destroy
      return
END
declare @statusText varchar(1000), @status varchar(1000)
-- Get status text
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status, @statusText, @methodName
-- Get response text
create table #mytemp (Col1 nvarchar(max));
insert into #mytemp exec sp_OAGetProperty @objectID, 'responseText'
select * from #mytemp
drop table #mytemp
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'ResponseText failed',
            MedthodName = @methodName
      goto destroy
      return
END
destroy:
      exec sp_OADestroy @objectID
SET NOCOUNT OFF

Here is a sample call:

Code: Select all

DECLARE	@return_value int

EXEC	@return_value = [dbo].[CallWebAPI_SP]
		@URI = N'https://[your_site]/api/AdminProducts/[some_product_id]',
		@methodName = N'GET',
		@UserName = N'[some_user]',
		@Password = N'[some_password]'

SELECT	'Return Value' = @return_value

The json data is returned within the stored procedure as the 1-column, 1-row #mytemp table; you can expand the stored procedure to parse it and use it as necessary.

[mazhar]

Is there any help or examples available for using Gold's Web API? I can successfully use it via the built-in API help pages, but I would like to access it from a custom program (C#, .NET) and I can't get a response. I thought I remembered seeing an example on this forum or in the Wiki, but now I can't find it.

It would help if the search function of this forum didn't ignore 3-letter words (searching for "api" or "web api" doesn't return any results, so I used Google with the site: parameter).

The Wiki page (http://wiki.ablecommerce.com/index.php/ ... ld_Web_Api) doesn't really give implementation help.

Here is an example of utilizing WEB API using jquery.

[jguengerich]

mazhar,

Do you have any examples of utilizing the Web API from a Windows Forms application? No ASP.NET or web site of any kind, just a Windows executable program that sends out a request and retrieves the response.

[mazhar]

That should be easy where you can make simple HTTP request using C#. Have a look at this example http://www.asp.net/web-api/overview/web ... net-client

I am sure you can do similar with AbleCommerce WEB API.

[jguengerich]

mazhar,

Thanks for the link. I thought I tried something like that and it wasn't working, I will take a look at the link and try again.