Can't sort 200+ records

maria · December 3, 2010, 2:24am

Hi Guys,

I’m trying to sort a foundset when it has more than 200 records and I come up with an exception saying: com.servoy.j2db.dataprocessing.DataException: Can’t find column: SERVCARDID_1342555544 in table TEMP_1342555542.
I never had table TEMP_whatevernumbers. Also SERVCARDID_78967896786 does not exist, it’s just ‘servcardid’ really.
Supposedly Servoy builds internal queries of its own and executes them, I’m fine with that, of course.
But I cannot run foundset.sort(condition); and automatic sort via clicking on the table header throws the same exception in the server log.

2010-12-03 13:17 	http-8080-7 	ERROR 	com.servoy.j2db.util.Debug 	Throwable
com.servoy.j2db.dataprocessing.DataException: Can't find column: SERVCARDID_1342555610 in table TEMP_1342555608
     at com.servoy.j2db.dblayer.Zb.Za(Zb.java:189)
     at com.servoy.j2db.persistence.Server.translateSQLException(Server.java:663)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:933)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:982)
     at com.servoy.j2db.dataprocessing.FoundSet.sort(FoundSet.java:1252)
     at com.servoy.j2db.server.headlessclient.dataui.Zzh.Za(Zzh.java:22)
     at com.servoy.j2db.server.headlessclient.dataui.Zvf.onClick(Zvf.java:41)
     at com.servoy.j2db.server.headlessclient.dataui.Zud.onEvent(Zud.java:6)
     at org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
     at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:286)
     at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:119)
     at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
     at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1250)
     at org.apache.wicket.RequestCycle.step(RequestCycle.java:1329)
     at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1428)
     at org.apache.wicket.RequestCycle.request(RequestCycle.java:545)
     at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:479)
     at com.servoy.j2db.server.servlets.Zk.doGet(Zk.java:4)
     at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Unknown Source)

2010-12-03 13:17 	http-8080-7 	ERROR 	com.servoy.j2db.util.Debug 	insert into TEMP_1342555608 (SERVCARDID_1342555610) values (?) parameters: [20001 ,type: java.lang.Long]
java.sql.SQLException: Can't find column: SERVCARDID_1342555610 in table TEMP_1342555608
     at com.hxtt.global.SQLState.SQLException(Unknown Source)
     at com.hxtt.sql.di.a(Unknown Source)
     at com.hxtt.sql.bp.a(Unknown Source)
     at com.hxtt.sql.bp.a(Unknown Source)
     at com.hxtt.sql.ah.a(Unknown Source)
     at com.hxtt.sql.dk.case(Unknown Source)
     at com.hxtt.sql.dk.executeUpdate(Unknown Source)
     at sun.reflect.GeneratedMethodAccessor688.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
     at java.lang.reflect.Method.invoke(Unknown Source)
     at com.servoy.j2db.persistence.datasource.Zf.invoke(Zf.java:1)
     at $Proxy1.executeUpdate(Unknown Source)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:207)
     at com.servoy.j2db.dataprocessing.SQLEngine.performUpdate(SQLEngine.java:143)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:352)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:982)
     at com.servoy.j2db.dataprocessing.FoundSet.sort(FoundSet.java:1252)
     at com.servoy.j2db.server.headlessclient.dataui.Zzh.Za(Zzh.java:22)
     at com.servoy.j2db.server.headlessclient.dataui.Zvf.onClick(Zvf.java:41)
     at com.servoy.j2db.server.headlessclient.dataui.Zud.onEvent(Zud.java:6)
     at org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
     at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:286)
     at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:119)
     at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
     at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1250)
     at org.apache.wicket.RequestCycle.step(RequestCycle.java:1329)
     at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1428)
     at org.apache.wicket.RequestCycle.request(RequestCycle.java:545)
     at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:479)
     at com.servoy.j2db.server.servlets.Zk.doGet(Zk.java:4)
     at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Unknown Source)

Sorting with less than 200 records is just fine.

I tried to work around it and load records with my own query: ‘SELECT servcardid FROM servcard WHERE servcardid IN (1,2,3,…6000)’ with or without ‘ORDER BY customerid desc’
It sort of loads the records but it’s always only 201 record and I can’t get any more even by databaseManager.getFoundSetCount(foundset).
In fact the last command returns 11 which is bs.

Cheers,
Maria

rgansevles · December 3, 2010, 10:41am

Maria,

When you load a foundset by pk dataset that has more than 200 rows, Servoy will not generate a 'where pk in (?, …, ?) clause, but instead the pks will be stored in a temp table and it will be used in a join.
The SERVCARDID_1342555610 column is just an alias we generate to make sure column names are unique.

The sorting works transparently with the temp table, it is also cleaned up afterwards.

One limitation exists, if you use databasemanager.getSQL(foundset), this will contain a ref to the temp table, but when you run it later, the temp table is gone.

Do you use this method?

Rob

maria · December 6, 2010, 12:39am

rgansevles:
Maria,

When you load a foundset by pk dataset that has more than 200 rows, Servoy will not generate a 'where pk in (?, …, ?) clause, but instead the pks will be stored in a temp table and it will be used in a join.
The SERVCARDID_1342555610 column is just an alias we generate to make sure column names are unique.

The sorting works transparently with the temp table, it is also cleaned up afterwards.

One limitation exists, if you use databasemanager.getSQL(foundset), this will contain a ref to the temp table, but when you run it later, the temp table is gone.

Do you use this method?

Rob

Hi Rob,

I used this method to debug the problem and that’s where I found that temp table. Normally I don’t execute this method.
Sean suggested that I make a case already which I will do asap.

Cheers,
Maria

rgansevles · December 6, 2010, 7:52am

Maria,

Please attach a small sample solution to your case, so we can investigate.

Rob

maria · January 27, 2011, 2:33am

rgansevles:
Maria,

Please attach a small sample solution to your case, so we can investigate.

Rob

Ok, Rob.
Please find the sample solution attached.
It’s not only about sorting but also loading records in the foundset.
It has three buttons to load the foundset with a query, dataset and loadAllRecords();
The query is most basic: select all rows from the form table.
Pay attention that the dataset and loadAllRecords() work find and the databaseManager.getFoundSetCount(foundset) displays the correct number of records.
However if records are loaded with a query then the foundset count is 11. It’s always 11, even if I have a different data in my application.
Also, loading records with a dataset does not work in my real solution, though it looks ok in the test solution attached.

The other thing that doesn’t work is when you click Sort right after loading records with a dataset. See full error details in console.

Please help, guys, this problem has been there forever.

Cheers,
Maria

rgansevles · January 28, 2011, 7:31am

Maria,

Please create a case in our support system with the sample attached so we can prioritize.

Thanks,
Rob

maria · February 1, 2011, 3:18am

rgansevles:
Maria,

Please create a case in our support system with the sample attached so we can prioritize.

Thanks,
Rob

Case ID 353089.

Cheers,
Maria

Topic		Replies	Views
Sorting more than 2000 records? Classic Servoy	9	5020	March 30, 2007
Next Record bizzareness at 200, 400, 600...? Classic Servoy	1	5523	June 15, 2004
bug foundset sort Classic Servoy	3	1495	April 5, 2012
Sorting a foundset with a calculation field Classic Servoy	28	19264	November 7, 2011
Sorting and looping Classic Servoy	4	2319	April 9, 2009

Can't sort 200+ records

Related topics