Solved

The GetColumnNames method was not found

Posted on 2011-03-09
15
638 Views
Last Modified: 2012-05-11
I'm getting the above error, and can't figure out why or where the data is not being returned.

Error is on line 15 as shown in code below:

<cfif url.event eq "generateIDLIst">
            <cfset db = StructNew() />
        <cfset  db.dbname = getProperty("dbName")>  <!--- getting dbname from machii.xml  --->
        <cfset db.dbUserName = getProperty("dbUserName")>
        <cfset  db.dbPassword = getProperty("dbPassword")>
        <cfset rootFolder = getProperty("rootFolder")>
        <cfset  queryToCSV = createObject("component","#rootFolder#.utils.queryToCSV").init()/>
        <cfoutput>
            <cfset  idListGenerationObj       =  createObject("component","#rootFolder#.model.idListGeneration.idListGenerationDAO").init(db.dbname,db.dbUserName,db.dbPassword)>
        </cfoutput>
        <cfset getSolicitCount = idListGenerationObj.getIDlist(url.appealOrProgCode,url.isSolicit,url.queryID,url.typeOfSolicit,url.hiIDObj,url.from,url.mailingList)>  
 
LINE 15        <cfset columnList = ArraytoList(getSolicitCount.GetColumnNames())>

        <cfset strOutput = queryToCSV.QueryToCSV(
                                               Query=getSolicitCount,  
                                               Fields=columnList
                                               ) />      
                                           
      <cfheader name="content-disposition" value="attachment; filename=contactList.CSV" />
      <cfcontent type="text/plain" /><cfoutput>#strOutput#</cfoutput>
      <!--- <cfdump var="#strOutput#"><cfabort>
      FOR QUERY BELOW--->
 <cfelse>  
   <cfprocessingdirective suppresswhitespace="yes" >
        <cfsetting enablecfoutputonly="1" showdebugoutput="0">
        <cfcontent type="application/x-javascript">
        <cfsilent>
            <cfparam name="FORM.appealOrProgCode" default="" />
            <cfparam name="FORM.isSolicit" default="" />
            <cfparam name="FORM.queryID" default="" />
            <cfparam name="FORM.typeOfSolicit" default="" />
            <cfparam name="FORM.hiIDObj" default="" />
            <cfparam name="FORM.from" default="" />
<<<<<<< .mine
           <!--- <cfdump var="#form#"><cfabort>--->
=======
            <!--- <cfdump var="#form#"><cfabort>--->
>>>>>>> .r941
            <cfset db = StructNew() />
            <cfset  db.dbname = getProperty("dbName")>  <!--- getting dbname from machii.xml  --->
            <cfset db.dbUserName = getProperty("dbUserName")>
            <cfset  db.dbPassword = getProperty("dbPassword")>
            <cfset rootFolder = getProperty("rootFolder")>
     
            <cfset tmpArray    = ArrayNew(1) >
           <cfoutput>
           <cfset  idListGenerationObj       =  createObject("component","#rootFolder#.model.idListGeneration.idListGenerationDAO").init(db.dbname,db.dbUserName,db.dbPassword)>
            </cfoutput>
             <cfset getSolicitCount = idListGenerationObj.getSolicitCount(FORM.appealOrProgCode,FORM.isSolicit,FORM.queryID,FORM.typeOfSolicit,FORM.hiIDObj,FORM.from)>  
                <cfset data =   serializeJson(getSolicitCount)>
                <cfcontent type="application/json"  reset="true">
        </cfsilent>
         <cfoutput>#data# </cfoutput>
    </cfprocessingdirective>
 </cfif>

   Another piece to this that may be helpful is that the above code is called from the code below, and it displays the error "objResponse.DATA is undefined" (LINE 2331) in Firebug.

function solicitCountDetails(appealOrProgCode,isSolicit,queryID,typeOfSolicit,hiIDObj,from) {       
      document.getElementById('solicit_generateCount').style.display = "none";
      document.getElementById('solicitCount_processing').style.display = "block";       
      //$("#"+type).html("");      
      //alert(appealCode); return;
      //var unicode=evt.keyCode? evt.keyCode : evt.charCode      
      // alert(unicode)
 
       $.ajax(
                  {
                        type: "post",
                  //      url:  "views/cfm/AJAXCalls/getFormData.cfm",
                         url:  "index.cfm?event=getSolicitCount",
                        data: {
                              appealOrProgCode : appealOrProgCode,
                              isSolicit : isSolicit,
                              queryID : queryID,
                              typeOfSolicit  :  typeOfSolicit ,
                              hiIDObj :hiIDObj,
                              from :from
                        },
                        dataType: "json",
                        success: function(objResponse)
                              {      
                                     //alert(objResponse); return;
LINE 2331                   var solictCount = objResponse.DATA[0][0];
                                     var idListContactCount = objResponse.DATA[0][1];
                                     if(idListContactCount == null)
                                           {
                                                idListContactCount = 0;
                                          }
                                     var idListDeceCount = objResponse.DATA[0][2];
                                      if(idListDeceCount == null)
                                           {
                                                idListDeceCount = 0;
                                          }
                                     var idListLostEntity = objResponse.DATA[0][3];
                                      if(idListLostEntity == null)
                                           {
                                                idListLostEntity = 0;
                                          }
                                     $("#idListSolicitCount").html("");
                                    $("#idListSolicitCount").html(solictCount);
                                    $("#idListContactCount").html("");
                                    $("#idListContactCount").html(idListContactCount);
                                    $("#idListDeceCount").html("");
                                    $("#idListDeceCount").html(idListDeceCount);
                                    $("#idListLostEntity").html("");
                                    $("#idListLostEntity").html(idListLostEntity);
                              
                                    document.getElementById('solicit_generateCount').style.display = "block";
                                    document.getElementById('solicitCount_processing').style.display = "none";       
                              },
                        error: function(xhr, objRequest, strError){
                               //alert(xhr.responseText); return;
                               document.getElementById('solicit_generateCount').style.display = "block";
                               document.getElementById('solicitCount_processing').style.display = "none";       
                         
                              }
                  }
            );
      }
   
  Any clues on why this may be happening would be greatly appreciated.

  Thanks!
0
Comment
Question by:henderxe
  • 5
  • 5
  • 4
  • +1
15 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 35083805

 Does getIDlist  return a Query ?

<cfset getSolicitCount = idListGenerationObj.getIDlist(....)>


<cfdump var="#getSolicitCount#">  <!---- find out what it returns ---->



0
 
LVL 52

Expert Comment

by:_agx_
ID: 35084545
The GetColumnNames method was not found
...
Another piece to this that may be helpful is that the above code is called from the code below, and it displays the error "objResponse.DATA is undefined"


Also, that suggests you're calling the 1st part of the code which returns a CSV string (not a query).  So objResponse.DATA wouldn't be defined ... because the result isn't a query.  Are you sure you're calling the right section of code

getSolicitCount.GetColumnNames())

Is that an alternative to query.columnList? Because there's no documented function by that name.
0
 
LVL 3

Assisted Solution

by:sajayc
sajayc earned 100 total points
ID: 35087474
Hi,

Try replacing line 15 with this:
<cfset columnList = getSolicitCount.columnlist>

I've assumed that getSolicitCount is a query result set.
The columnlist is already a comma delimited list, so no need to use the arraytolist function.

Hope this helps.
0
 

Author Comment

by:henderxe
ID: 35089128
gdemaria:

     Yes, it returns the query itself, but not the data, which is:

SELECT id_number ,hi_id ,PREF_MAIL_NAME ,PREF_NAME_SORT ,JNT_SALUTATION ,ADDRH_PHONE_NUMBER ,ADDRC_CITY ,ADDRC_COUNTRY_CODE ,ADDRC_FOREIGN_CITYZIP ,ADDRC_FULL_PHONE_NUMBER ,ADDRC_PHONE_AREA_CODE ,ADDRC_PHONE_NUMBER ,ADDRC_STATE_CODE ,ADDRC_STREET1 ,ADDRC_STREET2 ,ADDRC_STREET3 ,ADDRC_ZIPCODE FROM ART_MANUSCRIPT WHERE id_number in (( select qry1.id_number from (SELECT clobqry.ID_NUMBER FROM( select to_number(vals) as id_number from tempIdlists )clobqry union select id_number from advance.mailing_list where mail_list_status_code = 'A' and mail_list_code in ('') and mail_list_ctrl_code in (' ', 'I') and (advance.mailing_list.stop_dt = '00000000' or advance.mailing_list.stop_dt > '20110309') ) qry1 minus SELECT distinct advance.handling.id_number from (SELECT clobqry1.ID_NUMBER FROM( select to_number(vals) as id_number from tempIdlists )clobqry1 union select id_number from advance.mailing_list where mail_list_status_code = 'A' and mail_list_code in ('') and mail_list_ctrl_code in (' ', 'I') and (advance.mailing_list.stop_dt = '00000000' or advance.mailing_list.stop_dt > '20110309') )msu_temp_ai_coni_hnd1, advance.handling where msu_temp_ai_coni_hnd1.id_number = advance.handling.id_number and advance.handling.hnd_status_code = 'E' and advance.handling.hnd_type_code in ('MZZ','ZZZ' ) and ( advance.handling.lift_dt = '00000000' or advance.handling.lift_dt = ' ' or advance.handling.lift_dt > '20110309' ) and ( rtrim(advance.handling.program_code) is null or advance.handling.program_code in ('') ) ) minus select id_number from advance.mailing_list where mail_list_status_code = 'A' and mail_list_ctrl_code in ('E','M') )

agx:

  I had noticed "GetColumnNames())" was not listed as a method, but assumed term " GetColumnNames())" was a "standard" command used to extract field names.   I'm guessing that was a bad assumption :]!

  The "getIDList" method cited in LINE 12 is as follow (returns ID List)s:

<cffunction name="getIDlist" access="public" returntype="any"><!---   Populate city based on the state we have selected --->  
            <cfargument name="appealOrProgCode" type="string" required="yes">
          <cfargument name="isSolicit" type="string" required="yes">
            <cfargument name="queryID" type="string" required="yes">
         <cfargument name="typeOfSolicit" type="string" required="yes">
         <cfargument name="hiIDObj" type="string" required="Yes">
           <cfargument name="from" type="string" required="Yes">
         <cfargument name="mailingList" type="string" required="Yes">  
           
        <cfset typecode = "">
                <cftry>
                  <cfquery name="getIDsFromID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                               select list_values from art_idlist where idList_id = #queryID#
                                </cfquery>
                                <cfset listID = getIDsFromID.list_values >
                                  <cfstoredproc procedure="addIdLists"    datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                                          <cfprocparam type="In"   cfsqltype="cf_sql_varchar"  variable="id_no"  value="#getIDsFromID.list_values#">
                                   </cfstoredproc>  
                         <cfcatch type="any" >
                            <cfset getProgCodeFromAppealCode = "#cfcatch.SQLState#">
                         </cfcatch>
                     </cftry>    
                 <cfset queryFromCLOB =     " select  to_number(vals) as  id_number from tempIdlists  "/>
                   
    </cffunction>

sajayc:

  Replaced LINE 15 as you suggested, and got the following error:

"You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members".

  Thanks all!
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 100 total points
ID: 35089668
The GetColumnNames method was not found

@henderxe - Which code are you calling ... the 1st part or the 2nd? You seem to be expecting a query, I'm guessing it's the 2nd part. But I don't see GetColumnNames() anywhere in the 2nd part of the code... What's the code behind the getSolicitCount function?

<!--- 1ST PART --->
<cfif url.event eq "generateIDLIst">
        ....
       <cfset columnList = ArraytoList(getSolicitCount.GetColumnNames())>
      <cfset strOutput = queryToCSV.QueryToCSV( ... )>
      ....
<!--- 2ND PART --->
<cfelse>
     ....
     <!--- GetColumnNames isn't used here ... ? --->
       <cfset getSolicitCount = idListGenerationObj.getSolicitCount(....)>
     ....
</cfif>

I had noticed "GetColumnNames())" was not listed as a method
Yeah, afaik it's not documented. But query.columnList is.




0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35090746
>  Yes, it returns the query itself, but not the data,

Then it is not a query... it is the text of a SELECT statement.

That is very different, you need to call your select statement inside CFQUERY and THAT is a query...

<cfquery name="getValues" ....>
   select this from that
</cfquery>
<cfdump var=#getValues.columnList#">  <!==== that's where you get the column list..



But wait, your function is not returning anything.. there is no cfreturn statement in the function..

Not sure how it works at all..

I don't understand the function, it just sets variables that it never uses, and there is no cfreturn statement so nothing is returned back to the calling code...


<cffunction name="getIDlist" access="public" returntype="any"><!---   Populate city based on the state we have selected --->  
    <cfargument name="appealOrProgCode" type="string" required="yes"> 
    <cfargument name="isSolicit" type="string" required="yes">
    <cfargument name="queryID" type="string" required="yes">
    <cfargument name="typeOfSolicit" type="string" required="yes">
    <cfargument name="hiIDObj" type="string" required="Yes">
    <cfargument name="from" type="string" required="Yes">
    <cfargument name="mailingList" type="string" required="Yes">   

    <cfset typecode = "">
    <cftry>
      <cfquery name="getIDsFromID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
        select list_values from art_idlist where idList_id = #queryID#
      </cfquery>
      <cfset listID = getIDsFromID.list_values > 
      <cfstoredproc procedure="addIdLists"    datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
          <cfprocparam type="In"   cfsqltype="cf_sql_varchar"  variable="id_no"  value="#getIDsFromID.list_values#"> 
      </cfstoredproc>  
    <cfcatch type="any" >
      <cfset getProgCodeFromAppealCode = "#cfcatch.SQLState#">
    </cfcatch>
    </cftry>     
    <cfset queryFromCLOB = " select  to_number(vals) as  id_number from tempIdlists  "/>
</cffunction>

</cffunction>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35090771
Frankly the whole thing is confusing.  Starting with why are we expecting a query object to be returned from code that appears to create a CSV list:

<cfset strOutput = queryToCSV.QueryToCSV(Query=getSolicitCount,  Fields=columnList) />      
<cfheader name="content-disposition" value="attachment; filename=contactList.CSV" />
<cfcontent type="text/plain" /><cfoutput>#strOutput#</cfoutput>
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:henderxe
ID: 35091790
@agx

     If I'm understanding you correctly, the sequence of events are:

       <cfif url.event eq "generateIDLIst">

          1ST PART:

           1.  Grabs some fields from a database, using "GETIDLIST" method":
                <cfset getSolicitCount = idListGenerationObj.getIDlist( . . . . . . .
            2.   .  and assigns those values to:
                <cfset columnList = ArraytoList(getSolicitCount.GetColumnNames())>
            3.  . which are converted to CSV, and assigned another value:
                   <cfset strOutput = queryToCSV.QueryToCSV( ... )>
            4.  Finally, "strOutput" is exported as a CSV file.
                 <cfheader name="content-disposition" value="attachment; filename=contactList.CSV" />
                 <cfcontent type="text/plain" /><cfoutput>#strOutput#</cfoutput>
                 
     <cfelse>

         2ND PART:

           1.  Uses the "GETSOLICITCOUNT" method to get IDs.
                 <cfset getSolicitCount = idListGenerationObj.getSolicitCount( .  .  .  .  .  

       NOTE:  1.  What's significant here is that the 1ST part loads IDs from a file, while the 2ND part loads IDs from a saved query in a database.
           
       Hope this makes sense, and is what you're seeking.

       

                 

   

0
 

Author Comment

by:henderxe
ID: 35093952
Sorry everyone -  didn't mean to confuse the issue.

As I mention, there are two parts to this application:

    1.  Loading IDs by a saved query (myQuery).
    2.  Loading IDs from a file.
   
The myQuery portion works fine.  It's just when loading IDs from a file that I get the error, so I tried to extract just that portion to minimize confusion, but apparently made it worse.

I have no problem posting the code in its entirety, so here goes:

The following (idListGenerationDAO.cfc) Contains both "GETIDLIST" and GETSOLICITCOUNT" Functions:

------------------------------------BEGIN  idListGenerationDAO.cfc -----------------------------------

<cfcomponent displayname="idListGenerationDAO" output="no"  extends="MachII.framework.Listener">
   
     <cffunction name="init" access="public" output="false"  hint="getting the database details from Listener" returntype="ART_TEST.model.idListGeneration.idListGenerationDAO">
         <cfargument name="dbName" type="string" required="yes" hint="dbName">
         <cfargument name="dbUserName" type="string" required="yes" hint="UserName">
         <cfargument name="dbPassword" type="string" required="yes" hint="Password">
         
                     <cfset db = StructNew() />
                        <cfset  db.dbname =dbName >  <!--- getting dbname from machii.xml  --->
                   <cfset db.dbUserName =dbUserName  >
                   <cfset  db.dbPassword = dbPassword >
            <cfreturn this>
      </cffunction>    
    <cffunction name="getSolicitCount" access="public" returntype="any"><!---   Populate city based on the state we have selected --->  
            <cfargument name="appealOrProgCode" type="string" required="yes">
        <cfargument name="isSolicit" type="string" required="yes">
            <cfargument name="queryID" type="string" required="yes">
        <cfargument name="typeOfSolicit" type="string" required="yes">
        <cfargument name="hiIDObj" type="string" required="yes">
        <cfargument name="from" type="string" required="yes">
        <cfset typecode = "">
       
            <cfif from eq "myQuery">
             <cfquery name="getIDListQueryForQueryID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                          select  idList_query  from art_query where query_id = #queryID#
               </cfquery>  
              <cfloop query="getIDListQueryForQueryID" startrow="1">
                    <cfset queryFromCLOB = getIDListQueryForQueryID.idList_query>
              </cfloop>
              <cfset queryFromCLOB = #Replace(queryFromCLOB, "''", "'", "all")# >
        <cfelse>
                <cftry>
                  <cfquery name="getIDsFromID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                        SELECT
                          list_values
                    FROM
                          art_idlist
                    WHERE
                          idList_id = #queryID#
                  </cfquery>
                          <cfset listID = getIDsFromID.list_values >
                  <cfstoredproc procedure="addIdLists"    datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                        <cfprocparam type="In"   cfsqltype="cf_sql_varchar"  variable="id_no"  value="#getIDsFromID.list_values#">
                  </cfstoredproc>  
                 <cfcatch type="any" >
                    <cfset getProgCodeFromAppealCode = "#cfcatch.sql#">
                    <cfreturn getProgCodeFromAppealCode>
                 </cfcatch>
             </cftry>    
                <!---  <cfset queryFromCLOB =     " select  lpad(vals,10,'0') id_number from tempIdlists "> --->
                <cfset queryFromCLOB =   "select distinct to_number(vals) as  id_number from tempIdlists ">
        </cfif>      
           
          <cfif  isSolicit  eq "Yes">
           <cfif typeOfSolicit eq "email">
                <cfset typecode = "'EZZ','TEZ','TZZ','ZZZ' ">
           <cfelseif typeOfSolicit eq "postal">
                <cfset typecode = "'MZZ','TMZ','TZZ','ZZZ' ">
           <cfelseif typeOfSolicit eq "telemarketing">
                <cfset typecode = "'PZZ','TPZ','TZZ','ZZZ' ">
           </cfif>
           <cfquery name="getProgCodeFromAppealCode"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
               select program_code from advance.tms_program
                where program_code in (select program_code from advance.appeal_header  where appeal_code in ('#appealOrProgCode#'))
            </cfquery>
            <cfset programCode = "#getProgCodeFromAppealCode.program_code#">
        <cfelse>
            <cfif typeOfSolicit eq "email">
                <cfset typecode = "'EZZ','ZZZ' ">
           <cfelseif typeOfSolicit eq "postal">
                <cfset typecode = "'MZZ','ZZZ' ">
           <cfelseif typeOfSolicit eq "telemarketing">
                <cfset typecode = "'PZZ','ZZZ' ">
           </cfif>    
           <cfset programCode = "#appealOrProgCode#">
        </cfif>  
               
        <cfset queryToGetIDFromSolicit = "
            select qry1.id_number from (#queryFromCLOB# ) qry1
             minus  
            SELECT distinct
                    advance.handling.id_number
                     from
                        (#queryFromCLOB# )msu_temp_ai_coni_hnd1,  
                     advance.handling
                     where
                        msu_temp_ai_coni_hnd1.id_number = advance.handling.id_number and
                          advance.handling.hnd_status_code = 'E'
                          and advance.handling.hnd_type_code in (#preserveSingleQuotes(typecode)#)
                          and (
                               advance.handling.lift_dt = '00000000'
                               or advance.handling.lift_dt = ' '
                               or advance.handling.lift_dt > '#dateFormat(now(),'YYYYMMDD')#'
                               )
                                and
                                (
                                rtrim(advance.handling.program_code) is null
                          or advance.handling.program_code in ('#programCode#')  ) "/>
        <cfset hiIDUnionQuery = "  union select hi_id from
                                    ART_MANUSCRIPT where id_number in(
             select qry1.id_number from (#queryFromCLOB# ) qry1
            minus    
                           SELECT distinct
                    advance.handling.id_number
                     from
                        (#queryFromCLOB# )msu_temp_ai_coni_hnd1,  
                     advance.handling
                     where
                        msu_temp_ai_coni_hnd1.id_number = advance.handling.id_number and
                          advance.handling.hnd_status_code = 'E'
                          and advance.handling.hnd_type_code in (#preserveSingleQuotes(typecode)#)
                          and (
                               advance.handling.lift_dt = '00000000'
                               or advance.handling.lift_dt = ' '
                               or advance.handling.lift_dt > '#dateFormat(now(),'YYYYMMDD')#'
                               )
                                and
                                (
                                rtrim(advance.handling.program_code) is null
                          or advance.handling.program_code in ('#programCode#')  ) "/>
        <cftry>
            <cfquery name="resultSet"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                            <!--- select '23' as solicit, '24' as contact, '25' as deceased, '26' as Lost from dual --->
                            select  
                                     sum(decode(solicitation,'Y','1','0')) as solicit,
                                sum(decode(deceased||lost_status,'00','1','0'))as contact ,
                                sum(deceased) deceased ,  
                                sum(lost_status) Lost
                                  from ART_MANUSCRIPT
                                where id_number in  
                                       ((#preserveSingleQuotes(queryToGetIDFromSolicit)#)
                                        <cfif hiIDObj eq "true">
                                       #preserveSingleQuotes(hiIDUnionQuery)#)
                                      </cfif>
                                       )  
            </cfquery>
        <cfcatch type="any">
              <cfset error = "#cfcatch.sql#">
                 <cfreturn error/>
        </cfcatch>
        </cftry>        
            <cfreturn resultSet>
      </cffunction>
   
     <cffunction name="getIDlist" access="public" returntype="any"><!---   Populate city based on the state we have selected --->  
            <cfargument name="appealOrProgCode" type="string" required="yes">
          <cfargument name="isSolicit" type="string" required="yes">
            <cfargument name="queryID" type="string" required="yes">
         <cfargument name="typeOfSolicit" type="string" required="yes">
         <cfargument name="hiIDObj" type="string" required="Yes">
           <cfargument name="from" type="string" required="Yes">
         <cfargument name="mailingList" type="string" required="Yes">  
           
        <cfset typecode = "">
         
        <cfif from eq "myQuery">
             <cfquery name="getIDListQueryForQueryID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                          select  idList_query  from art_query where query_id = #queryID#
               </cfquery>  
              <cfloop query="getIDListQueryForQueryID" startrow="1">
                   <cfset queryFromCLOB = getIDListQueryForQueryID.idList_query>
              </cfloop>
               <cfset queryFromCLOB = #Replace(queryFromCLOB,"''","'","all")#>
         <cfelse>
                <cftry>
                  <cfquery name="getIDsFromID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                               select list_values from art_idlist where idList_id = #queryID#
                                </cfquery>
                                <cfset listID = getIDsFromID.list_values >
                                  <cfstoredproc procedure="addIdLists"    datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                                          <cfprocparam type="In"   cfsqltype="cf_sql_varchar"  variable="id_no"  value="#getIDsFromID.list_values#">
                                   </cfstoredproc>  
                         <cfcatch type="any" >
                            <cfset getProgCodeFromAppealCode = "#cfcatch.SQLState#">
                         </cfcatch>
                     </cftry>    
                 <!--- <cfset queryFromCLOB =     " select  lpad(vals,10,'0') id_number from tempIdlists "> --->
                 <cfset queryFromCLOB =     " select  to_number(vals) as  id_number from tempIdlists  "/>
                   
                   
          </cfif>      
           <cfif #isSolicit# eq "Yes">
                     <cfif typeOfSolicit eq "email">
                    <cfset typecode = "'EZZ','TEZ','TZZ','ZZZ' ">
               <cfelseif typeOfSolicit eq "postal">
                    <cfset typecode = "'MZZ','TMZ','TZZ','ZZZ' ">
               <cfelseif typeOfSolicit eq "telemarketing">
                    <cfset typecode = "'PZZ','TPZ','TZZ','ZZZ' ">
               </cfif>
               <cfquery name="getProgCodeFromAppealCode"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                   select program_code from advance.tms_program
                    where program_code in (select program_code from advance.appeal_header  where appeal_code in ('#appealOrProgCode#'))
                </cfquery>
                <cfset programCode = "#getProgCodeFromAppealCode.program_code#">
           <cfelse>
                       <cfif typeOfSolicit eq "email">
                    <cfset typecode = "'EZZ','ZZZ' ">
               <cfelseif typeOfSolicit eq "postal">
                    <cfset typecode = "'MZZ','ZZZ' ">
               <cfelseif typeOfSolicit eq "telemarketing">
                    <cfset typecode = "'PZZ','ZZZ' ">
               </cfif>    
               <cfset programCode = "#appealOrProgCode#">
          </cfif>  
               
        <cfset queryToGetIDFromSolicit = "
             select qry1.id_number from (SELECT clobqry.ID_NUMBER FROM(#queryFromCLOB#)clobqry
                                                                         union  
                          select id_number
                              from advance.mailing_list
                              where mail_list_status_code = 'A'
                              and mail_list_code in ('#trim(mailingList)#')
                              and mail_list_ctrl_code in (' ', 'I')       
                              and (advance.mailing_list.stop_dt = '00000000'
         or advance.mailing_list.stop_dt >  '#dateFormat(now(),'YYYYMMDD')#')                                                                                                    
                                                                                                   ) qry1
            minus
            SELECT distinct
                    advance.handling.id_number
                     from
                        (SELECT clobqry1.ID_NUMBER FROM(#queryFromCLOB#)clobqry1
                                                                         union  
                          select id_number
                              from advance.mailing_list
                              where mail_list_status_code = 'A'
                              and mail_list_code in ('#trim(mailingList)#')
                              and mail_list_ctrl_code in (' ', 'I')         
                                    and (advance.mailing_list.stop_dt = '00000000'
         or advance.mailing_list.stop_dt >  '#dateFormat(now(),'YYYYMMDD')#')

                                                                                                   )msu_temp_ai_coni_hnd1,  
                     advance.handling
                     where
                        msu_temp_ai_coni_hnd1.id_number = advance.handling.id_number and
                          advance.handling.hnd_status_code = 'E'
                          and advance.handling.hnd_type_code in (#preserveSingleQuotes(typecode)#)
                          and (
                               advance.handling.lift_dt = '00000000'
                               or advance.handling.lift_dt = ' '
                               or advance.handling.lift_dt > '#dateFormat(now(),'YYYYMMDD')#'
                               )
                                and
                                (
                                rtrim(advance.handling.program_code) is null
                          or advance.handling.program_code in ('#programCode#')  )
                                    "/>
                   
                           
            <cfset hiIDUnionQuery = "  union select hi_id from
                                    ART_MANUSCRIPT where id_number in(
             select qry1.id_number from (SELECT clobqry.ID_NUMBER FROM(#queryFromCLOB#)clobqry
                                                                         union  
                          select id_number
                              from advance.mailing_list
                              where mail_list_status_code = 'A'
                              and mail_list_code in ('#trim(mailingList)#')
                              and mail_list_ctrl_code in (' ', 'I')         
                                    and (advance.mailing_list.stop_dt = '00000000'
         or advance.mailing_list.stop_dt >  '#dateFormat(now(),'YYYYMMDD')#')

                                                                                                   ) qry1
            minus
                           SELECT distinct
                    advance.handling.id_number
                     from
                        (SELECT clobqry.ID_NUMBER FROM(#queryFromCLOB#)clobqry
                                                                         union  
                          select id_number
                              from advance.mailing_list
                              where mail_list_status_code = 'A'
                              and mail_list_code in ('#trim(mailingList)#')
                              and mail_list_ctrl_code in (' ', 'I')         
                                    and (advance.mailing_list.stop_dt = '00000000'
         or advance.mailing_list.stop_dt >  '#dateFormat(now(),'YYYYMMDD')#')
                                                                                
                                                                                                    )msu_temp_ai_coni_hnd1,  
                     advance.handling
                     where
                        msu_temp_ai_coni_hnd1.id_number = advance.handling.id_number and
                          advance.handling.hnd_status_code = 'E'
                          and advance.handling.hnd_type_code in (#preserveSingleQuotes(typecode)#)
                          and (
                               advance.handling.lift_dt = '00000000'
                               or advance.handling.lift_dt = ' '
                               or advance.handling.lift_dt > '#dateFormat(now(),'YYYYMMDD')#'
                               )
                                and
                                (
                                rtrim(advance.handling.program_code) is null
                          or advance.handling.program_code in ('#programCode#')  ) "/>
                     <cftry>
       
       
                 <cfquery name="resultSet"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                                           <!--- select '23' as solicit, '24' as contact, '25' as deceased, '26' as Lost from dual --->
                        SELECT                            
                                          <cfif typeOfSolicit eq "email">
                                  id_number
                                ,hi_id
                                                ,EMAIL_ADDRESS
                                                ,PREF_MAIL_NAME
                                ,PREF_NAME_SORT
                                                ,LAST_NAME
                                                ,FIRST_NAME
                                                ,JNT_SALUTATION
                           <cfelseif typeOfSolicit eq "telemarketing">
                                id_number
                                ,hi_id
                                ,ADDRC_FULL_PHONE_NUMBER
                                ,ADDRC_PHONE_AREA_CODE
                                ,ADDRC_PHONE_NUMBER
                           <cfelseif typeOfSolicit eq "postal">
                                id_number
                                ,hi_id
                                ,PREF_MAIL_NAME
                                ,PREF_NAME_SORT
                                ,JNT_SALUTATION
                                ,ADDRH_PHONE_NUMBER
                                ,ADDRC_CITY
                                ,ADDRC_COUNTRY_CODE
                                ,ADDRC_FOREIGN_CITYZIP
                                ,ADDRC_FULL_PHONE_NUMBER
                                ,ADDRC_PHONE_AREA_CODE
                                ,ADDRC_PHONE_NUMBER
                                ,ADDRC_STATE_CODE
                                ,ADDRC_STREET1
                                ,ADDRC_STREET2
                                ,ADDRC_STREET3
                                ,ADDRC_ZIPCODE                                        
                           </cfif>  
                           <cfif hiIDObj eq "true">
                                       ,SPOUSE_ID
                                ,jnt_salutation
                                ,pref_jnt_mail_name1
                                ,pref_jnt_mail_name2
                           </cfif>    
                             
                        FROM
                              ART_MANUSCRIPT
                        WHERE
                              id_number in  
                                   ((#preserveSingleQuotes(queryToGetIDFromSolicit)#)
                                    <cfif hiIDObj eq "true">
                                        <!--- #preserveSingleQuotes(hiIDUnionQuery)#) --->
                                    </cfif>
                            minus
                            select id_number
                                    from advance.mailing_list
                                    where mail_list_status_code = 'A'
                           <cfif typeOfSolicit eq "email">  
                                 and mail_list_ctrl_code in ('E','C')
                           <cfelseif typeOfSolicit eq "telemarketing">
                            and mail_list_ctrl_code in ('E')
                           <cfelseif typeOfSolicit eq "postal">  
                            and mail_list_ctrl_code in ('E','M')
                           </cfif>  
                                   )  
                </cfquery>
                <cfcatch type="any">
                <cfset error = "#cfcatch.sql#">
                <cfreturn error/>
                </cfcatch>
                </cftry>        
            <cfreturn resultSet>
      </cffunction>
</cfcomponent>
------------------------------------------END idListGenerationDAO.cfc -------------------------------------

Hope this helps to clear up the confusion.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35095869
In your first function, you are returning a query called resultSet, however, if there is an error, you are returning the SQL statement.   That is why you're getting the undefined columList, because there must be an error and you're just returning SELECT.....  instead of the query.

It's not a good idea to return multiple things like that.  I suggest that you throw the error and catch it outside the CFC.

This is the problem..

        <cfcatch type="any">
           <cfset error = "#cfcatch.sql#">
           <cfreturn error/>  <!=============
        </cfcatch>
        </cftry>        
        <cfreturn resultSet>
      </cffunction>

Try doing this for testing...


        <cfcatch type="any">
            <cfrethrow>
        </cfcatch>
        </cftry>        
        <cfreturn resultSet>
      </cffunction>

and maybe something like this for long term...
        <cfcatch type="any">
           <cfthrow message="Function XYZ failed, error: #cfcatch.message#">
        </cfcatch>
        </cftry>        
        <cfreturn resultSet>
      </cffunction>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35097755
Hm.. if you're just going to throw/rethrow the error, why bother catching it at all?  It would make more sense to let the error bubble up. So the calling page knows there's a problem. Better to do the cftry/cfcatch in the calling code.

Calling page ...
<cftry>
       <cfset result = yourObject.getIDlist(....)>

       <cfcatch type="any">
             something went wrong. handle the error
      </cfcatch>
</cftry>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35099238
That is why you're getting the undefined columList, because there must be an error and you're just returning SELECT.....  instead of the query.

Btw, gdemaria hit the nail on the head.  Some sort of error's occurring in the function. But you didn't realize anything's wrong because the code's basically swallowing the error.  That's why it's better to let the error bubble up.  Catch the error in calling page - not inside the function.  
0
 

Author Comment

by:henderxe
ID: 35104501
@gdemaria:

    Two things when trying your solution:

     1.  The code you suggested to modify in the CFC is in two different places.  Wasn't sure which one to modify, but changed them both individually, and together.

     2.  Received the following error:

----------------------------------BEGIN ERROR -------------------------------------------------------
     "[Macromedia][Oracle JDBC Driver][Oracle]ORA-01790: expression must have same datatype as corresponding expression
Extended Info       
Tag Context       R:\aihtc\ART_TEST\model\idListGeneration\idListGenerationDAO.cfc (357)
R:\aihtc\ART_TEST\views\cfm\exportReports\idListGeneration\getSolicitCount.cfm (14)
C:\inetpub\wwwroot\MachII\framework\ViewContext.cfc (107)
C:\inetpub\wwwroot\MachII\framework\EventContext.cfc (468)
C:\inetpub\wwwroot\MachII\framework\commands\ViewPageCommand.cfc (85)
C:\inetpub\wwwroot\MachII\framework\EventHandler.cfc (81)
C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (379)
C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (327)
C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (271)
C:\inetpub\wwwroot\MachII\framework\RequestHandler.cfc (201)
C:\inetpub\wwwroot\MachII\mach-ii.cfm (123)
R:\aihtc\ART_TEST\index.cfm (9)
--------------------------------------END ERROR --------------------------------------------------------
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 300 total points
ID: 35104802
Ok, good.  So that gives you the erorr and the approx line number (357)

It looks like it's around this area.  I suspect that the error is referring to the MINUS.

> expression must have same datatype as corresponding expression

the query on both sides of the MINUS have to have a data type that matches.

I tried to follow back the query from the variable "queryToGetIDFromSolicit" and got dizzy with the nested sql stored in variables.

I think the creator of these select statements did not understand JOIN statements and instead attempted to nest query within query.   You may want to consider rewriting them, I suspect they are very slow too.   If you don't have time, then you need to look at the query in the debug output to see what it is resolved and then address the problem from there..


id_number in  
           ((#preserveSingleQuotes(queryToGetIDFromSolicit)#) 
            <cfif hiIDObj eq "true">
                <!--- #preserveSingleQuotes(hiIDUnionQuery)#) --->
            </cfif> 
    minus
    select id_number
            from advance.mailing_list
            where mail_list_status_code = 'A'
   <cfif typeOfSolicit eq "email">  
         and mail_list_ctrl_code in ('E','C')
   <cfelseif typeOfSolicit eq "telemarketing"> 
    and mail_list_ctrl_code in ('E')
   <cfelseif typeOfSolicit eq "postal">  
    and mail_list_ctrl_code in ('E','M')
   </cfif>  
           )

Open in new window

0
 

Author Comment

by:henderxe
ID: 35181390
@gdemaria:

   FINALLY, figured out the solution, but would never have without your identifying the key cause of the problem, which sent me in the right direction.  The solution follows (see last three lines):

-----------------------------------------START CODE -----------------------------------------------------
<cfquery name="getIDsFromID"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                               select list_values from art_idlist where idList_id = #queryID#
                                </cfquery>
                                <cfset listID = getIDsFromID.list_values >
                                  <cfstoredproc procedure="addIdLists"    datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
                                          <cfprocparam type="In"   cfsqltype="cf_sql_varchar"  variable="id_no"  value="#getIDsFromID.list_values#">
                                   </cfstoredproc>  
                         <cfcatch type="any" >
                            <cfset getProgCodeFromAppealCode = "#cfcatch.SQLState#">
                         </cfcatch>
                     </cftry>    
             <!--- <cfset queryFromCLOB =     " select  lpad(vals,10,'0') id_number from tempIdlists "> --->

CHANGED THIS LINE FROM:
               <cfset queryFromCLOB =     " select  to_number(vals) as  id_number from tempIdlists  "/>
TO (REMOVED "TO_NUMBER"):
             <cfset queryFromCLOB =     " select  vals as  id_number from tempIdlists  "/>
ADDED THIS LINE:
             <cfset queryFromCLOB = #Replace(queryFromCLOB,"''","'","all")#>
------------------------------------- END CODE ----------------------------------------------------------------

    Thank you all, especially gdemaria for your thoughts, and expertise.  

   
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article will give core knowledge of JavaScript and will head in to your first JavaScript program. I am Durvesh Naik and I am here to deal with this series of JavaScript. I will teach you JavaScript in part wise , as its quite boring to read big…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now