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!
joedfuseAsked:
Who is Participating?
 
LajuanTaylorCommented:
Try something like the following assuming sf_refresh a stored procedure that's defined in your SQL database. Note, the snippet is structured to only execute your stored proc and pass in the hard coded parameters.

<cfstoredproc procedure="sf_refresh" datasource="#YourSQLDataSource#">    
    <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="SALESFORCE">
      
     <cfprocparam  
      cfsqltype="cf_sql_varchar"
      value="Account">  
</cfstoredproc>

Open in new window

0
 
BlueYonderCommented:
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.
0
 
joedfuseAuthor Commented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
joedfuseAuthor Commented:
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
0
 
joedfuseAuthor Commented:
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

0
 
LajuanTaylorCommented:
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

0
 
joedfuseAuthor Commented:
Variable PROC_RESULTS is undefined.

The error occurred in line 25
23 : </cfoutput>
24 : <hr>
25 : <cfdump var="#proc_results#">
26 : </body>
27 : </html>
0
 
LajuanTaylorCommented:
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

0
 
joedfuseAuthor Commented:
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?
0
 
LajuanTaylorCommented:
@joedfuse - Here's a wiki link to documentation on "cfprocresult".
https://wikidocs.adobe.com/wiki/display/coldfusionen/cfprocresult
0
 
joedfuseAuthor Commented:
Would adding a type="OUT" parameter to the stored proc help create a set of info to use?
0
 
LajuanTaylorCommented:
@joedfuse - Yes, change code so that it matches "result_txt".
0
 
joedfuseAuthor Commented:
Ok so i get

struct
CACHED      false
EXECUTIONTIME      1635
STATUSCODE      0

The return code for the stored procedure is: '0'
0
 
LajuanTaylorCommented:
@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.
0
 
LajuanTaylorCommented:
@joedfuse  - Good luck on the rest of your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.