http call embedded in stored proc
-
So i have a sql sript that i'm converting to a proc. Theres a chunk of sql that runs then the end user is supposed to goto an asp page that when opened has some inline sql that updates the db. Step one for me is to convert this to a proc. Once done i'll swing back and pull the sql out of the asp page and throw it into my proc accordingly.
Long story short is there a way to make an http call in sql stored proc? Was going to use bat file but i have to assume there's a better way.
i just need to embed http://www.helloWorld.asp in my stored proc
Make sence?
-
I had to do this very recently (last few days), and I ended up creating the stored procedure as a CLR. I wasn't sure how I felt about it at first, but it ended up working really well. I created the CLR in Visual Studio and was able to go from conception to realization in only a few hours.
Here's the code for the CLR ion C# (I ended up making it as a UDF so I could call it for every line):
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ufnFunction(string variable)
{
string str = "";
string cn = "http://server/default.aspx?variable=" + variable;
HttpWebRequest http = (HttpWebRequest)
WebRequest.Create(cn);
http.KeepAlive = false;
HttpWebResponse resp = (HttpWebResponse)http.GetResponse();
Stream receiveStream = resp.GetResponseStream();
Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
StreamReader readStream = new StreamReader(receiveStream, encode);
while (!readStream.EndOfStream)
{
str = str + readStream.ReadLine();
}
readStream.Close();
resp.Close();
return new SqlString(str);
}
};
-
thanks for the solution. unfortunatly my mngr over heard me discussing your sol w/ a teamate and he hixed it on the spot. I appreciate the solution though.
-
sorry. Nixed it on the spot.
-
He nixed the solution or the entire project? Did he give a reason?
--J
-
Nixed the solution. He's big on keeping things as simple as possable for easy debugging purposes. As i mentioned before we're monitoring serveral different apps accross 50 or so sql instances. So i understand where he's coming from. We switch the pager weekly between 5 of us so any backend dev work needs to be easily supported between all 5 of us. I'll prob end up createing a vbs that makes the http request and call vbs via .bat file in my proc.
I can't complain. We've finally moved all sql 2000 instances to 2005 or 2008. I'm really enjoying being away for 2000 thats for damn sure.
-
I'd argue that calling a VBS from the command line is much less manageable than using CLR. Calling VBS from the command line introduces points of failure that don't exist in CLR, and it also introduces a possible security hole. Unless you already have the operating system extension enabled, I suggest you keep them disabled. CLR is much more secure.
The CLR doesn't have to be in C# either, it can be in VB. And, as you see, it's very short. Personally, I keep my CLRs under source control, and they are very easy to manage. Once deployed, I don't usually have problems with them at all...
It's ultimately up to you and your boss what you do, but personally I'd try to make the case for the CLR. Relying on the operating system seems sloppy, especially when the database provided the mechanism to do what you're doing, within the system, without compromising security.
--J
-
Noted. Thanks J.
-
J i've got a question for you regarding deployment;
i created a new VS project named "MyCLRProjects, and added only your Partial Public class as an example;
it compiled, and i was able to run the command to add the assembly:
CREATE ASSEMBLY MyCLRProjects from 'C:\data\MyCLRProjects.dll' WITH PERMISSION_SET = SAFE
when i try to add the udf with the command below, i get this simple error:
Msg 6505, Level 16, State 2, Procedure fn_ReadHttpPage, Line 1
Could not find Type 'UserDefinedFunctions' in assembly 'MyCLRProjects'.
the code:
CREATE FUNCTION fn_ReadHttpPage(@pageToRead varchar(250))
RETURNS varchar(max)
EXTERNAL NAME MyCLRProjects.UserDefinedFunctions.ufnFunction
can you point out my obvious error? i'm sure my Root namespace is simply "MyCLRProjects"
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That's definitely strange. Personally, I deployed mine from VS, but I've deployed CLRs manually before, and didn't have any such problem.
If I script out my function, it looks mostly identical to your SQL script:
CREATE FUNCTION [dbo].[ufnFunction](@variable [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerCLR].[UserDefinedFunctions].[ufnFunction]
So I'm not sure what's going on. Have you tried deploying from VS? Before this project, I had never tried deploying from VS before, but I tried it and it was pain-free. Might be worth a shot since the manual way is giving you trouble...
I'm not sure if this will make a difference, but the permission level will need to be External. I had to change that in the project properties before deploying.
--J
-
You will also need to:
ALTER DATABASE <dbname>SET TRUSTWORTHY ON
For the database you are deploying the assembly to.
-
J that got me looking in the right direction;
for whatever reason, my dll's class name is a little weird; i used hte deploy option, and then when i scripted the function it created out, it looks like htis: notice the middle name has a period in it...
CREATE FUNCTION [dbo].[Function1]()
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MyCLRProject].[MyCLRProject.UserDefinedFunctions].[Function1]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Oddly enough, I was looking for a similar thing today. I found the following. Not sure it applies here but might be helpful in what to look for...
Declare @Object as Int;Declare @ResponseText as Varchar(8000);
--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'http://www.lmgtfy.com/?q=Jeff+Moden', --Your Web Service Url (invoked)
'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText
Exec sp_OADestroy @Object
I also have to warn you that the sp_OA* methods reportedly had some memory leaks in the past. They've supposedly been fixed but I've not confirmed that in testing.
I'll probably catch hell from other forum members but tell him I like his line of thought. "KISS".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
J this thread was the most valuable thing of the week for me; i learned a lot. thank you very much.
I had to change your udf very slightly; i converted it to vb syntax, which is minor, but by making it return SQLChars instead of SQLString, it will map to a varchar(max), instead of raising a truncation issue on some pages:
i was testing it to just return the entire http response of a page, so google.com returns more than 800 chars.
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function ufnFunction(ByVal variable As String) As SqlChars
Dim str As String = ""
Dim cn As String = variable
Dim http As HttpWebRequest = DirectCast(WebRequest.Create(cn), HttpWebRequest)
http.KeepAlive = False
Dim resp As HttpWebResponse = DirectCast(http.GetResponse(), HttpWebResponse)
Dim receiveStream As Stream = resp.GetResponseStream()
Dim encode As Encoding = System.Text.Encoding.GetEncoding("utf-8")
Dim readStream As New StreamReader(receiveStream, encode)
While Not readStream.EndOfStream
str = str + readStream.ReadLine()
End While
readStream.Close()
resp.Close()
Return New SqlChars(str)
End Function
End Class
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Great Lowell, I'm glad I could help. For my requirements, 4000 characters was more than enough. I'm glad you were able to manipulate it to meet your needs.
--J
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply