databaseManager.getSQL() sometimes returns invalid SQL.

Classic Servoy
1

All,

Due to a performance issue (case #243280) with databaseManager.getFoundSetDataProviderAsArray() I had to re-implement it myself. At first I thought I had a working solution:

function getFoundSetDataProviderAsArray()
{
  var foundset = arguments[0];
  var field = arguments[1];
  
  var set = databaseManager.getDataSetByQuery(
    foundset.getServerName(),
    "select " + field + " from (" + databaseManager.getSQL(foundset) + ")",
    databaseManager.getSQLParameters(foundset),
    -1);
  return set.getColumnAsArray(1);
}

I just discovered though, that in some cases what’s returned by the databaseManager.getSQL() call is not valid SQL. Or rather, the SQL syntax is valid but it’s referencing a temporary table that apparently no longer exists. Here’s one of the problematic SQL queries being returned:

select kti_line_id from (select KTI_LINE_ID from KV_SEIS_LINE_LIST inner join TEMP_3118245000 TEMP31182450002459 on KTI_LINE_ID=TEMP31182450002459.KTI_LINE_ID_3118245002 order by LINE_NAME asc)

And the error:

java.sql.SQLException: ORA-00942: table or view does not exist
     at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
     at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
     at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
     at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
     at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
     at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1202)
     at sun.reflect.GeneratedMethodAccessor421.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:585)
     at com.servoy.j2db.persistence.datasource.Ze.invoke(Ze.java:9)
     at $Proxy3.executeQuery(Unknown Source)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
     at com.servoy.j2db.dataprocessing.SQLEngine.performSelect(SQLEngine.java:26)
     at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:461)
     at com.servoy.j2db.dataprocessing.SQLEngine.performCustomQuery(SQLEngine.java:706)
     at com.servoy.j2db.dataprocessing.Zgb.performCustomQuery(Zgb.java:23)
     at com.servoy.j2db.dataprocessing.Zrb.Za(Zrb.java:346)
     at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_getDataSetByQuery(JSDatabaseManager.java:66)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:585)
     at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:179)
     at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:347)
     at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3666)
     at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2680)
     at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:166)
     at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:387)
     at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3125)
     at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:165)
     at com.kelman.iglass.gis.servoy.BehaviorProvider.processCallback(BehaviorProvider.java:96)
     at com.kelman.iglass.gis.servoy.BehaviorProvider.respond(BehaviorProvider.java:61)
     at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:297)
     at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:104)
     at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:91)
     at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1248)
     at org.apache.wicket.RequestCycle.step(RequestCycle.java:1325)
     at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1427)
     at org.apache.wicket.RequestCycle.request(RequestCycle.java:532)
     at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:356)
     at org.apache.wicket.protocol.http.WicketServlet.doPost(WicketServlet.java:145)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
     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:230)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:563)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
     at java.lang.Thread.run(Thread.java:595)

I’m running Servoy v4.1.4 i1 (build 682). The database is Oracle 10g.

Thanks,
Corey

2

Corey,

In some cases, Servoy cannot get the data it needs from the database in a single select statement.
For those cases a temporary table is created, filled and dropped, by the time dbmgr.getSQL() returns the select part, the temp table is already dropped.

Those cases are:

  • a select or update with more than 200 pks (the sql string would get too long)
  • a pk in-condition with a subselect in case of a composite PK

Rob

3

Rob,

Thanks for the explanation but can you give me any hints as to how to solve this problem?

Regardless of the explanation, to me (and presumably anyone else trying to use databaseManger.getSQL()) it’s a bug that the SQL being returned isn’t usable in all cases. How can I reliably program around this limitation?

Thanks,
Corey

4

Corey,

We have optimized databaseManager.getFoundSetDataProviderAsArray() to get the data in 1 go if possible.
This way you do not run into this limitation.

Added in release 5.

Rob

5

Rob,

Thanks a lot for addressing the performance issue. That removes my immediate need for getSQL().

That said, is it still on the radar to enhance getSQL() so what’s returned is always a usable SQL statement? Perhaps instead of temporary tables, permanent tables could be used with records tied to a sessionid that are cleared at the end of the session? It’s more housekeeping work but there are many valid use-cases for this method. If not, I think it should be removed because it can cause code to break rather unexpectedly.

Thanks,
Corey