asked on

Results of a query in cfscript in the next query.

I'm building a report output page that needs to pull records (UserID) from from the table that associates a user with a group (groupAssociation) and then use each of those IDs to pull the records out of a data table. For arguments sake it's a list of the userid and a value. My included script is the start of this but CF is showing an error and saying that "Variable CFQUERY is undefined."

<cfscript>
sqlstring = "select UserID from groupAssociation where groupid="&form.groupid;
datasource = request.dsn;
username = request.dbusername;
password = request.dbpassword;
maxrows = attributes.maxrows;
getGroupMembers = CFQUERY(sqlstring: sqlstring, datasource: datasource, username: username, password:password, maxrows: maxrows);

// initialize Java StingBuffer Object
SQL = createObject("java", "java.lang.StringBuffer");
      SQL.append("select ID, Name, groupParticipate from groupName where ");
// build the SQL statement
for (i = 1; i lt getGroupMembers.length(); i = i + 1){
SQL.append("ID = " & getGroupMembers.UserID & " and ");
}
      // the last one does not need an and.
      SQL.append("ID = " & getGroupMembers.UserID);
// display string
getGroupResults = CFQUERY(sql:string: variables.SQL.toString(), , datasource: datasource, username: username, password:password);

</cfscript>

results: <cfoutput query=getGroupResults> #userid#<br />#value#

Help me see what I am doing wrong

Mause

Hi there

cfquery is not available in cfscript!

In:
CFQUERY(sqlstring: sqlstring, datasource: datasource, username: username, password:password, maxrows: maxrows);

CF wants to call a function cfquery wicth is also not defined or is it?

Mause

ASKER CERTIFIED SOLUTION

smaglio81

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leeandron

ASKER

Mause, I had gotten some of the ideas for what I was trying to do from this article:
http://www.macromedia.com/devnet/mx/coldfusion/extreme/cftags_cfscript.html
it explained (but I had misunderstood before I sent this question) how to set up a cfcomponent that you could call from within your cfscript. Once that is instanciated you can make a query without leaving the cfscript:

<cfcomponent>
      <cffunction name="QUERY" access="public" returntype="query">
            <cfargument name="SQLString" type="string" required="yes">
            <cfargument name="Datasource" type="string" required="no" default="#request.dsn#">
            <cfargument name="Username" type="string" required="no" default="#request.dbusername#">
            <cfargument name="password" type="string" required="no" default="#request.dbpassword#">
            <cfargument name="maxrows" type="string" required="no" default="1000">
            <CFQUERY NAME="RecordSet" Datasource="#arguments.Datasource#" username="#arguments.Username#" password="#arguments.password#" maxrows="#arguments.maxrows#">
                  #preserveSingleQuotes(arguments.SQLString)#
            </CFQUERY>
            <cfreturn RecordSet>
      </cffunction>
</cfcomponent>

However that requires another file in the root and adds some complexity. I'm going to try out steven's answer and I'll be back.

Mause

If you have a function cfquery you could call it within cfscript
Now you've got a component just call componentname.query to call the function query.

if you just past this in the same source it will work:

<cffunction name="CFQUERY" access="public" returntype="query">
<cfargument name="SQLString" type="string" required="yes">
<cfargument name="Datasource" type="string" required="yes">
<cfargument name="dbType" type="string" default="">
<cfquery name="RecordSet" datasource="#arguments.Datasource#"
dbtype="#arguments.dbType#">
#preserveSingleQuotes(arguments.SQLString)#
</cfquery>
<cfreturn RecordSet>
</cffunction>