Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Oct 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation MySQL works, JDBC does not - column not found? Strange

    I'm using JDBC MySQL Java Connector 5.1 on NetBeans.

    The MySQL query on PHPMyAdmin works, but somehow when I use the exact same thing in JDBC, I get column not found.

    The column referred to should be quite straightforward as it is defined in the SQL schema itself.

    I have tried everything and it still returns column not found error.

    Here is the MySQL query.

    Code:
    SELECT semName, SUM( countPerLocId ) AS heatmapCount
    FROM (
    
    SELECT semName, userlocFiltered.locId, countPerLocId
    FROM (
    
    SELECT userloc.locId, COUNT( DISTINCT userloc.mac ) AS countPerLocId
    FROM userloc
    INNER JOIN (
    
    SELECT mac, MAX( TIMESTAMP ) AS mostRecentTime
    FROM userloc
    WHERE TIMESTAMP >=  '2014-03-24 02:00:00'
    AND TIMESTAMP <=  '2014-03-24 02:15:00'
    GROUP BY mac
    ) AS pairing ON userloc.timestamp = pairing.mostRecentTime
    GROUP BY userloc.mac
    ) AS userlocFiltered
    INNER JOIN semplace ON userlocFiltered.locId = semplace.locId
    ) AS result
    GROUP BY semName;
    And here is the JDBC prepared statement:

    Code:
    sql = "SELECT semName, SUM(countPerLocId) AS heatmapCount FROM"
                        + "(SELECT semName, userlocFiltered.locId, countPerLocId FROM"
                        + "(SELECT userloc.locId, COUNT(DISTINCT userloc.mac) AS countPerLocId FROM userloc INNER JOIN"
                        + "(SELECT mac, MAX(timestamp) AS mostRecentTime FROM userloc WHERE timestamp > ? AND timestamp <= ? GROUP BY mac)"
                        + "AS pairing ON userloc.timestamp = pairing.mostRecentTime GROUP BY userloc.mac)"
                        + "AS userlocFiltered INNER JOIN semplace ON userlocFiltered.locId=semplace.locId)"
                        + "AS result GROUP BY semName";
    I don't know why it does not work. I have tried various methods such as pairing the timestamp column with identifiers and so on, but it still returns the same error. Here is the stack trace:

    Code:
    java.sql.SQLException: Column 'timestamp' not found.
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    	at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167)
    	at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6230)
    	at sloca.dao.UserLocDAO.heatmapAllByTimeWindow(UserLocDAO.java:105)
    	at sloca.controller.HeatmapController.heatmapByFloor(HeatmapController.java:50)
    	at org.apache.jsp.processHeatmap_jsp._jspService(processHeatmap_jsp.java:97)
    	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
    	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    	at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
    	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    	at java.lang.Thread.run(Thread.java:744)
    Any help? Thanks!

  • #2
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,901
    Thanks
    46
    Thanked 202 Times in 201 Posts
    does this
    sql = "SELECT semName, SUM(countPerLocId) AS heatmapCount FROM"
    + "(SELECT semName, userlocFiltered.locId, countPerLocId FROM"
    + "(SELECT userloc.locId, COUNT(DISTINCT userloc.mac) AS countPerLocId FROM userloc INNER JOIN"
    + "(SELECT mac, MAX(timestamp) AS mostRecentTime FROM userloc WHERE timestamp > ? AND timestamp <= ? GROUP BY mac)"
    + "AS pairing ON userloc.timestamp = pairing.mostRecentTime GROUP BY userloc.mac)"
    + "AS userlocFiltered INNER JOIN semplace ON userlocFiltered.locId=semplace.locId)"
    + "AS result GROUP BY semName";
    Assemble to be:
    Code:
    "SELECT semName, SUM(countPerLocId) AS heatmapCount FROM(SELECT semName, userlocFiltered.locId, countPerLocId FROM(SELECT userloc.locId, COUNT(DISTINCT userloc.mac) AS countPerLocId FROM userloc INNER JOIN(SELECT mac, MAX(timestamp) AS mostRecentTime FROM userloc WHERE timestamp > ? AND timestamp <= ? GROUP BY mac)AS pairing ON userloc.timestamp = pairing.mostRecentTime GROUP BY userloc.mac)AS userlocFiltered INNER JOIN semplace ON userlocFiltered.locId=semplace.locId)AS result GROUP BY semName"
    Note how there are no spaces in your concatenation- Is that MySQL Query ok?

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •