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.