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
Overall 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),
[email protected] varchar(10)
  (result  varchar(30)

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




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.



This Discussion

Related Content