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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.