p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   how to send http post request via stored procedure (http://p2p.wrox.com/showthread.php?t=35035)

kissvineet November 11th, 2005 02:11 AM

how to send http post request via stored procedure
 
Hello Sir

i am a beginner level programmar. i have got a task and need help on
that. Plz Help me.

i have to send a http post request from a stored procedure to a URL.
and also from that stored procedure i have to send XML Data to the URL.

i have following XML Info.

<ERECHARGE>
          <TRANS_ID>Vendor's Transaction ID </TRANS_ID>
          <SOURCE_ID> Vendor's Name </SOURCE_ID>
          <MDN> Vendor's MDN </MDN>
          <DEALER_ID> Vendor's Dealer ID </DEALER_ID>
          <REQUEST>
                      <REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
                      <ATTRIBUTES>
                                 <ATTRIBUTE>
                                            <NAME>TARGET_MDN</NAME>
                                            <VALUE>MDN where recharge
amount to be transferred </VALUE>
                                 </ATTRIBUTE>
                                 <ATTRIBUTE>
                                           <NAME>AMOUNT</NAME>
                                           <VALUE>Recharge
Amount</VALUE>
                                 </ATTRIBUTE>
                                 <ATTRIBUTE>
                                            <NAME>PIN</NAME>
                                            <VALUE>Unique PIN given to
distributor on registration</VALUE>
                                </ATTRIBUTE>
                      </ATTRIBUTES>
          </REQUEST>
</ERECHARGE>

i have to send this information to a URL via a stored procedure.

i have to use stored procedure because we also have to store these
information in a table.

also, we have to store the response from that URL.

now, I am asking about what approach i should take. and how to solve
this problem.

i will be very thankful to you.

thanx



robprell November 12th, 2005 12:38 AM

I think you have to have a browser to get a url. The only way I can see doing this if I follow the question is to shell out to the command prompt launch a browser with the xml data submitted to the url. SQL can create xml but it expects some other thing to talk on the web for it. Can't you have an asp page or ??? call the stored proc, get the xml, then have the web page hit your url? I don't follow how you plan to use just SQL are you using other programming tools?


kissvineet November 12th, 2005 05:39 AM

hi

i think we can post http request from SQL Server. i have done it. Now the only issue is to post XML Data.



robprell November 14th, 2005 01:47 PM

Perhaps you can teach me. How do you post a http request from sql without executing something from a command prompt?


Papak March 29th, 2006 07:42 AM

Http Post can be done from Sql Server 2000 with Stored Procedure. I have done and implemented it .

Here is the Stored Procedure :


CREATE procedure HTTP_POST( @sUrl varchar(200), @response varchar(8000)
out)
As


Declare
@obj int
,@hr int
,@status int
,@msg varchar(255)


exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
-- exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end


exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end


exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded'
if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto
eh end


exec @hr = sp_OAMethod @obj, send, NULL, ''
if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end


exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto
eh
end


if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +
str(@status) goto eh end


exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto
eh end


exec @hr = sp_OADestroy @obj
return
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
GO


I think it will he helpful to you.

Thanks
Papak


Papak Mondal
Senior Developer
http://www.autocastle.com

robprell April 5th, 2006 05:38 PM

From what I can see..... You are going through the SQL server to execute code that does a post (sends web data but doesn't look at the web servers response). If you want to get web data it will have to be on port 80, SQL won't listen on this port for you. You can possibly shell out to a command prompt and run some text based web browser and possibly capture the results of the text based browser???

Just a thought.

This thread intrigues me. If you get this to work please post how.

Thanks,
Rob



alicemark August 18th, 2008 12:26 AM

Paessler URL Recorder helps to find out the URLs and POST DATA strings that a user sends to a web server while surfing a sequence of URLs. It works like a standard web browser, whereby you enter a URL at the top of the application then click "Go". You can then use the mouse to surf through the sequence you want to record. While you are accessing one page after the other, the URL and - if you submit a POST request - the POST DATA, are stored in the list at the bottom of the window. Once you have finished recording your URLs or POST DATA you can save the list of URLs to CSV and HTML, copy the list to the clipboard and copy individual URLs or POST DATA to the clipboard.
----------
Alicemark


All times are GMT -4. The time now is 08:19 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.