asked on

Using dbAMP Refresh stored procedure in Coldfusion

Hello all,

Running into an issue trying to get dbAMP working with Coldfusion. I have been using CF for a while now but have yet to have to use a stored procedure, i normally just query the tables etc. We decided to go with dbAMP to integrate our salesforce info and we got it installed and operatinf just fine in MS SQL. All our test stored procs work fine as well as the linked server.

So now comes the time when i want to extend the use and fire these stored procs from CF but cant seem to figure out how to to do it since dbAMP uses a linked server. The follow code snippet shows how I run a proc to pull down some Account info into SQL.

exec sf_refresh 'SALESFORCE' , 'Account'

Open in new window

Pretty simple the proc sf_refresh runs on the linked server SALESFORCE and targets the object Account.

My question is how would I fire this from coldfusion?

Thanks in advance for any help getting this going!

BlueYonder

If you are trying to refresh into coldfusion, it is not possible, since refresh and replicate copy into a table with stored procedures which are loaded during the dbamp install. If you want to pull data directly use SalesForce...[Account] or pull from the account table used for the sync from DBAmp.

joedfuse

ASKER

I'm sorry I'm not understanding what your saying about the dbamp install. I can open SQL manager and use the code above on any object and refresh data. Not just during an install. Are you saying cold fusion can not execute the sf_refresh stored procedures ? <cfstoredproc> doesn't allow the linked server or objet name so I am a bit confused.

ASKER CERTIFIED SOLUTION

LajuanTaylor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joedfuse

ASKER

You Rock!

Looks like the stored proc fired, well I didnt get any errors. I didnt even think to use the param to output the linked server and object.

Now one more thing so i can actually use the results. I cant seem to get the results attribute to work so i can create actions based on if anything gets updated by the proc. The text below shows whats returned after the stored proc executes in ms sql manager. I want to be able to use some of whats returned here to create more actions. I dont need you to get too deep into doing any additional actions just how to access the return info.

Here is whats reurned in sql

--- Starting SF_Refresh for Account
11:24:56: Using last run time of 2015-04-24 10:53:00
11:24:57: Identified 0 updated/inserted rows.
11:24:57: Identified 0 deleted rows.
--- Ending SF_Refresh. Operation successful.

What I think should happen is to load the results into a variable or something and parse through it but I can seem to get started doing this.

Thanks again

joedfuse

ASKER

Here is what the code looks like working ... I got the status code to work not not displaying any of the result inf. I get status code '0'

<cfstoredproc procedure="sf_refresh" result="result_txt" returncode="yes" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">    

	<cfprocresult name="proc_results"> 
    
    <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="SALESFORCE">
      
     <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="Account">  
</cfstoredproc>
<html>
<head>
<meta charset="utf-8">
<title>Test Stored Proc</title>
</head>

<body>
<cfoutput>
The return code for the stored procedure is: '#result_txt.statusCode#'<br> 
</cfoutput>
</body>
</html>

Open in new window

LajuanTaylor

What do you get if you dump out the "proc_results"?

<cfstoredproc procedure="sf_refresh" result="result_txt" returncode="yes" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">
    <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="SALESFORCE">
      
     <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="Account">
     <cfprocresult name="proc_results">    
</cfstoredproc>

<cfdump var="#proc_results#">

Open in new window

joedfuse

ASKER

Variable PROC_RESULTS is undefined.

The error occurred in line 25
23 : </cfoutput>
24 : <hr>
25 : <cfdump var="#proc_results#">
26 : </body>
27 : </html>

LajuanTaylor

Try this. Note, I added the "resultset" attribute.

<cfstoredproc procedure="sf_refresh" result="result_txt" returncode="yes" datasource="#request.dsn#" username="#request.dsnu#" password="#request.dsnp#">
    <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="SALESFORCE">
      
     <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="Account">
      <cfprocresult name="proc_results" resultset="1">
</cfstoredproc>

<cfdump var="#proc_results#">

Open in new window

joedfuse

ASKER

Same error but I think it has to do with the line one result="result_txt" name. Ami correct in thinking that this has to line up with the resultset?

LajuanTaylor

@joedfuse - Here's a wiki link to documentation on "cfprocresult".
https://wikidocs.adobe.com/wiki/display/coldfusionen/cfprocresult

joedfuse

ASKER

Would adding a type="OUT" parameter to the stored proc help create a set of info to use?

LajuanTaylor

@joedfuse - Yes, change code so that it matches "result_txt".

joedfuse

ASKER

Ok so i get

struct
CACHED      false
EXECUTIONTIME      1635
STATUSCODE      0

The return code for the stored procedure is: '0'

LajuanTaylor

@joedfuse - "STATUSCODE 0" means that the stored proc executed successfully. If you want more detail, you could add some logic on the ColdFusion side in case the returned status is not successful.

LajuanTaylor

@joedfuse - Good luck on the rest of your project.