query to cfscript properly in coldfusion?

Question

CLARIFICATION: The question here is, "how do I stop using <cfquery> to retrieve data and replace it with a call to an API?"

I have a sql query where I need help to convert it to cfscript. However as you can see I have tried converting it. But, I need some verification if I am on the right path to convert from sql query to cfscript. if not, can anyone help me by converting from sql query to cfscript? thanks for the help. here is my code.

CFSCRIPT:

<cfset jsonDatas = fileRead("c:\Users\Desktop\MyApi.json" )>      
<cfset jsonData = deserializeJSON(jsonDatas) />       
<cfif arrayLen(jsonData)>  
  <cfloop array="#jsonData#" index="prop">       
    <cfoutput>  
      <cfscript>  
        // writedump(jsonData);for (item in jsonData[1])   {  
          if (#prop.payGrade# == 0) {
                 #prop.divisionNbr#;        
         #prop.probationBeginDate#;        
         #prop.legacyStatus#;        
         #prop.payStep#;        
         #prop.creationDate#;           
          } 

Answer 1

You are asking about two different things.

• SQL defines the query run on the database.
• CFSCRIPT is just a script style syntax for ColdFusion code (as opposed to tags).

Your example code is

• Here is some SQL
• Here I am reading JSON and converting it to a ColdFusion struct using script style code instead of tags.

Are you trying to go from currently calling a database, which returns data as a ColdFusion query object, to calling an API, which returns data as a JSON packet? You need to then convert the JSON data into the same or similar CF structures you currently use?

UPDATE: If you want to replace the existing query with an API call,

• Does the API already exist?
• Does it return the same data as the current query?
• Is your team moving to APIs to decouple existing code?
• Do you need to know HOW to convert a <cfquery> call to an API call?

Answer 2

I am going to go for broke here. Do you have data in a DB and you just want to return a JSON representation of it? As in

<cfquery name="result">
    SELECT ...
    FROM whereever
</cfquery>

<cfoutput>#SerializeJSON(result, 'struct')#</cfoutput>

And then something else tries to consume this data? I have been looking and your questions, and I don't know if we are on the generating data side of the world, or in the consuming data side of the world.

Answer 3

This is really a comment, but I need a lot of space to write it.

Are you looking to get data from a remote source and display it on a web page. Are you looking to do something like this?

I think we need to clarify which are client side technologies and which are server side technologies.

Update based on comments**

Consider a VueJS solution

<!-- Showing stuff on screen -->
<div id="app">
  {{ info }}
</div>

I can show stuff on the screen. This is similar, but not identical to #info#

<!-- Getting data -->
<cfscript>
new Vue({
  el: '#app',
  data () {
    return {
      info: null
    }
  },
  mounted () {
    axios
      .get('https://api.coindesk.com/v1/bpi/currentprice.json')
      .then(response => (this.info = response))
  }
})
<cfscript>

And this is getting data from a remote source and putting that data into a javascript variable.

Explaination

So why am I doing this with Javascript rather than ColdFusion? Javascript runs on the browser; ColdFusion runs on the server. If you want to consume an API on the browser, you have to use browser based technologies.

My example is in VueJS, but Angular and React are also options. It is a bit dated, but jQuery can do this kind of stuff too.

Code Source: https://v2.vuejs.org/v2/cookbook/using-axios-to-consume-apis.html