coldfusion - getting array into database

Question

i am attempting to pull data from one website to insert into the database of another website in coldfusion - have gotten the data to the second site - and studied all the documentation i can find on coldfusion struct commands and i just seem to go around in circles -

it is just a simple array that i want to upload into a table - the tables from the two websites are identical -

evidently i need a very simple step by step instruction to understand how to get the data into a value i can upload into the second database - can someone please help

this is the code i used to get the data - and an image of the results - if the image doesn's show the url is http://www.tectrics.com/english/map/getjson.cfm

<cfset urlAddress="http://www.tectrics.com/english/map/localjson.cfm">
<cfhttp url="#urlAddress#" method="GET" resolveurl="Yes" throwOnError="Yes"/>
<cfset meetlist=DeserializeJSON(CFHTTP.FileContent)>
<cfdump var="#meetlist#">

array image

What version of CF and what database? Also, what are the datatypes you'll be inserting? — Shawn
– Shawn, Commented Mar 9, 2020 at 18:51
I finally got your image to load. Are some of those 0s and empty strings supposed to be boolean types? It looks like most of those values should be fairly simple to whitelist, like day or open (if that one is either open or closed, but then it should probably be a different datatype). Though you might also be better off fixing your database to have the correct types that the API is sending you, then convert that data on your INSERT. — Shawn
– Shawn, Commented Mar 9, 2020 at 19:17

user12031119 · Accepted Answer · 2020-03-07 02:48:53Z

You're off to a good start. The contents of your variable meetlist contains an array of structs. So all you'll need to do is loop through the array of structs and perform an insert for each array element. Building off your existing code, here's what you will need.

Some improvements on this code sample should have <cftry>, <cfcatch> and <cftransaction> blocks, but this should be enough to get you started.

<cfset urlAddress="http://www.tectrics.com/english/map/localjson.cfm">
<cfhttp url="#urlAddress#" method="GET" resolveurl="Yes" throwOnError="Yes"/>
<cfset meetlist=DeserializeJSON(CFHTTP.FileContent)>
<cfdump var="#meetlist#"> <!---  Remove this dump --->

<cfloop array="#meetlist#" index="i">
    <!---  each loop iteration will contain a struct called "i". Do an insert for each iteration of the loop --->
    <cfquery datasource="mydatasource">
        INSERT INTO table_name (
            attended, 
            childcare, 
            childfriend,
            ...
        )
        VALUES (
            <cfqueryparam value="#i.attended#" cfsqltype="cf_sql_integer">, <!---  use the appropriate cfsqltype that matches the column's data type --->
            <cfqueryparam value="#i.childcare#" cfsqltype="cf_sql_varchar">,  <!---  use the appropriate cfsqltype that matches the column's data type --->
            <cfqueryparam value="#i.childfriend#" cfsqltype="cf_sql_varchar">,  <!---  use the appropriate cfsqltype that matches the column's data type --->
            ...
        )
    </cfquery>
</cfloop>

James A Mohler · Accepted Answer · 2020-03-07 04:39:30Z

0

I like user12031119's answer. But I would tighten it up some more

<cfset urlAddress="http://www.tectrics.com/english/map/localjson.cfm">
<cfhttp url="#urlAddress#" method="GET" resolveurl="Yes" throwOnError="Yes"/>

<cfscript>
meets = DeserializeJSON(CFHTTP.FileContent);

for(meet in meets) {
    EntitySave(EntityNew("table_name", meet)); 
}
</cfscript>

Entity

I would have to enable ORM and create an Entity definition. Look up ColdFusion Entities and ORM on how to do that

answered Mar 7, 2020 at 4:39

James A Mohler

11.1k15 gold badges51 silver badges76 bronze badges

Collectives™ on Stack Overflow

coldfusion - getting array into database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related