New generic procedures - Dynamic WebMethod

Release v2.3.5: New generic procedures - Dynamic WebMethod

The web method is no longer hard coded for each procedure, meaning that with only one procedure you would be able to call whatever web method you need. For instance; POST, GET, PATCH, PUT...

Below the two new procedure;

APICaller_Web_Extended(SqlString httpMethod, SqlString URL, SqlString Headers, SqlString JsonBody)

CREATE PROCEDURE [dbo].[APICaller_Web_Extended]
@httpMethod NVARCHAR (MAX) NULL, @URL NVARCHAR (MAX) NULL, @Headers NVARCHAR (MAX) NULL, @JsonBody NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_Web_Extended]

APICaller_WebMethod(SqlString httpMethod, SqlString URL, SqlString JsonBody)

CREATE PROCEDURE [dbo].[APICaller_WebMethod]
@httpMethod NVARCHAR (MAX) NULL, @URL NVARCHAR (MAX) NULL, @JsonBody NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_WebMethod]

The only different between those two is the result returned. Extented version return more detail related to the request.

Sample script calling POST Method with procedure APICaller_Web_Extended;

GO
DECLARE @httpMethod nvarchar(max)	 = 'POST'
DECLARE @URL nvarchar(max)			 = 'https://url-shortener-service.p.rapidapi.com/shorten'
DECLARE @Headers nvarchar(max)		 =  '[{ "Name": "Content-Type", "Value" :"application/x-www-form-urlencoded" }
										 ,{ "Name": "X-RapidAPI-Host","Value" :"url-shortener-service.p.rapidapi.com"}
										 ,{ "Name": "X-RapidAPI-Key", "Value" :"c56b333d25mshdbfec15f02f096ep19fa94jsne5189032cf7d"}
										 ,{"Name": "useQueryString","Value" :"true"}]';

DECLARE @JsonBody nvarchar(max)		 =  'url=https://www.linkedin.com/in/geraldo-diaz/'

Declare @ts as table
(
	Json_Result  NVARCHAR(MAX),
	ContentType  VARCHAR(100),
	ServerName   VARCHAR(100),
	Statuscode   VARCHAR(100),
	Descripcion  VARCHAR(100),
	Json_Headers NVARCHAR(MAX)
)

DECLARE @i AS INT 
 
INSERT INTO @ts
EXECUTE @i =  [dbo].[APICaller_Web_Extended] 
			   @httpMethod
			  ,@URL
			  ,@Headers
			  ,@JsonBody

 SELECT * FROM @ts

SELECT 
		Result = [name]	
 FROM (
			SELECT Context = Json_Result 
			  from @ts
		)tb
	OUTER APPLY OPENJSON  (context)  
  WITH
    ( [name]		VARCHAR(20) '$.result_url' );

SELECT  * 
 FROM OPENJSON((select Json_Headers from @ts))  
			WITH (   
					 Header		NVARCHAR(MAX) '$."Name"'      
					,Value		NVARCHAR(MAX) '$."Value"'      
					) a

Sample script calling GET Method with procedure APICaller_Web_Extended;

GO
DECLARE @httpMethod nvarchar(max)	 = 'GET'
DECLARE @URL nvarchar(max)			 = 'https://www.routingnumbers.info/api/name.json?rn=122242597'
DECLARE @Headers nvarchar(max)		 = '[{"Name": "Content-Type", "Value" :"text/javascript; charset=utf-8" }]';

DECLARE @JsonBody nvarchar(max)		 =  ''

Declare @ts as table
(
	Json_Result nvarchar(max),
	ContentType varchar(100),
	ServerName varchar(100),
	Statuscode varchar(100),
	Descripcion varchar(100),
	Json_Headers nvarchar(max)
)

DECLARE @i AS INT 
 
INSERT INTO @ts

EXECUTE @i =  [dbo].[APICaller_Web_Extended] 
			   @httpMethod
			  ,@URL
			  ,@Headers
			  ,@JsonBody

SELECT * FROM @ts

SELECT 
		[name]	
		,[rn]		
		,[message]	
		,[code]	
 FROM (
			SELECT Context = Json_Result 
			  from @ts
		)tb
	OUTER APPLY OPENJSON  (context)  
  WITH
    ( [name]		VARCHAR(20) '$.name'
	, [rn]			VARCHAR(20) '$.rn'
	, [message]		VARCHAR(20) '$.message'
	, [code]		INT			'$.code'
    );

SELECT  * 
 FROM OPENJSON((select Json_Headers from @ts))  
			WITH (   
					 Header		NVARCHAR(MAX) '$."Name"'      
					,Value		NVARCHAR(MAX) '$."Value"'      
					) a

Bug fixes:

In this release were fixed the issues below;

#36 - Patch Method
#42 - Oauth 2 with APICaller_POST_Encoded
#44 - ERROR: Procedure APICaller_POST_Extended - Column name or number of supplied values does not match table definition

New procedures added and addition of return execution value -1 when Exceptions occurs

Now, every procedure will return a default exectution value of 0 when it run suscessfully, and -1 if any exception happens. In this way, we will be able to identify when it failed, instead of returning 0 always.

Three new procedures were added;

• APICaller_POST_Encoded ( fix Issue #30 )
  This new procedure is exclusive for Call API with enconded contentType (application/x-www-form-urlencoded).

• Added APICaller_POST_Extended

• Added APICaller_POST_Encoded

These two extended procedures enable the ability to change the content-type, through the header parameter.

--Set Header
Declare @header nvarchar(max) =
 '[ {
	 "Name": "Content-Type",
	 "Value" :"application/json; charset=utf-8"
    },
    {
        "Name": "X-RapidAPI-Host",
	 "Value" :"restcountries-v1.p.rapidapi.com"
     },{
	"Name": "X-RapidAPI-Key",
	"Value" :"c56b333d25mshdbfec15f02f096ep19fa94jsne5189032cf7d"
 }]';

And more important return information related to HTTP Response like:
(Hope this would help in issue #27)

• ContentType
• Server
• StatusCode
• Status Description
• Response Headers

Example:

USE TEstDB
--Set Header
Declare @header nvarchar(max) = 
  '[{
		"Name": "Content-Type",
		"Value" :"application/json; charset=utf-8"
	},
	{
		"Name": "X-RapidAPI-Host",
		"Value" :"restcountries-v1.p.rapidapi.com"
	},{
		"Name": "X-RapidAPI-Key",
		"Value" :"c56b333d25mshdbfec15f02f096ep19fa94jsne5189032cf7d"
	}]';
--Set URL
Declare @wurl varchar(max) = 'https://restcountries-v1.p.rapidapi.com/all' 

Declare @ts as table
(
	Json_Result nvarchar(max),
	ContentType varchar(100),
	ServerName varchar(100),
	Statuscode varchar(100),
	Descripcion varchar(100),
	Json_Headers nvarchar(max)
)
declare @i as int 
 
 insert into @ts
 --Get Account Data
 exec @i = [dbo].[APICaller_GET_Extended]
							@wurl
							,''
							,@header

select * from @ts

SELECT  * 
 FROM OPENJSON((select Json_Result from @ts))  
		WITH (   
				 name		 nvarchar(max) '$."name"'      
				,alpha2Code	 nvarchar(max) '$."alpha2Code"'      
				,alpha3Code	 nvarchar(max) '$."alpha3Code"'      
				,callingCodes	 nvarchar(max) '$."callingCodes"'   as JSON    
				,capital              nvarchar(max) '$."capital"'      
				,region		 nvarchar(max) '$."region"'      
				,subregion	 nvarchar(max) '$."subregion"'  
				,timezones	 nvarchar(max) '$."timezones"'	  as JSON     
				,population	 nvarchar(max) '$."population"'      
				,"currencies"	 nvarchar(max) '$."currencies"'	  as JSON 
				,languages	 nvarchar(max) '$."languages"'	  as JSON         
				) a

SELECT  * 
 FROM OPENJSON((select Json_Headers from @ts))  
		WITH (   
				 Header	 nvarchar(max) '$."Name"'      
				,Value	 nvarchar(max) '$."Value"'      
				) a

New Utility function.

In this version the assembly was unsigned in order to prevent issue related to the key. (Bug #18 ).
I also added a new utility function named fn_GetBytes which receive an string as parameter and return an array of byte. It would help when calling API that required basic User:Password authentication like the one commented in bug #18 .

#20 Fix SSL issue.

Establish trust relationship for the SSL/TLS secure channel

Fix bugs:
#15 - Could not create SSL/TLS secure channel.
#16 - Object reference not set to an instance of an object

I fixed this error by adding this line before calling the web method:

System.Net.ServicePointManager.ServerCertificateValidationCallback = (senderX, certificate, chain, sslPolicyErrors) => { return true; };

Multiple headers in request JsonFormat

There are new procedures that could consumer GET/POST Method sending multiples headers in JsonFormat. See sample samples in readme.

APICaller_GET_headers(SqlString URL, SqlString Headers)
APICaller_POST_headers(SqlString URL, SqlString Headers)
APICaller_GET_JsonBody_Header(SqlString URL, SqlString Headers, SqlString JsonBody)
APICaller_POST_JsonBody_Header(SqlString URL, SqlString Headers, SqlString JsonBody)

New utilities method added:
*GetTimestamp
*Create_HMACSHA256(SqlString value, SqlString Key)

Fix to show the correct message responsed.

SQL-APIConsumer

Tested version