Can't sort 200+ records

[maria]

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.

Code: Select all

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]

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]

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]

Maria,

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

Rob

[maria]

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]

Maria,

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

Thanks,
Rob

[maria]

Maria,

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

Thanks,
Rob

Case ID 353089.

Cheers,
Maria