-3

I have few functions in my system that should be converted from CFML to CFSCRIPT. While working on this project I run to the situation where my structure outputs more result (a lot of empty rows) than it should, even query returns only one row. Here is example of my code in CFSCRIPT:

cfstoredproc( procedure="GetZip", datasource=Application.dsnRead ) {
    cfprocparam( dbvarname="@Zip", value=trim(52401), cfsqltype="cf_sql_char", maxlength=5 );
    cfprocresult( name="ZipResult" );

}

local.strZip = {};
strZip[ZipResult.RecID] = {
    "City" : trim(ZipResult.City),
    "State" : trim(ZipResult.State)
};

writeDump(strZip);

Here is what I get for the output:

array
1   [undefined array element]
2   [undefined array element]
3   [undefined array element]
4   [undefined array element]
5   [undefined array element]
6   [undefined array element]
7   [undefined array element]
8   [undefined array element]
9   [undefined array element]
10  [undefined array element]
11  [undefined array element]
12  [undefined array element]
13  [undefined array element]
14  [undefined array element] 
...

I'm wondering what is the best way to output query result(s) in structure and use RecID as a unique key? This example and query above always will return 1 record at the time but also I'm wondering how this code would work if I need to loop over the query result with multiple records?

Update: I think that I found the problem. The RecID is auto increment id in my db table. When i return RecID the value can be 56743 for example. So if I pass RecID as a key in my structure, that will cause so many rows in the structure. My question is how to prevent this? Is there a way to just set the key?

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • 1
    So it happens because CF thinks strZip is an array, not a structure. I can't reproduce it using the snippet posted. Could you please post the complete function? Also, what version of CF? – SOS Sep 26 '18 at 14:20
  • @Ageax I'm not sure how to create working snippet for ColdFusion here. I use CF 2016. I'm not sure if this the problem only because I tried to dump the result on the screen... – espresso_coffee Sep 26 '18 at 14:23
  • use `writedump(ZipResult)` to find out what you are getting out of the stored procedure – James A Mohler Sep 26 '18 at 14:24
  • @JamesAMohler I have mentioned in my comment that I'm getting only one row back since query has TOP 1 in SQL query. I think that @Ageax is right about the fact that CF thinks that row.RecID in `[]` is array. I'm not sure how to fix that problem. – espresso_coffee Sep 26 '18 at 14:26
  • 1
    @espresso_coffee Just post the whole function above. You can also create snippets with manual queries, ie. QueryNew(), but let's start with seeing the function – SOS Sep 26 '18 at 14:49
  • Is this a complex query? I'm a big fan of using stored procedures, but if this is a simple query, a sproc may not necessarily be the best solution. Especially if you split your database team off from your application team. – Shawn Sep 26 '18 at 15:53
  • 1
    @espresso_coffee - We definitely need to see the full code, because it's not reproducible so far. Suggesting something's different in your app. – SOS Sep 26 '18 at 15:57
  • @Ageax I'm not sure what other information you need. The problem is something completely different and I explained the details. – espresso_coffee Sep 26 '18 at 16:06
  • Whatever function produced the "undefined array element"...., please post that code. Part of troubleshooting is isolating the issue and reducing it to the smallest amount of code that reproduces the issue. It's not reproducible with the snippet above: https://trycf.com/gist/31583cd0c188504690542d7f81c19cb0/acf2016?theme=monokai – SOS Sep 26 '18 at 16:10
  • Yes, the only way I've been able to duplicate your result is to not explicitly set `local.strZip` as a structure. Then CF will implicitly make it an array. – Shawn Sep 26 '18 at 16:44
  • @espresso_coffee Is your above code inside a function? `LOCAL` is a function-only scope, so I believe if you aren't inside a function, you're just creating a `LOCAL` object inside the `VARIABLES` scope, so outside of a function, you'd actually be making `VARIABLES.LOCAL.strZip`. – Shawn Sep 26 '18 at 17:13
  • @Shawn That's the problem, my original code is inside of the function. When I was running the test I did not get rid of the `.local` scope. Thank you. – espresso_coffee Sep 26 '18 at 17:14
  • 1
    I don't really understand the downvotes on a question with a lot of activity. But anyway, I have confirmed the issue and my suspicions with scoping issues. CFFiddle is apparently configured with the same scope-hunting that your server is. I think I like this setting a lot better than TryCFs. Anyway: https://cffiddle.org/app/file?filepath=01889d75-e5c9-4ffd-9970-bfa2cfa9898a/3f865834-ee62-4984-924c-fd7d4025830f/4c3e9fd6-0d8f-4f45-a5ba-8451d3b1248d.cfm – Shawn Sep 26 '18 at 18:13
  • @Shawn Thanks for your help. I do not understand the downvotes too... – espresso_coffee Sep 26 '18 at 18:48

3 Answers3

3

I'm not sure why your CF is converting strZip to an array, but that is why you are getting a bunch of empties. A structure key can be an integer, but when you tell an array to insert x[42], you will have 42 elements in your array. local.strZip = {}; pretty explicitly states that strZip is a struct. However, scopes can get weird in CF sometimes. Depending on how you're using it, I think when you do strZip[ZipResult.RecID] it may be creating a new unscoped variable that is an array. And then when you dump it, you are dumping the array version of strZip. You can try using local.strZip[ZipResult.RecID] and see if that changes your behavior. Or try dumping local.strZip and see if it's empty.

Or you can do:

<cfscript>
    // Build a fake query object.
    ZipResult = queryNew(
        "RecID, City, State",
        "integer, varchar, varchar",
        [
              { RecID: 99, City: "Nashville", State: "TN" }
        ]
    );
    writeDump(ZipResult); // What's in our query?


local.strZip = {
    "#ZipResult.RecID#" : {
      City : trim(ZipResult.City) , 
      State : trim(ZipResult.State)
    }
};

writeDump(strZip);

</cfscript>

https://trycf.com/gist/28440630b0aa7ba1642e45bab3503652/acf2016?theme=monokai

NOTE: I was unable to duplicate your behavior in TryCF, but I'm also not likely executing that code in the same scope that you are. CONFIRMED: https://cffiddle.org/app/file?filepath=50f4e710-bd9b-40dd-a03a-15695bdd5a0d/c476f91b-6931-4295-be67-e4309aecfa0c/0492a7c6-029f-4227-941a-faee9da5a7cc.cfm

Shawn
  • 4,758
  • 1
  • 20
  • 29
  • I'm not sure what could cause this problem. Only what I can do is maybe use `structnew()` instead of `= {};` I'm not sure if that would change anything. – espresso_coffee Sep 26 '18 at 16:45
  • There's essentially no difference between `structNew()` and `= {}`. My example sets a struct within a struct, which seems to be what your initial code was doing. – Shawn Sep 26 '18 at 16:49
  • @espresso_coffee - The fact that 2 separate people haven't been able to reproduce it using the OP posted strongly indicates there's something different in the actual code than the snippet you posted. But without an actual repro case, we're all just guessing... Please take a little more time and put together the smallest amount of code that actually reproduces the issue :-) – SOS Sep 26 '18 at 17:03
1

You can try this - 

    var strZip = {};
    for ( i=1; i<=ZipResult.recordCount; i++)
    {
        strZip[ZipResult.RecID[i]] = {
             "City" : trim(ZipResult.City[i]),
             "State" : trim(ZipResult.State[i])
        };
    }

This way we are treating query like a structure of arrays.

Pankaj
  • 1,731
  • 1
  • 13
  • 15
0

No, you're right. Because it's an integer, ColdFusion is populating the array up to the point of that integer since you can't have an array with a bunch of empty values; they have to have some value, so - as you see here - they're undefined. You can't use an integer for a struct key. If you add a letter to the front of it, then chop the key to that value when you reference it, that will work. It's hacky, but that's what I generally do for this sort of thing. If you want to test it, you could use StructInsert() instead of [].

Colin G
  • 155
  • 6
  • 2
    Yes, you *can* use integers for structure keys. But context matters. CF needs to understand it's a structure. Usually this kind of thing happens in cases where the variable type is ambiguous. CF doesn't know which one you want, so it guesses. It sees an integer and decides you want an array. – SOS Sep 26 '18 at 14:48
  • You're right, my comment is misleading: it won't throw an error if you use integers for structure keys, but this is exactly what happens: CF converts it to an array. I guess another way around this would be to set strZip.a = "" or similar, so it's set as a struct before you start adding numeric keys, then just ignore that "a" key. I don't really know which is less hacky. – Colin G Sep 26 '18 at 16:46
  • FWIW, you can use the [edit](https://stackoverflow.com/posts/52520325/edit) to update answers. I suspect the cause is lack of scoping. – SOS Sep 26 '18 at 16:55