2 Replies Latest reply on Sep 22, 2009 3:28 PM by Adam Cameron.

    CF8 ListAppend and ListGetAt problem

    Neldarin

      Hello everyone,

       

      I'm using ColdFusion 8 and I encounter an issue usint the ListAppend and ListGetAt.

       

      My code is the following:

      <cfquery name="myProject" datasource="login">
                                  SELECT usersreports.USERID, usersreports.REPORTID, reports.ID
                                  FROM usersreports, reports
                                  WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                              </cfquery>
                             
                              <cfset projectNames = "">
                              <cfset projectLinks = "">
                              <cfloop query="myProject">
                                  <cfquery name="projects" datasource="login">
                                      SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, reports.NAME AS NAMES, reports.URL AS URL
                                      FROM usersreports, reports
                                      WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                                  </cfquery>
                                  <cfset projectNames = ListAppend(projectNames, projects.NAMES)>
                                  <cfset projectLinks = ListAppend(projectLinks, projects.URL)>
                              </cfloop>
                             
                              <cfoutput>
                                  <cfset i = 0>
                                  <cfloop index="i" from="1" to="#projects.RecordCount#">
                                      <cfset projectNames = #ListGetAt(projectNames, i)#>
                                      #projectNames#
                                  </cfloop>
                              </cfoutput>

       

      With that code I get the following error and I try to fix it in some different ways but I didn't get yet.

      Error Executing Database Query.

      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'USERID' as part of an aggregate function.
      The error occurred in C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm: line 179
      177 :                                 SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, COUNT(reports.NAME) AS NAMES, reports.URL AS URL
      178 :                                 FROM usersreports, reports
      179 :                                 WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
      180 :                             </cfquery>
      181 :                             <cfset projectNames = ListAppend(projectNames, projects.NAMES)>

      SQLSTATE  42000
      SQL    SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, COUNT(reports.NAME) AS NAMES, reports.URL AS URL FROM usersreports, reports WHERE usersreports.USERID = 174 AND reports.ID = usersreports.REPORTID
      VENDORERRORCODE  -3011
      DATASOURCE  login
      Resources:

       

      Browser  Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3
      Remote Address  ::1
      Referrer  http://localhost/Gaither%20Suite/gsdbank.cfm
      Date/Time  21-Sep-09 11:07 PM
      Stack Trace
      at cfgsproject2ecfm122092148.runPage(C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm:179)                                                                                   at cfgsproject2ecfm122092148.runPage(C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm:179)                                 

      java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'USERID' as part of an aggregate function.
           at macromedia.sequelink.ssp.Diagnostic.toSQLException(Unknown Source)
           at macromedia.sequelink.ssp.Chain.cnvDiagnostics(Unknown Source)
           at macromedia.sequelink.ssp.Chain.decodeDiagnostic(Unknown Source)
           at macromedia.sequelink.ssp.Chain.decodeBody(Unknown Source)
           at macromedia.sequelink.ssp.Chain.decode(Unknown Source)
           at macromedia.sequelink.ssp.Chain.send(Unknown Source)
           at macromedia.sequelink.ctxt.stmt.StatementContext.execDirect(Unknown Source)
           at macromedia.jdbc.sequelink.SequeLinkImplStatement.execute(Unknown Source)
           at macromedia.jdbc.slbase.BaseStatement.commonExecute(Unknown Source)
           at macromedia.jdbc.slbase.BaseStatement.executeInternal(Unknown Source)
           at macromedia.jdbc.slbase.BaseStatement.execute(Unknown Source)
           at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:254)
           at coldfusion.sql.Executive.executeQuery(Executive.java:1243)
           at coldfusion.sql.Executive.executeQuery(Executive.java:1008)
           at coldfusion.sql.Executive.executeQuery(Executive.java:939)
           at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325)
           at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831)
           at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521)
           at cfgsproject2ecfm122092148.runPage(C:\inetpub\wwwroot\Gaither Suite\gsproject.cfm:179)
           at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:196)
           at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:370)
           at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
           at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:279)
           at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
           at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
           at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
           at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
           at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
           at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
           at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
           at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
           at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
           at coldfusion.CfmServlet.service(CfmServlet.java:175)
           at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
           at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
           at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
           at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
           at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
           at jrun.servlet.FilterChain.service(FilterChain.java:101)
           at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
           at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
           at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)
           at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)
           at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)
           at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
           at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
           at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
           at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

      Any recomendation to fix it?

      Thanks for your help.

        • 1. Re: CF8 ListAppend and ListGetAt problem
          Neldarin Level 1

          <cfquery name="myProject" datasource="login">
                                      SELECT usersreports.USERID, usersreports.REPORTID, reports.ID
                                      FROM usersreports, reports
                                      WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                                  </cfquery>
                                 
                                  <cfset projectNames = "">
                                  <cfset projectLinks = "">
                                  <cfloop index="i" from="1" to="#myProject.recordCount#">
                                      <cfquery name="projects" datasource="login">
                                          SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, reports.NAME AS NAMES, reports.URL AS URL
                                          FROM usersreports, reports
                                          WHERE usersreports.USERID = #client.uid# AND reports.ID = usersreports.REPORTID
                                      </cfquery>
                                      <cfset projectNames = ListAppend(projectNames, #projects.NAMES[i]#)>
                                      <cfset projectLinks = ListAppend(projectLinks, #projects.URL[i]#)>
                                  </cfloop>
                                 
                                  <cfoutput>
                                      <cfloop index="i" from="1" to="#ListLen(projectNames)#">
                                          <a href="#ListGetAt(projectLinks, i)#" target="_blank">#ListGetAt(projectNames, i)#</a><br />
                                      </cfloop>
                                  </cfoutput>

          • 2. Re: CF8 ListAppend and ListGetAt problem
            Adam Cameron. Level 5

            That's a DB error, so it's got nothing to do with listAppend() or listGetAt().  Also the query that is reported as erroring is not one that you show in the code snippet you provide.

             

            SELECT usersreports.USERID, usersreports.REPORTID, reports.ID, COUNT(reports.NAME) AS NAMES, reports.URL AS URL FROM usersreports, reports WHERE usersreports.USERID = 174 AND reports.ID = usersreports.REPORTID

             

            The error itself is fairly self-explanatory:

             

            You tried to execute a query that does not include the specified expression 'USERID' as part of an aggregate function.

             

            If you're using aggregate functions, then all the columns that aren't being aggregated need to be grouped.  Look this up in your SQL book ("group by" or "count").

             

            As for the code you posted, I can't help but think that you should be able to combine the two queries into one single one, to avoid the querying in a loop, which is a real performance killer.  One should minimise the number of times one hits the DB in a given operation.

             

            Also: ditch MS Access if at all possible.  It is not fit for purpose as being the backend for a web site.  It's a desktop application, not a server application.

             

            --

            Adam