WinHTTP in Stored Proc

Question

Post reply

WinHTTP in Stored Proc

JuanBob

SSCertifiable

Points: 5059
More actions
July 16, 2007 at 9:32 am

#117776

I am sending data to a POST via a stored proc and using the sp_OA procedures in SQL. I am using WinHTTP. Below is a snippet of code.
EXEC @rc = master.dbo.sp_OACreate 'WinHttp.WinHttpRequest.5.1', @http output
IF   @rc <> 0 GOTO Error
SET @proc = 'Open("POST" , "' + @URL + '", 0 )'
EXEC @rc    = master.dbo.sp_OAMethod @http, @proc
IF   @rc <> 0 GOTO Error
SET @proc = 'SetRequestHeader("HTTP-Version","HTTP/1.1")'
EXEC @rc    = master.dbo.sp_OAMethod @http,@proc
IF   @rc <> 0 GOTO Error
SET @proc = 'SetRequestHeader("Authorization","Basic c2...")'
EXEC @rc    = master.dbo.sp_OAMethod @http,@proc
IF   @rc <> 0 GOTO Error
SET @proc = 'SetRequestHeader("Content-Type","application/x-www-form-urlencoded")'
EXEC @rc    = master.dbo.sp_OAMethod @http,@proc
IF @rc <> 0 GOTO Error
SET @proc = 'Send("' + @post + '")' -- Here's the part I need to encode somehow
EXEC @rc    = master.dbo.sp_OAMethod @http,@proc
if   @rc <> 0 GOTO Error

I can connect and post the data just find, except for sending double-quotes. If the parameter @post has a double-quote, it will not send the data and instead, return an error stating that the ' " ' is not a valid delimiter. We have confirmed that if the @post does have a double-quote, then the transmission is not sent. This error is on my end before it posts anywhere.
Any ideas how to translate that double-quote? Hopefully this was not too vague. If so, let me know and I'll provide more info.
TIA!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

parse you @post and replace the double quotes with "

e.g.

DECLARE @var VARCHAR(50)

SET

@var = 'My "Test"'

SELECT

@var = REPLACE(@var,'"','"')

SELECT

@var

Everything you can imagine is real.

JuanBob SSCertifiable Points: 5059 More actions · Answer 2

Thank you for the reply!

I did try this, along with several other special character encoding. It does not seem to send the data as expected since the POST returns 0 results. Seems like it does not translate once it has been sent, so the POST itself does not see any double-quote, just '"' for instance.

I've run into several articles referencing bugs within WinHTTP, though this process seems straight forward. I'm not doing anything special, just trying to send the double-quote.

The double-quote is necessary since the POST serves as a stepping-stone process for communicating with our mainframe which requires double-quotes as delimiters.

Here is the exact error if I send the double-quote:

Error [-2147211445], While [Send('str1"str2"str3"str4"num1"num2"num3')], Source [ODSOLE Extended Procedure], Description [Traversal string: The '"' delimiter is allowed only in a parameter list.]

EDITED:

When I encode with " " ", I receive the following error. It looks like it is encoding it properly, but I don't understand why it's finding an unclosed "(" delimiter.

Error [-2147211431], While [Send('str1"str2"str3"str4"num1"num2"num3')], Source [ODSOLE Extended Procedure], Description [Traversal string: A '(' delimiter was found with no matching ')'.]

bledu SSChampion Points: 12335 More actions · Answer 3

what happens if you don't include the double qoutes on your send thingy i.e.

SET @proc = 'Send(' + @post + ')'

with an encoded @post as per my earlier example

Everything you can imagine is real.

JuanBob SSCertifiable Points: 5059 More actions · Answer 4

It does not appear to send the data out. I am going to run through a different approach to see what happens. I appreciate the help!

okana SSC Rookie Points: 39 More actions · Answer 5

If you are still watching this - did you ever solve it, and if so, what did you do?

okana SSC Rookie Points: 39 More actions · Answer 6

FYI - for anyone else coming across this - do not try to replace the " as mentioned above - the object does all xml parsing and that causes other issues.

Instead - replace any double quotes with single quotes - and the data will be parsed just fine in the object.

Thank you - that is all - have a great day.