How to get associative arrays notation to assign simple values

Question

I created sample code using the Microsoft SQL Server Northwind demo database. If you don't have access to this demo database here is a simple (MS-SQL) script to create the table and a row of data for this question.

CREATE TABLE [dbo].[Products](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [nvarchar](40) NOT NULL,
    [SupplierID] [int] NULL,
    [CategoryID] [int] NULL,
    [QuantityPerUnit] [nvarchar](20) NULL,
    [UnitPrice] [money] NULL CONSTRAINT [DF_Products_UnitPrice]  DEFAULT (0),
    [UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock]  DEFAULT (0),
    [UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder]  DEFAULT (0),
    [ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel]  DEFAULT (0),
    [Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued]  DEFAULT (0),
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Products] ON 
GO
INSERT [dbo].[Products] ([ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (1, N'Chai', 1, 1, N'10 boxes x 20 bags', 18.0000, 39, 0, 10, 0)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO

Here is the ColdFusion code:

<cfset variables.useTempVar = false>

<cfquery datasource="Northwind2014" name="qryNWProducts">
SELECT TOP 1 * from Products;
</cfquery>

<cfdump var="#qryNWProducts#" label="qryNWProducts">

<cfset variables['stProduct'] = {}>
<cfloop index="vcColName" list="#qryNWProducts.columnlist#">
    <cfif variables.useTempVar>
        <cfset variables['temp'] = qryNWProducts[vcColName]>
        <cfset variables['stProduct'][vcColName] = variables.temp>
    <cfelse>
        <cfset variables['stProduct'][vcColName] = qryNWProducts[vcColName]>
    </cfif>
</cfloop>

<cfdump var="#variables['stProduct']#" label="variables['stProduct']">

<cfloop collection="#variables['stProduct']#" item="key"><cfoutput>
    variables['stProduct']['#key#'] JVM datatype = #getMetadata(variables['stProduct'][key]).getName()#<br>
</cfoutput></cfloop>

<br>
This always works:<br>
<cfset variables['aPhrase'] = "I ordered " &  variables.stProduct.ProductName & " for " & DollarFormat(variables.stProduct.UnitPrice) & ".">
<cfoutput>#variables['aPhrase']#<br></cfoutput>

<br>
With &quot;variables.useTempVar = false&quot;, the next line will throw a &quot;Complex object types cannot be converted to simple values. &quot; error.<br>
<cfset variables['aPhrase'] = "I ordered " &  variables['stProduct']['ProductName'] & " for " & DollarFormat(variables['stProduct']['UnitPrice']) & ".">
<cfoutput>#variables['aPhrase']#<br></cfoutput>

The code above has a boolean variable named "variables.useTempVar" at the top that can be flipped to see the error that I'm getting.

It looks like the direct assignment (when variables.useTempVar = false) from the query to the structure causes the structure values to be of JVM type "coldfusion.sql.QueryColumn".

Another note: if this line of code:

<cfset variables['stProduct'][vcColName] = variables.temp>

is changed to:

<cfset variables['stProduct'][vcColName] = variables['temp']>

The JVM datatype will be "coldfusion.sql.QueryColumn".

When the dot notation temp variable is used to assign the query field (when variables.useTempVar = true); the JVM datatypes are simple types that matches up pretty well with the database column types (java.lang.Integer, java.math.BigDecimal, java.lang.String, etc.).

I've also experiemented with statements like this and that provided some odd results:

<cfset variables['stProduct'][vcColName] = qryNWProducts[vcColName].toString()>

Here's the question. Is this the best way to transfer the simple values from a query to a structure? It seems odd to be forced to use a temp variable and dot notation to make this work.

Comment: I've always thought that dot notation and associative array notation were equivalent. This code example appears to contradict that opinion.

Answer 1

@Leigh is correct in that you need to supply the row number when using associative array notation with a query object. So you'd reference row 1 like: qryNWProducts[vcColName][1]

As for your question

Is this the best way to transfer the simple values from a query to a structure?

Are you sure you need a struct? Your question doesn't really specify the use case, so it is entirely possible that you would be better off using the query object as-is.

If you do need it to be a struct (and since you are using ColdFusion 11) might I suggest you take a look at serializeJSON/deSerializeJSON to convert this to a struct. The serializeJSON has a new attribute that will properly serialize a query object into an "AJAX friendly" JSON array of structs. You can then deSerialize the JSON into a CF array, like so:

NWProducts = deSerializeJSON( serializeJSON( qryNWProducts, 'struct' ) )[1]; Which would return a struct representation of the first row in that query object.

Although it's not obvious from the Adobe docs for serializeJSON, the second parameter can be one of: true|false|struct|row|column which will change how the resulting data is formatted.

Here's a runnable example of using the above technique showcasing each serializeQueryAs option.

It's also a better practice to start moving that kind of code into cfscript. queryExecute is quite easy to use and makes script based queries very easy to develop. See the How To Create a Query in cfscript tutorial at trycf.com for more on how to develop script based queries.

Final note, and this is a bit off topic but it is a generally accepted best practice to not use Hungarian Notation when naming variables.

Answer 2

@Abram's covered the mains answer, but just to pick up one tangential point you raise.

Dot notation and associative array notation are generally equivalent in CFML. However in the case of queries, there is a slight variation. Dot notation: query.columnName is treated as shorthand for query.columnName[currentRow] (where currentRow defaults to 1).

Associative array notation with queries does not have this "syntactic sugar", so query["columnName"] refers to the entire column, as the syntax actually indicates.

There are no functions I am aware of in CFML that take a query column as an argument, however the CFML engine will convert the column to an array if it's used in an array function. This is quite handy sometimes.