October 10, 2018 at 10:01 pm
Comments posted to this topic are about the item HTTP Requests Using SQLCLR
October 11, 2018 at 12:27 am
Hello, interesting approach, Eilert!
There are a couple errors in the script though:
1. The parameter @convertResponseToBas64 is missing (it's not optional in the C# code).
2. There is a typo in the first query (missing s in the variable name):
FROM OPENJSON(@reponse_json)
I would also suggest implementing some exception handling in the C# library. If an exception occurs (for instance, invalid URL, authentication problem, invalid request, etc.) the function will raise an exception and it won't return any response as it should according to the HTTP protocol specification.
Other than that, really nice work!
October 11, 2018 at 7:32 am
Thanks for the great Feedback, Sergio!
Late last night I noticed that I missed the @convertResponseToBase64 parameter and submitted a fix for the article. I also just now submitted a fix for the other issue you pointed out.
Since I first submitted this article I've made some changes to the function I would like to post soon. Notably, I've added some error handling and I also changed the parameters to include a generic "options" parameter to pass in the things like timeout, base64 encoding, etc. Thought being if more features are added in over time and it requires adding new parameters that will break existing code since SQL Server requires all parameters to be specified when calling a function. A single "options" parameter that allows you to pass in various features you'd like to utilize solves that issue.
October 11, 2018 at 7:42 am
It has long been best practice to firewall databases from the internet. Both SSIS and SqlCLR are proscribed by most corporate governance practices from internet connections altogether, proxies block outbound traffic altogether. Ideally WSUS servers handle service packs and update installations in order to protect the database. Sacrificial app servers are much better suited handling httpwebrequests.
October 11, 2018 at 9:53 am
This is fun and interesting as far as examples go, but the overall idea of integrating Web API calls in database implementation may do more harm than good. There are levels of "safety" for CLR code and I am almost sure HTTP libraries will require CLR integration as unsafe. That in itself is not bad but has pottential for harmful implementations. Again, interesting piece of code, nice writting, good job! Just understand the implications from an architectural standpoint, and be careful.
October 11, 2018 at 10:15 am
This is a very interesting topic. CLR integration may not necessarily allowed in production most of the cases, but CLR integration can be of great help in non-production environments, such as importing data from websites, or utilizing the external resources (computing some complex stuff and getting back data etc).
There can be chances that we integrate the internal websites with our internal T-SQL solutions for company internal applications. So in short, I am always a big fan of CLR integration which provides more opportunities for us to choose.
Good article Eilert !
October 11, 2018 at 1:59 pm
Good stuff!
By the way if I copy/paste your code to SSMS I receive: Msg 319, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
October 11, 2018 at 8:50 pm
Indeed, some policies (especially in larger corporate environments) may to some extent block this method. For those that allow it this can be a very convenient tool, though!
This does fall into the "UNSAFE" category, but then all CLR in starting in SQL Server 2017 is considered UNSAFE. See CLR strict security: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security
Additionally, I was careful to do some homework on this one and find out how to "properly" sign the assembly so I wouldn't need to do things like set TRUSTWORTHY on the database.
CLR, just like other excellent features of SQL Server like dynamic sql, xp_cmdshell, OPENQUERY, etc. need to be handled with care!
October 11, 2018 at 8:52 pm
francesco.mantovani - Thursday, October 11, 2018 1:59 PMGood stuff!
By the way if I copy/paste your code to SSMS I receive:Msg 319, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Which version of SQL Server are you using? If it's prior to SQL Server 2016, then the examples using "OPENJSON(...) WITH (...) will not work since that JSON support was introduced in SQL Server 2016.
Also, thanks everyone for all the kind words!
October 11, 2018 at 8:53 pm
SQL Server 2016:
Microsoft SQL Server Management Studio 13.0.16106.4
Microsoft Analysis Services Client Tools 13.0.1700.441
Microsoft Data Access Components (MDAC) 10.0.17134.1
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.17134.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.17134
October 11, 2018 at 9:02 pm
Just to be out of confusion: OPENJSON is working because my archaic query still works.
Look how horrible it is, with that unhealthy "MSXML2.ServerXMLHTTP.6.0", it looks like an old petrol engine from the early 1900:
Declare @Object as Int;
DECLARE @hr int
Declare @json as table(Json_Table nvarchar(max))
Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json), N'$.elements')
WITH (
[type] nvarchar(max) N'$.type' ,
[id] nvarchar(max) N'$.id',
[lat] nvarchar(max) N'$.lat',
[lon] nvarchar(max) N'$.lon',
[amenity] nvarchar(max) N'$.tags.amenity',
[name] nvarchar(max) N'$.tags.name'
)
EXEC sp_OADestroy @Object
I like you code so much that if I arrive to make it work I'm going to use it on daily basis.
My colleagues and I will jump on the chairs like the monkeys from Space Odyssey, when they see a spaceship arriving on their planet from a remote galaxy.
I hope you understand how trilled I am.
October 11, 2018 at 9:08 pm
Eilert Hjelmeseth - Thursday, October 11, 2018 8:50 PMIndeed, some policies (especially in larger corporate environments) may to some extent block this method. For those that allow it this can be a very convenient tool, though!This does fall into the "UNSAFE" category, but then all CLR in starting in SQL Server 2017 is considered UNSAFE. See CLR strict security: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security
Additionally, I was careful to do some homework on this one and find out how to "properly" sign the assembly so I wouldn't need to do things like set TRUSTWORTHY on the database.
CLR, just like other excellent features of SQL Server like dynamic sql, xp_cmdshell, OPENQUERY, etc. need to be handled with care!
yeah, that's why my horrible script you can see I posted on this page doesn't use xp_cmdshell but sp_OACreate
October 11, 2018 at 9:18 pm
Ha, careful with those chairs!
I was able to pretty quickly take your code and use this function to query it 🙂
October 12, 2018 at 3:26 am
I tried this at home on my SQL Server 2017:
Microsoft SQL Server Management Studio 14.0.17285.0
Microsoft Analysis Services Client Tools 14.0.1016.283
Microsoft Data Access Components (MDAC) 10.0.17763.1
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.17763.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.17763
And I receive the same error.
For instance, what is:[dbo].[clr_http_request]
Do I need to activate SQLCLR first?
I run :EXEC sp_configure 'clr enabled';
EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;
But I still see the 2 errors on line 21 ans 22.
Very kind from you to write the code for me, I copy/pasted it into my SSMS and I receive 1 error on line 9: Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I'm experiencing the same problem on 2 different environment?
Maybe there is something I need to turn ON or OFF?
October 12, 2018 at 5:42 am
For instance, what is:
[dbo].[clr_http_request]
[dbo].[clr_http_request] is the function this article describes. The steps to create the function are noted at the bottom of the article. I'll copy here for convenience. You can also download ClrHttpRequest.zip at the bottom of the article.
To create the function, follow these steps using files found in the attached ClrHttpRequest.zip:
Copy ClrHttpRequest.dll to C:\ on the machine running the SQL Server instanceRun code in clr_http_request.sql to create the function (tweaking as necessary to adjust which database should be used).
Note: This script includes code to change configuration of the SQL Server instance by enabling the CLR integration.
Very kind from you to write the code for me, I copy/pasted it into my SSMS and I receive 1 error on line 9:
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
This error is very strange to me. The article was intiially published with a typo in the examples where it was missing an "s" in @response_json, and for me that was giving this error:
Msg 137, Level 15, State 2, Line 28
Must declare the scalar variable "@reponse_json".
Msg 319, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
However, I'm not able to get any similar error with the code I just provided.
If you have created the [dbo].[clr_http_request] function, what happens when you just run this?
SELECT [dbo].[clr_http_request]('GET', 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema]; );out;', NULL, NULL, 10000, 0, 0)
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply