Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Passing one row of a query to a function

Guest
May 12, 2008 May 12, 2008
I want to pass a query into a function one row at a time in the same way you could pass an array to a function one row at a time. I've read that queries are just CF structures under the hood so I believe it should be possible. I just don't know the syntax to reference a single query row (and all it's variables).

I don't want to pass the whole query to the variable because I would like to do the looping outside the function. The idea is to simplify and standardize the formatting and arrangement of several variables (columns) in a UDF and keep the syntax to use the function simple. Therefore, I'd like to avoid having to do stuff like convert the query to an array. That would defeat the goal of keeping it simple.

Thanks
Magnus
3.7K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 12, 2008 May 12, 2008
one way would be:
<cfoutput query="yourquery">
call your function here: it will be called for each query row
</cfoutput>

another way would be using array notation: queryname.columnname[rownumber]
you will have to reference and pass each query column individually like
that, so if you want to pass a full query row use the first method

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 13, 2008 May 13, 2008
I was hoping to avoid having to pass each column value into the function individually. There are several of them. It would be so much easier to be able to do something like:

foo(query.row )

and have all the values in that row available in the function.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
May 13, 2008 May 13, 2008
**entered in error** sorry
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 13, 2008 May 13, 2008
quote:

Originally posted by: mthyvold
I want to pass a query into a function one row at a time in the same way you could pass an array to a function one row at a time. I've read that queries are just CF structures under the hood so I believe it should be possible. I just don't know the syntax to reference a single query row (and all it's variables).

I don't want to pass the whole query to the variable because I would like to do the looping outside the function. The idea is to simplify and standardize the formatting and arrangement of several variables (columns) in a UDF and keep the syntax to use the function simple. Therefore, I'd like to avoid having to do stuff like convert the query to an array. That would defeat the goal of keeping it simple.

Thanks
Magnus

Your plan of sending one row at a time will not achieve your objective of keeping the syntax to call the function simple.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 13, 2008 May 13, 2008
Can you explain why not? (other than that AFIK you can't reference and pass a specific query row) Just curious.

Anyway, I guess what I want to do would look more like this:

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 13, 2008 May 13, 2008
quote:

Originally posted by: magnusthyvold
Can you explain why not? (other than that AFIK you can't reference and pass a specific query row) Just curious.

Anyway, I guess what I want to do would look more like this:



Start with the concept that cfcs and udfs are reusable blocks of code. If complicated processing is involved, it's a lot easier to code it once in the function than each and every time you call it.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 13, 2008 May 13, 2008
That's why I want to use a function. The function will format and arrange a string of names and links that occur again and again and again in my application. In many similar situations I run or pass in the query to the function and do the looping within it to create the desired output. But that won't work so well here.

I don't want to do the looping within the function because, depending on the use, sometimes the formated item is prefixed by an image, a checkbox, a link etc. and sometimes suffixed by other links or other text. I would need to create a lot of conditional code for situations that only occur once and pass a bunch of arguments to control what is shown and what isn't.

Thanks for everyone's input
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 13, 2008 May 13, 2008
You can do this:

myQuery.getRow(n)

where n is the *zero* indexed row reference.

This will return an object of type coldfusion.sql.imq.Row, which has a
method getColumn(), which likewise takes a zero-indexed reference to the
column you want.

Pls note: this is "leveraging" the internal workings of CF, and is not
documented, encouraged or supported by Adobe. However it does what you
want.

I've tested it on CFMX7.0.2 and CF8.

To be honest, I'd simply pass the whole query (which is done by reference,
so there's not much overhead there), and the row you want as two separate
arguments, then using query["column"][row] notation to access the bits you
want.

--
Adam
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 13, 2008 May 13, 2008
quote:

Originally posted by: Newsgroup User

To be honest, I'd simply pass the whole query (which is done by reference,
so there's not much overhead there), and the row you want as two separate
arguments, then using query["column"][row] notation to access the bits you
want.

--
Adam



I think this sounds like the way to do it. Thanks
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 16, 2008 May 16, 2008
Adams suggestion is pretty neat - I didn't know about myQuery.getRow(), but using undocumented functions make me nervous. If you really wanted to accomplish this using standard CF functions you could do something like this:

1) Dynamically retrieve a list of columns using qMyQuery.ColumnList
2) Loop over columns and retrieve column values for the current row using qMyQuery[ColumnListItem][Row]
3) Using the above techniques, build a CF structure where each key is a column in the query and the key value is the query field/row value
4) Pass the results of (3) into a UDF.

But that seems like a lot of work.
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 16, 2008 May 16, 2008
> myQuery.getRow(), but
> using undocumented functions make me nervous.

I absolutely agree, as a standard "caveat emptor" sort of thing. Which is
a shame because there's a wealth of functionality "under the hood" which is
far more useful than the "official" approach to things.

To be honest, I treat the whole "that stuff is 'not supported'" and "it
could change in future versions" thing with a liberal amount of salt.

1) I didn't find the support from Macromedia very accessible in the first
place, and have no reason to think it will be any different with Adobe.
Other than in emergencies (that'd be once, in eight years), I have never
used official support;

2) I've been watching a lot of the "undocumented" stuff for a few versions
(like since 6.1), and it all still works;

3) I've had more problem with "fixes" to CFML issues breaking workarounds
of the same than I have with using undocumented code. Obviously that's
because I have 1000 lines of "normal" code to every line of "undocumented"
code I use, but still.

Basically when one changes version of software, there's a chance your code
will stop working. It doesn't seem any more risky to me to use
undocumented features than documented ones.

Food for thought, if nothing else.

:-)

--
Adam
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 19, 2024 Jun 19, 2024
LATEST

For anyone else arrving here QueryGetRow does what you want I believe (introduced in CF11) 

 

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/querygetrow.htm... 

 

 

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources