Simple 'loadRecords' failing with...

Classic Servoy
1

java.lang.ArrayIndexOutOfBoundsException: 1

Basically I am running a query where, if successful, it will just find one record and then load the resulting dataset into the form. At every point I see that the PK I am loading ‘looks’ normal. I’ve also tried changing the dataset into an array and then back into a dataset. This is something I do time and again. I have other solutions running on this same server using these same type of methods without any problem. This is driving me nuts!! :)

Simplified the code looks like this:

	var query = "select DISTINCT ONC_REL_ID " +
				    "FROM JOHN_PROD.ONC_RELEASE " +
				    "WHERE MEDREC = ?";
	var dataset = databaseManager.getDataSetByQuery('onc_patient', query, args, maxReturnedRows);
	if(dataset.getMaxRowIndex() == 1)
	{
		var dataArray = dataset.getColumnAsArray(1);
		var pks = databaseManager.convertToDataSet(dataArray)
		controller.loadRecords(dataset)

It doesn’t matter if I load the ‘pks’ or the dataset I still get the same ‘ArrayIndexOutOfBounds’ error… The ‘dataArray’ variable looks like this ‘[15833]’, i.e. just a normal PK. The rest of the error log has this:

at com.servoy.j2db.dataprocessing.FoundSet.loadExternalPKList(Unknown Source)
at com.servoy.j2db.FormController.loadData(Unknown Source)
at com.servoy.j2db.FormController$JSForm.js_loadRecords(Unknown Source)

If I do a ‘Find’ on that form based on the MEDREC as above, it works without any problem but for various reasons I can’t do that. Besides it is just so illogical that it isn’t working. This is just a small solution I am doing (5 or 6 forms) that I want to complete a project before, hopefully, moving all the solutions over to Servoy 5.1. Any help gratefully received. Could it be related to a Java update? This is running Servoy 3.5.10 on an XP machine that had trouble a couple of weeks ago because of that Java release and Johan gave out a jar update to fix it. It is running Java version 1.6.0_18.

2

this code should work fine
I will see if i can reproduce it.

But why are you first getting the dataset out of the query
then converting (getColumnAsArray) it to an array (but as far as i can see that dataset already just had one array)
and then convert that array back to a dataset… (convertToDataSet)

if i look at that code the end result should be that the “pks” dataset and the “datasset” dataset are the same thing…

But then in your sample code you do use the dataset in the controller.loadRecords… so why create the pks datasset?
Or is this example not really complete or right?

3

Sorry I just confused matters with the ‘conversion’ to
an array. I was trying to make it clear that I just made
the array to view in the debugger the actual value that
was being passed to loadRecords. Just to test it I used
the converted array once to see if that would work.
I left it in my post just to show that I had tried it.

I use this type of method all the time, this is a very simple
new method in a new solution that uses this technique
and doesn’t work for some strange reason. Yet doing a
regular ‘find’ on that form that should be doing the same
thing DOES work.

4

this does resembles something we already fixed.
But we have to see if this is really that specific case.

5

Hi Johan

We also have problems with loadRecords(dataset). If the dataset is empty, the result is always wrong (either the previous records are still loaded or an unpredictable set of records will be loaded).

You mentioned that you fixed a bug similar to the one mentioned in this topic. May it be our bug? Or do we have to rewrite our code since loadRecord(query) seems to work fine.

I’ll appreciate your answer a lot. Regards
Birgit

6

I don’t know if this helps or not but after reading Birgit’s post I thought I should try doing the load as query and had kind of an interesting result… I don’t normally use load as query as sometimes the limitations of it don’t work for what I’m trying to do but thought it would be fun to try it here. I tried it both with arguments and as well as hard coding the parameter and in every case got the same error but this time it was an Oracle error.

Without the arguments the code was simply this:

	var query = 'SELECT onc_rel_id FROM onc_release WHERE medrec = 13745'
	forms.frm_onc_release.controller.loadRecords(query)

The error was ORA-00947 or not enough values… The query itself of course runs fine when I run it outside of Servoy. So kind of interesting what is going on here

7

John,

Does the table of for frm_onc_release have a combined pk?
If I try that I can reproduce the error.

Rob

8

Bingo. It wasn’t quite that (or I should say I didn’t mean it as a ‘combined PK’) but that told me where to look. ONC_REL_ID is the true PK for that table. However at one point or another I listed the MEDREC column within SERVOY as a ‘row_ident’. I forget exactly how or when that came about but switching MEDREC off as a ‘row_ident’ immediately fixed the problem.

I think I might have even remembered that I had MEDREC as a row_ident because when I read your post I immediately thought of turning that off. But it certainly didn’t occur to me that there might be anything wrong with having it as a ‘row_ident’. MEDREC is a unique identifier set to NOT NULL so in an English syntax sense it is a ‘row identifier’. I haven’t used used the ‘row_ident’ function for years and it looks like I inadvertently used it wrongly. I guess if one column is identified as a PK and another as a row_ident then SERVOY must treat that as a combined PK. So I guess the original error was due to the fact that SERVOY wanted both identifiers before loading the record although I didn’t get that from the error message. But the second error when running it as the ‘load query’ was from ORACLE and I don’t quite understand how that one comes up. It doesn’t matter particularly but I’m just curious as how that happens. What does Servoy do with the query to get that response?

9

John,

When you have a single-key pk and you use a custom query, Servoy will do something like ‘where pk in (select x …)’.
Unfortunately, in case of combined pk you can’t do something like that in sql.
Instead we insert the query result in a temp table and join with that:

insert into temp (pk1, pk2) select x, y …

select tab.pk1, tab.pk2 from tab join temp on temp.pk1 = tab.pk1 and temp.pk2 = tab.pk2.

The temp table is dropped automatically.

The oracle-error you get happens during the insert, when the custom select does not return the same number and type of columns as the temp table columns.
We cannot check on that before because we don’t want to parse sql, thus allowing any valid sql for that database.

Rob

10

Hi

I come back to my question. I have

var query = "SELECT kind, profile_definition_level_number, profile_definition_profile_code, profile_definition_profile_period_fraction_name, profile_definition_profile_period_school_year, profile_definition_subject_code, profile_definition_subject_type_code" + 
			"  FROM exam_definitions" +
			" WHERE profile_definition_profile_code = '" + globals.prfSelectedProfile.split(" ")[0] + "'" +
			"   AND profile_definition_profile_period_fraction_name = '" + globals.prfSelectedProfile.split(" ")[1] + "'" +
			"   AND profile_definition_profile_period_school_year = " + globals.prfSelectedProfile.split(" ")[2] +
			"   AND profile_definition_level_number = " + globals.prfSelectedLevel;

Why does this work

controller.loadRecords(query);

and this fail

var server = databaseManager.getDataSourceServerName(controller.getDataSource());		
var dataset = databaseManager.getDataSetByQuery(server, query, null, 1000);
controller.loadRecords(dataset);

???

With fail I mean that the dataset is wrong. It is filled if it should be empty e.g.

Regards
Birgit

11

what do you mean exactly
in the second example, the dataset you get back from the getDataSetByQuery call is not empty and that is what you do expect
or the dataset is empty and the call to loadRecords() with that empty dataset doesnt result in a empty foundset?

12

Birgit,

Are you sure the foundset.loadrecords(sql) works?
The sql you pass in has to be the pk-query, not the query that returns the values.
Same for the controller.loadRecords(dataset), it must contain the pks.

Rob

13

Johan:
The dataset was filled with random records when it should be empty e.g.

Rob:
Yes, foundset.loadrecords(sql) works. And the 7 attributes are the pk.

Regards
Birgit

14

birgit:
The dataset was filled with random records when it should be empty e.g.

so the getDataSetByQuery call already returns code that you dont expect?
In that dataset that gets returned there you have pk’s that should be there?
But thats out of servoy’s scope. getDataSetByQuery only runs that sql statement
more plain and closer to the database is not really possible,

What happens if you do that query in another tool?

15

Hi Johan

Sorry, I did remember wrong. I did debug again and this is what happens:

The dataset is always filled correctly: It returns the same data I’ll get back when executing the query in InteractiveSQL. But when the dataset is empty and I execute loadRecord(dataset), the displayed records are all records of the table exam_definitions. And this is quite a new bevaviour!

Any idea? Or even better: Is this a bug you already fixed?

Thanks for help and regards
Birgit

16

i cant reproduce this in 5.1

i create a form on the orders table
place some fields
then add a button that has this as an onaction:

function onAction(event) {
	var ds = databaseManager.getDataSetByQuery("example_data","select orderid from orders where 1=2",null,10);
	controller.loadRecords(ds);
}

when i press the button the orders form is empty.

17

Ahhh, do you think I would come up with such an easy error to reproduce?

loadRecords(dataset) is used at 79 places in our code. I only noticed the error (so far) when laoding exam_definitions (of yourse I do not have too much confidence in all other places, now). So, probably the context is essential! Have a look at the topic #13595: The error could be reproduced on forms which were used on tab panels AND where the foundset was cleared onLoad.

My additional question was: Does the error sound familiar to you? May it be possible that you fixed it already? Reading your comments, I assume that your answer is no. Right?

I would be glad if you could give this error description some thought, since reproducing it in a simple solution will be tricky. Or keep it in mind. Maybe you’ll step into it all of a sudden and come up with a fix. Which would make me :D.

Regards
Birgit

18

there are some fixes in that area, thats why i was testing with a plain install

But i cant see immediately that is is related to a empty dataset.
If i look at the code then an empty dataset should just result in an empty foundset.

ofcourse there could be another option and that is that the loadRecords() couldnt be done because of some unsafed records

But then controller.loadRecords() will return false. instead of true.

19

I did track the error further down, since there was one more command in the code after the loadRecords. And I found the sort function guilty (for now):

This works fine (loadRecords with dataset and no sort):

var query = "SELECT fraction_name, graduation_code, graduation_discriminator, level_number, profile_code,profile_period_fraction_name, profile_period_school_year" + 
			" FROM graduation_paths" +
			" WHERE profile_code = '" + globals.prfSelectedProfile.split(" ")[0] + "'" +
			"   AND profile_period_fraction_name = '" + globals.prfSelectedProfile.split(" ")[1] + "'" +
			"   AND profile_period_school_year = " + globals.prfSelectedProfile.split(" ")[2] +
			"   AND graduation_discriminator = '" + getDiscriminator() + "'";	
		
			var server = databaseManager.getDataSourceServerName(controller.getDataSource());
			var dataset = databaseManager.getDataSetByQuery(server, query, null ,1000);
			controller.loadRecords(dataset);			
		//controller.loadRecords(query);
		//controller.sort('level_number asc');

This works also fine (loadRecords with query and sort):

var query = "SELECT fraction_name, graduation_code, graduation_discriminator, level_number, profile_code,profile_period_fraction_name, profile_period_school_year" + 
			" FROM graduation_paths" +
			" WHERE profile_code = '" + globals.prfSelectedProfile.split(" ")[0] + "'" +
			"   AND profile_period_fraction_name = '" + globals.prfSelectedProfile.split(" ")[1] + "'" +
			"   AND profile_period_school_year = " + globals.prfSelectedProfile.split(" ")[2] +
			"   AND graduation_discriminator = '" + getDiscriminator() + "'";	
		
			//var server = databaseManager.getDataSourceServerName(controller.getDataSource());
			//var dataset = databaseManager.getDataSetByQuery(server, query, null ,1000);
			//controller.loadRecords(dataset);
		controller.loadRecords(query);
		controller.sort('level_number asc');

This does not work (loadRecords with dataset and sort):

var query = "SELECT fraction_name, graduation_code, graduation_discriminator, level_number, profile_code,profile_period_fraction_name, profile_period_school_year" + 
			" FROM graduation_paths" +
			" WHERE profile_code = '" + globals.prfSelectedProfile.split(" ")[0] + "'" +
			"   AND profile_period_fraction_name = '" + globals.prfSelectedProfile.split(" ")[1] + "'" +
			"   AND profile_period_school_year = " + globals.prfSelectedProfile.split(" ")[2] +
			"   AND graduation_discriminator = '" + getDiscriminator() + "'";	

			var server = databaseManager.getDataSourceServerName(controller.getDataSource());
			var dataset = databaseManager.getDataSetByQuery(server, query, null ,1000);
			controller.loadRecords(dataset);
		//controller.loadRecords(query);
		controller.sort('level_number asc');

The error still only occurrs when the dataset is empty: Either the previous foundset stays or all records are displayed.

I hope this description helps you to identify the bug!

Regards
Birgit

20

ahh now it makes much more sense.

If you set an empty dataset then we see it as a clear
But if you then call sort on it it will result in the previous sql statement with the sort criteria added to it.

I will check what the changes are between 4 and 5 in this area.
I think we need to add some extra condition (0=1 to the where part)