Load Records Behaviour`

Zuke · May 17, 2011, 12:17pm

Hi

When using the following code in the on load of one of my forms, the load records seems to trigger the generation of queries that are building temp tables(servoy generated queries)

var fabricSQL = databaseManager.getSQL(forms.fabman_frm_detail_search.foundset,true)
		var fabricParams = databaseManager.getSQLParameters(forms.fabman_frm_detail_search.foundset,true)
		fabricSQL = fabricSQL.substr(0,fabricSQL.search('order by'))
		//var useDate = getdate()
		var useDateToday = new Date()
		useDateToday = Date.parse(useDateToday).toString("yyyy-MM-dd HH:mm:ss")
		//application.output(useDateToday)
	var showSQL = "select fc_id from fabric_condition where fc_spare4 < '"+useDateToday+"' and fc_spare4 IS NOT NULL and fc_id IN ("+fabricSQL+")"
		
	var ds = databaseManager.getDataSetByQuery(globals.vServerToUse,showSQL,fabricParams,-1)
	foundset.loadRecords(ds)

In this instance it takes 5 mins plus to run, it just churns away running insert statements in the background.

see below from the servor performance log

05:47:011	81138	00:00:004	Load foundset	insert into TEMP_5632466399 (fc_id_5632466401) values (?)
01:11:996	12273	00:00:005	Load foundset	insert into TEMP_5632466390 (dr_id_5632466392) values (?)
01:07:233	12273	00:00:005	Load foundset	insert into TEMP_5632466393 (dr_id_5632466395) values (?)

The counts are huge and im not sure why its running the insert anyway.

Could anyone else shed any light on this for us?.

servoydevguy · May 17, 2011, 12:29pm

Unless something has changed (I don’t think it has) there is no foundset in onLoad. Trying moving the code to the onShow.

function onShow(_firstShow,_event) {
   if (_firstShow) {
            <your code here>
   }
}

Zuke · May 17, 2011, 1:48pm

Hi Iv tried that thanks and we are’nt seeing the miultiple inserts anymore.

However the load records is still taking forever to run.

The query im using in the foundset.loadrecords only takes seconds to run in Sql Server management Studio, why would this not translate to Servoy?

servoydevguy · May 17, 2011, 2:00pm

You might look and see what is happening in the Performance page and if that doesn’t shed any light then turn tracing on in the Server Log page. Refresh and see what is being processed.

Zuke · May 17, 2011, 2:21pm

Hi Again

We have another strange error within a different form, again with load records

We use the following code

	var fabricSQL = databaseManager.getSQL(forms.fabman_frm_detail_search.foundset,true)
	var fabricParams = databaseManager.getSQLParameters(forms.fabman_frm_detail_search.foundset,true)
	fabricSQL = fabricSQL.substr(0,fabricSQL.search('order by'))

	var vSql =  "select fp_id from fabric_system_paint where fp_id in(select a.fp_id "+
						"from fabric_system_paint a, fabric_condition b "+
						"where a.fp_system_code = b.fp_paint_fabric_system1 "+
						"or a.fp_system_code = b.fp_paint_fabric_system2 "+
						"or a.fp_system_code = b.fp_paint_fabric_system3 "+
						"AND b.fc_id IN ("+fabricSQL+"))"
	
	var data = databaseManager.getDataSetByQuery(globals.vServerToUse,vSql,fabricParams,-1)

	foundset.loadRecords(vSql,fabricParams)

And we get an error on the line

foundset.loadRecords(vSql,fabricParams)

which is

Wrapped com.servoy.j2db.dataprocessing.DataException: The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. (/home/mac/FinalWork/tci_excorr_01/forms/fabric_rpt_paint_spec.js#66)

Looking at the servoy log we get this just before it errors out

2011-05-17 15:08	AWT-EventQueue-0	ERROR	com.servoy.j2db.util.Debug	insert into TEMP_5636164446 (fp_id_5636164448, fp_system_code_5636164449) select fp_id from fabric_system_paint where fp_id in(select a.fp_id from fabric_system_paint a, fabric_condition b where a.fp_system_code = b.fp_paint_fabric_system1 or a.fp_system_code = b.fp_paint_fabric_system2 or a.fp_system_code = b.fp_paint_fabric_system3 AND b.fc_id IN (select fabriccondition610.fc_id from fabric_condition fabriccondition610 where fabriccondition610.fc_id in (select fc_id from fabric_condition where fc_id IN (Select a.fc_id from fabric_condition a, analysis_codes b where a.ad_analysis_code = b.ad_analysis_code and b.ad_analysis_code IN (22)) AND fc_id IN (SELECT fc_id From fabric_condition where cd_id in (SELECT component_detail.cd_id FROM component_detail, fabric_condition Where fabric_condition.cd_id = component_detail.cd_id AND component_detail.cd_plant = 'DFA')) AND fc_id IN (SELECT fc_id FROM fabric_condition WHERE fc_marked_deleted != 1 OR fc_marked_deleted IS NULL)) )) parameters: <null>

It looks as if servoy is making a mistake when building its temp tables

Any thoughts?

servoydevguy · May 17, 2011, 2:40pm

I didn’t dissect everything, but you might first try using var _ds = databaseManager.getDatasetByQuery(_query,_args). Then use that dataset of PK’s to load your form data (example: foundset.loadRecords(_ds)

The loadRecords() seems to work best with simple select statements from my experience.

Zuke · May 17, 2011, 2:55pm

We use that quite frequently throughout our dev work

Thats mainly why im stumped with this.

When trying to use a dataset with the query i posted earlier, we get the following error

Wrapped java.lang.ArrayIndexOutOfBoundsException:

??

servoydevguy · May 17, 2011, 3:17pm

Ah. One of those nice vague Java errors. I would say that is a good time to put a case into the Support System. :wink: Especially if you verified that the SQL statement/params run in SQL Management Studio without an issue. You might try running it in the SQL Explorer inside of Servoy too so it is being executed using the JDBC driver.

rgansevles · May 17, 2011, 6:14pm

McCourt,

The error ‘The select list for the INSERT statement contains fewer items than the insert list’ is caused by a mismatch between the pks in the table of your foundset and the sql you use.
The table has a composite (2 columns) pk and your sql selects only 1 column.

I would advise to go away from all this sql, better use the power of Servoy with relations.
You may also want to have a look at databaseManager.convertFoundSet() to get a foundset over a relation with all related records.

Rob

rgansevles · May 18, 2011, 7:02am

Zuke:
When trying to use a dataset with the query i posted earlier, we get the following error
Wrapped java.lang.ArrayIndexOutOfBoundsException:

Do you have the full stacktrace?

Rob

Zuke · May 18, 2011, 8:14am

Here you go

org.mozilla.javascript.WrappedException: Wrapped java.lang.ArrayIndexOutOfBoundsException: 1 (/home/mac/FinalWork/tci_excorr_01/forms/sandbox.js#461) 
    	at org.mozilla.javascript.Context.throwAsScriptRuntimeEx(Context.java:1828) 
    	at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:199) 
    	at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:353) 
    	at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3666) 
    	at script.onShow1(/home/mac/FinalWork/tci_excorr_01/forms/sandbox.js:461) 
    	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:3135) 
    	at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:165) 
    	at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:476) 
    	at com.servoy.j2db.debug.RemoteDebugScriptEngine.executeFunction(RemoteDebugScriptEngine.java:382) 
    	at com.servoy.j2db.FormController.executeFunction(FormController.java:3977) 
    	at com.servoy.j2db.FormController.executeFormMethod(FormController.java:4291) 
    	at com.servoy.j2db.FormController.executeOnShowMethod(FormController.java:4170) 
    	at com.servoy.j2db.FormController.access$15(FormController.java:4161) 
    	at com.servoy.j2db.FormController$3.run(FormController.java:3040) 
    	at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209) 
    	at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:642) 
    	at java.awt.EventQueue.access$000(EventQueue.java:85) 
    	at java.awt.EventQueue$1.run(EventQueue.java:603) 
    	at java.awt.EventQueue$1.run(EventQueue.java:601) 
    	at java.security.AccessController.doPrivileged(Native Method) 
    	at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87) 
    	at java.awt.EventQueue.dispatchEvent(EventQueue.java:612) 
    	at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269) 
    	at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184) 
    	at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174) 
    	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169) 
    	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161) 
    	at java.awt.EventDispatchThread.run(EventDispatchThread.java:122) 
    Caused by: java.lang.ArrayIndexOutOfBoundsException: 1 
    	at com.servoy.j2db.dataprocessing.FoundSet.loadExternalPKList(FoundSet.java:1505) 
    	at com.servoy.j2db.dataprocessing.FoundSet.js_loadRecords(FoundSet.java:974) 
    	at sun.reflect.GeneratedMethodAccessor602.invoke(Unknown Source) 
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 
    	at java.lang.reflect.Method.invoke(Method.java:597) 
    	at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:179) 
    	... 28 more

rgansevles · May 18, 2011, 8:40am

This is a similar error as the ‘The select list for the INSERT statement contains fewer items than the insert list’ error.
You pass in a data set with 1 column, your table has a 2-column composite pk.

Rob

Zuke · May 18, 2011, 9:04am

Hi Rob

I can confirm

We had a rogue PK in one of our tables, an oversight from when we rebuilt our backend I think.

Thanks for the response, that was turning into a head scratcher.

Regards

Topic		Replies	Views
Simple 'loadRecords' failing with... Classic Servoy	22	17783	April 13, 2010
loadRecords(sql) returns different data to SQL Code?? Classic Servoy	38	19947	August 27, 2010
Performance on load records Classic Servoy	5	1933	October 4, 2011
Huge table - slow loading Classic Servoy	16	11154	January 2, 2011
loadrecords Classic Servoy	1	892	November 1, 2011

Load Records Behaviour`

Related topics