consume a web service from a stored procedure

[Cameron Johnson]

Hi, all.

I'm trying to hit a remote web service from within a SQL stored procedure. I don't need any data back; it's just a call to initiate another process.

If I were doing this in CF, it would be:

<cfinvoke webservice="http://www.domain.com/webservices/webservice.asmx" method="AddUser" ID="#ID# />


Is there a way to do this from within a stored procedure? I've seen some examples using sp_OACreate and sp_OAMethod, but it seems overkill for what I'm trying to accomplish. And, I'm having a hard time deconstructing the MSDN documentation.

Thanks for the help/tips/info/other.

Cameron


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3222
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Cameron Johnson]

As a follow-up, I can also hit the web service with an HTTP GET request:

http://www.domain.com/webservices/webservice.asmx/AddUser?ID=#ID#

Is there a straightforward way of doing this in an SP?

Cameron

> Hi, all.
>
> I'm trying to hit a remote web service from within a SQL stored
> procedure. I don't need any data back; it's just a call to initiate
> another process.
>
> If I were doing this in CF, it would be:
>
> <cfinvoke webservice="http://www.domain.com/webservices/webservice.
> asmx" method="AddUser" ID="#ID# />
>
>
> Is there a way to do this from within a stored procedure? I've seen
> some examples using sp_OACreate and sp_OAMethod, but it seems overkill
> for what I'm trying to accomplish. And, I'm having a hard time
> deconstructing the MSDN documentation.
>
> Thanks for the help/tips/info/other.
>
> Cameron

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3223
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Peter Boughton]

Huh?

Stored procedures are for database logic, not web application logic
(which is what this appears to be).

Do it in CF.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3224
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Cameron Johnson]

I wish I could. But the application that is processing the request is (a) a closed, third-party app that is (b) written in .NET.

What we do have control over is the validation stored procedure that occurs after a person submits the form. The SP is doing normal database logic first - check for duplicate records in table X, add a new row to table Y, etc.. When all that's done, I need to fire a quick request to the remote web service.

Cameron



>Huh?
>
>Stored procedures are for database logic, not web application logic
>(which is what this appears to be).
>
>Do it in CF.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3225
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Adrian Moreno]

I don't get the problem. Create a Service Object (CFC) with a function that
handles this process.

Submit to your action page, send the form struct into the function. Inside
the function, call the stored procedure & pass in the form data. Have the
SP return the new ID and a bit value indicating that this is a new record.
If you call the web service when a new record is created, then do that,
otherwise, skip it and return a boolean value or the ID from the new record
from the function to whatever process called the function.

-- Adrian

----------------------------------------
From: "Cameron Johnson" <johnson@...>
Sent: Thursday, May 28, 2009 4:05 PM
To: "sql" <sql@...>
Subject: Re: consume a web service from a stored procedure

I wish I could. But the application that is processing the request is (a) a
closed, third-party app that is (b) written in .NET.

What we do have control over is the validation stored procedure that occurs
after a person submits the form. The SP is doing normal database logic
first - check for duplicate records in table X, add a new row to table Y,
etc.. When all that's done, I need to fire a quick request to the remote
web service.

Cameron

>Huh?
>
>Stored procedures are for database logic, not web application logic
>(which is what this appears to be).
>
>Do it in CF.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3226
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[OLHAUSJ]

You could have the stored proc write a row to a table, and run a CF
scheduled task every few minutes to look for that row.  When it finds it,
have the scheduled task send the web service request.




From:
Cameron Johnson <johnson@...>
To:
sql <sql@...>
Date:
05/28/2009 04:05 PM
Subject:
Re: consume a web service from a stored procedure




I wish I could. But the application that is processing the request is (a)
a closed, third-party app that is (b) written in .NET.

What we do have control over is the validation stored procedure that
occurs after a person submits the form. The SP is doing normal database
logic first - check for duplicate records in table X, add a new row to
table Y, etc.. When all that's done, I need to fire a quick request to the
remote web service.

Cameron



>Huh?
>
>Stored procedures are for database logic, not web application logic
>(which is what this appears to be).
>
>Do it in CF.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3227
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Cameron Johnson]

>I don't get the problem. Create a Service Object (CFC) with a function that
>handles this process.
>
>Submit to your action page, send the form struct into the function. Inside
>the function, call the stored procedure & pass in the form data. Have the
>SP return the new ID and a bit value indicating that this is a new record.
>If you call the web service when a new record is created, then do that,
>otherwise, skip it and return a boolean value or the ID from the new record
>from the function to whatever process called the function.
>
>-- Adrian
>
>I wish I could. But the application that is processing the request is (a) a
>closed, third-party app that is (b) written in .NET.
>
>What we do have control over is the validation stored procedure that occurs
>after a person submits the form. The SP is doing normal database logic
>first - check for duplicate records in table X, add a new row to table Y,
>etc.. When all that's done, I need to fire a quick request to the remote
>web service.
>
>Cameron
>
>>Huh?
>>
>>Stored procedures are for database logic, not web application logic
>>(which is what this appears to be).
>>
>>Do it in CF.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3228
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Cameron Johnson]

> Submit to your action page, send the form struct into the function.

I don't have access to the form or the action page. Those are part of the proprietary, third-party software that runs our e-commerce. What I can do is add pre- and post-process SPs to validate data, fire off other SPs, etc.

Being a CF developer first, I'd prefer to create a CFC or a simple CFM page to do the web service call. But the higher problem still exists: what do I put into the SP to initiate the call? Or, put differently, how do I communicate to a web app from an SP?

More searching turned up this example:
http://blog.davebouwman.com/index.php/2008/07/call-geonames-web-services-from-a-sql-stored-procedure/

I was hoping someone on this list had done something similar.

Cameron


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3229
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[listman]

Why not run a DTS/SSIS job from a stored proc that hits a
webpage/webservice?

Cameron Johnson wrote:

> Hi, all.
>
> I'm trying to hit a remote web service from within a SQL stored procedure. I don't need any data back; it's just a call to initiate another process.
>
> If I were doing this in CF, it would be:
>
> <cfinvoke webservice="http://www.domain.com/webservices/webservice.asmx" method="AddUser" ID="#ID# />
>
>
> Is there a way to do this from within a stored procedure? I've seen some examples using sp_OACreate and sp_OAMethod, but it seems overkill for what I'm trying to accomplish. And, I'm having a hard time deconstructing the MSDN documentation.
>
> Thanks for the help/tips/info/other.
>
> Cameron
>
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3230
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6