Question : Is there a way to do a Direct SQL write using AFCs

Unanswered Question
Nov 17th, 2011

We are looking to integrate a service that will pull values from an external database table. Also, we would like another service to be able to update those database table values.

We are looking to integrate a service that will pull  values from an external database table.   Also, we would like another service to be able to update those database table values.  Example.    Service for ordering a Blackberry, depending on some values selected (type, model, carrier, etc) would then be able to have a pricing structure pulled in from an external table.   We would like for the non technical people to be given access to another service, with which they could simply enter the data via submitting a requisition that would update the table with the data that the previous service would then be able to access.

I had a few thoughts:  ISF code to executing a SQL Update, a service Link Integration that would push data to an external webservice that would then update the table,  but I am specifically asking if the OOTB AFCs can be levearagted using congured datasource to write to that table instead of just read from it.

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Average Rating: 0 (0 ratings)
Brian Papa Thu, 11/17/2011 - 18:08

Here is a untested thought for SQL Server.  You might be able to wrap the update code with a function and call it from a DDR via a select statement. This tricks gets around the limitation that you can't execute a stored procedure through a DDR, at least I haven't figured out how.

SELECT * FROM fn_updateMyTable (#your_form.yourupdatefield#, #your_form.yoursearchkey#) 

#yourfield# is the data that you want to update. and yoursearchkey is the row to match

Then you just write a sql function to do the update(obviously a trivial example below, probably with typos).  Updates are probably a little tricky since you need to depend on the searchkey value  matching a unique value in your table.  You might be better off doing and insert to a staging table then writing a trigger on insert

 

Create function fn_updateMyTable
(@searchkey varchar(10),
 @updatedata varchar(10)
 )
returns 
@RtnTABLE  TABLE
  (result  varchar(30)
)
as
BEGIN

  update MyTable set myfield = @updatedata where mykey = @searchkey
 
insert into @RtnTABLE  VALUES ("returnvalue")

  RETURN
END

 

 

Chris Morris Thu, 11/17/2011 - 18:08
  • You could do a javascript call to some web services that were designed to do this.
  • You could create an Agent that uses the SQL Adapter to make the update and set that as one of the steps in the plan.

 

Actions

Login or Register to take actions

This Discussion

Posted November 17, 2011 at 6:07 PM
Stats:
Replies:3 Avg. Rating:
Views:214 Votes:0
Shares:0

Related Content