ReadabilityDynamically adding columns to a database query using ColdFusion
The following example shows how you can dynamically add a column to a database query in ColdFusion using the queryAddColumn()
function.
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
And the output of the <CFDUMP> tag is as follows:
query
[Record # 1]
ARTISTID: 1
FIRSTNAME: Aiden
FULLNAME: DONOLAN, AIDEN
LASTNAME: Donolan
[Record # 2]
ARTISTID: 2
FIRSTNAME: Austin
FULLNAME: WEBER, AUSTIN
LASTNAME: Weber
[Record # 3]
ARTISTID: 3
FIRSTNAME: Elicia
FULLNAME: KIM, ELICIA
LASTNAME: Kim
[Record # 4]
ARTISTID: 4
FIRSTNAME: Jeff
FULLNAME: BACLAWSKI, JEFF
LASTNAME: Baclawski
[Record # 5]
ARTISTID: 5
FIRSTNAME: Lori
FULLNAME: JOHNSON, LORI
LASTNAME: Johnson
You can also populate an array object and pass it to the queryAddColumn()
function, as seen in the following example:
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
Or you can pre-size the array object and set the column value using the arrayResize()
and arraySet()
functions, as seen in the following examples:
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
The following example shows how you can dynamically add a column to a database query in ColdFusion using the queryAddColumn()
function.
<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
</cfquery>
<cfset queryAddColumn(getArtists, "fullName", ArrayNew(1)) />
<cfloop query="getArtists">
<cfset querySetCell(getArtists, "fullName", uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME), getArtists.currentRow) />
</cfloop>
<cfdump var="#getArtists#" metaInfo="false" format="text" /> |
<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
</cfquery>
<cfset queryAddColumn(getArtists, "fullName", ArrayNew(1)) />
<cfloop query="getArtists">
<cfset querySetCell(getArtists, "fullName", uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME), getArtists.currentRow) />
</cfloop>
<cfdump var="#getArtists#" metaInfo="false" format="text" />
And the output of the <CFDUMP> tag is as follows:
query
[Record # 1]
ARTISTID: 1
FIRSTNAME: Aiden
FULLNAME: DONOLAN, AIDEN
LASTNAME: Donolan
[Record # 2]
ARTISTID: 2
FIRSTNAME: Austin
FULLNAME: WEBER, AUSTIN
LASTNAME: Weber
[Record # 3]
ARTISTID: 3
FIRSTNAME: Elicia
FULLNAME: KIM, ELICIA
LASTNAME: Kim
[Record # 4]
ARTISTID: 4
FIRSTNAME: Jeff
FULLNAME: BACLAWSKI, JEFF
LASTNAME: Baclawski
[Record # 5]
ARTISTID: 5
FIRSTNAME: Lori
FULLNAME: JOHNSON, LORI
LASTNAME: Johnson
You can also populate an array object and pass it to the queryAddColumn()
function, as seen in the following example:
<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
</cfquery>
<cfset col = arrayNew(1) />
<cfloop query="getArtists">
<cfset arrayAppend(col, uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME)) />
</cfloop>
<cfset queryAddColumn(getArtists, "fullName", col) />
<cfdump var="#getArtists#" metaInfo="false" format="text" /> |
<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
</cfquery>
<cfset col = arrayNew(1) />
<cfloop query="getArtists">
<cfset arrayAppend(col, uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME)) />
</cfloop>
<cfset queryAddColumn(getArtists, "fullName", col) />
<cfdump var="#getArtists#" metaInfo="false" format="text" />
Or you can pre-size the array object and set the column value using the arrayResize()
and arraySet()
functions, as seen in the following examples:
<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
</cfquery>
<cfset col = arrayNew(1) />
<cfset arrayResize(col, getArtists.recordCount) />
<cfloop query="getArtists">
<cfset idx = getArtists.currentRow />
<cfset arraySet(col, idx, idx, uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME)) />
</cfloop>
<cfset queryAddColumn(getArtists, "fullName", col) />
<cfdump var="#getArtists#" metaInfo="false" format="text" /> |
<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
FROM ARTISTS A
</cfquery>
<cfset col = arrayNew(1) />
<cfset arrayResize(col, getArtists.recordCount) />
<cfloop query="getArtists">
<cfset idx = getArtists.currentRow />
<cfset arraySet(col, idx, idx, uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME)) />
</cfloop>
<cfset queryAddColumn(getArtists, "fullName", col) />
<cfdump var="#getArtists#" metaInfo="false" format="text" />