http post debug - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

http post debug

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-09 : 07:58:52

I have the following code below.
It takes a url and posts and then retrieves the response.

It works fine except the response is not returned. Any ideas on what I need to fix to return the response.


CREATE procedure HTTP_POST
( @sUrl  varchar(2000)
, @response varchar(8000) output
, @status int output )
As

Declare @obj int
declare @hr int

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
-- exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr <> 0 begin 
  set @status = -1 
  set @response = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed'
  return 
end

exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin 
  set @status = -2
  set @response = 'sp_OAMethod Open failed' 
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded'
if @hr <>0 begin 
  set @status = -3
  set @response = 'sp_OAMethod setRequestHeader failed' 
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAMethod @obj, send, NULL, ''
if @hr <>0 begin 
  set @status = -4
  set @response = 'sp_OAMethod Send failed' 
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <>0 begin 
  set @status = -5
  set @response = 'sp_OAMethod read status failed' 
  exec @hr = sp_OADestroy @obj
  return
end

if @status <> 200 begin 
  set @response = 'sp_OAMethod http status ' + cast(@status as varchar(20) ) 
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <>0 begin 
  set @status = -6
  set @response = 'sp_OAMethod read response failed' 
  exec @hr = sp_OADestroy @obj
  return
end

exec @hr = sp_OADestroy @obj
return
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-09 : 08:24:03

try setting the content-length header also.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-09 : 09:16:14

can you explain to me what you mean?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-09 : 09:19:53

declare @len int
set @len = len(@sUrl)
exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Length', @len
if @hr <>0 begin
set @status = -3
set @response = 'sp_OAMethod setRequestHeader failed'
exec @hr = sp_OADestroy @obj
return
end

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-11 : 01:51:48

if i do this i get an error

sp_OAMethod setRequestHeader failed

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-12 : 02:07:40

does anyone have any idea what i can do to debug this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-12 : 05:43:32

to what page are you trying to post this?
is it php or asp?

there was a discussion here about this not working for posts to php pages a while ago.

when i tried to do the same thing i randomly sometimes got the respose and sometimes not.
so i wouldn't count this as very reliable method.
what are you trying to do? maybe it could be done in a different way.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-06-12 : 07:50:50

This is interesting, i never knew u could execute the XMLHTTP object via a stored proceedure, we use this regularly in ASP pages. but this is awesome...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-12 : 08:28:15

yes it can be done, but i find it unreliable.

this the code i use


create proc spHTTPRequest
	@URI varchar(2000) = '',
	@methodName varchar(50) = '',
	@requestBody varchar(8000) = '',
	@responseText varchar(8000) output
as
SET NOCOUNT ON
IF 	@methodName = ''
BEGIN
	select FailPoint = 'Method Name must be set'
	return
END 
set 	@responseText = 'FAILED'

DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
EXEC 	@hResult = sp_OACreate 'MSXML2.ServerXMLHTTP.4.0', @objectID OUT
--EXEC 	@hResult = sp_OACreate 'Microsoft.XMLHTTP', @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'
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'
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: Content-Type',
		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: Content-Length',
		MedthodName = @methodName
	goto destroy
	return
END

-- send the request
select 	@requestBody
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
exec sp_OAGetProperty @objectID, 'responseText', @responseText out 
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
go

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-06-12 : 08:37:10

Why do you say unreliable.

We use it a lot, for web 2phone sms and domain name search.

...Also if other server side scripts, search, or posts need to be done, we use it.

It has proven to be extremely reliable for ASP pages for us. But with Stored Proceedures, this is new...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-12 : 08:42:10

i mean it's use from sql server is unreliable.
at least in my experience.

from asp it's ok. we use that too.