loadRecords(sql) returns different data to SQL Code??

ROCLASI · August 18, 2010, 11:56am

Hi Ian,

So what if you use the following code:

function onLoad(event) {
   var sqlData = "select dr_id from fabric_condition fc inner join drawings_register dr on fc.fc_id = dr.fc_fabric_key"
   controller.loadRecords(sqlData);
   application.output(databaseManager.getFoundsetCount(foundset)); // debug
}

Kahuna · August 18, 2010, 12:23pm

ROCLASI:
Hi Ian,

So what if you use the following code:
function onLoad(event) {

var sqlData = “select dr_id from fabric_condition fc inner join drawings_register dr on fc.fc_id = dr.fc_fabric_key”
controller.loadRecords(sqlData);
application.output(databaseManager.getFoundsetCount(foundset)); // debug
}

WOW That works Robert - returns the correct no records. Brilliant thanks Bud.

Now I’m confused though (again and not hard to do I’ll admit :oops: ) but why does this work and getDataSetByQuery does not??? I think a full review of how we have used this elsewhere is going to be necessary now.

ROCLASI · August 18, 2010, 12:37pm

Hi Ian,

For debugging purposes can you try the following code and post the result in this thread?

function onLoad(event) {
    var sqlData = "SELECT dr_id, fc_id, fc_fabric_key FROM fabric_condition fc JOIN drawings_register dr ON fc.fc_id = dr.fc_fabric_key"
    var dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    controller.loadRecords(dataset)
    application.output("test 1: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));

    sqlData = "SELECT dr_id FROM fabric_condition fc JOIN drawings_register dr ON fc.fc_id = dr.fc_fabric_key"
    dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    controller.loadRecords(dataset)
    application.output("test 2: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));
    
    sqlData = "SELECT dr_id, fc_id, fc_fabric_key FROM drawings_register dr JOIN fabric_condition fc ON dr.fc_fabric_key = fc.fc_id"
    dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    controller.loadRecords(dataset)
    application.output("test 3: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));

    sqlData = "SELECT dr_id FROM drawings_register dr JOIN fabric_condition fc ON dr.fc_fabric_key = fc.fc_id"
    dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    controller.loadRecords(dataset)
    application.output("test 4: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));
}

Kahuna · August 18, 2010, 12:56pm

ROCLASI:
Hi Ian,
For debugging purposes can you try the following code and post the result in this thread?

Results:
test 1: 14800 → 14800
test 2: 14800 → 14800
test 3: 14800 → 14800
test 4: 14800 → 14800

However here is the last few records output in the form for each load (showing the missing data / extra records):[attachment=0]Load1.png[/attachment]
As you can see Robert every one loads more than maxRowIndex is reporting? All Loads are doing this in your sample code. Is this making sense Robert ?

EDIT: On inspection controller.loadRecords(dataset) is not actually applying the dataset - confirmed by simply commenting the controller out in the code and running the form naked!

The app output is also = test 4: 14800 → 14821 So dataset.getMaxRowIndex() is correct but is what’s actually loaded is the full foundset.

ROCLASI · August 18, 2010, 4:47pm

Hi Ian,

So the dataset doesn’t load.
Can you try the following code to see which of the 4 tests fails? Also clear the Servoy log page before you do this and see if you log some errors.

function onLoad(event) {
    var sqlData = "SELECT dr_id, fc_id, fc_fabric_key FROM fabric_condition fc JOIN drawings_register dr ON fc.fc_id = dr.fc_fabric_key"
    var dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    if ( controller.loadRecords(dataset) ) {
        application.output("test 1: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));
    } else { 
        application.output("test 1: failed to load");
    }

    sqlData = "SELECT dr_id FROM fabric_condition fc JOIN drawings_register dr ON fc.fc_id = dr.fc_fabric_key"
    dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    controller.loadRecords(dataset)
    if ( controller.loadRecords(dataset) ) {
        application.output("test 2: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));
    } else { 
        application.output("test 2: failed to load");
    }
    
    sqlData = "SELECT dr_id, fc_id, fc_fabric_key FROM drawings_register dr JOIN fabric_condition fc ON dr.fc_fabric_key = fc.fc_id"
    dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    controller.loadRecords(dataset)
    if ( controller.loadRecords(dataset) ) {
        application.output("test 3: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));
    } else { 
        application.output("test 3: failed to load");
    }

    sqlData = "SELECT dr_id FROM drawings_register dr JOIN fabric_condition fc ON dr.fc_fabric_key = fc.fc_id"
    dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
    controller.loadRecords(dataset)
    if ( controller.loadRecords(dataset) ) {
        application.output("test 4: " + dataset.getMaxRowIndex() + " -> " + databaseManager.getFoundsetCount(foundset));
    } else { 
        application.output("test 4: failed to load");
    }
}

Kahuna · August 19, 2010, 7:30am

ROCLASI:
Hi Ian,

So the dataset doesn’t load.
Can you try the following code to see which of the 4 tests fails? Also clear the Servoy log page before you do this and see if you log some errors.

Morning Robert - here is the result of you code tests:
test 1: 14800 → 14800
test 2: 14800 → 14800
test 3: 14800 → 14800
test 4: 14800 → 14800

All look like they passed but the form displays 14821 again in each of the test (when run individually). There are no additional warnings or errors shown in the servoy_log.txt file Robert?

ROCLASI · August 19, 2010, 8:31am

Hi Ian,

Do you have any other events triggering on this/these form(s) like onShow, onRecordSelect, etc. that might do a Sort (yes even that can influence the foundset) or touches the foundset in anyway ?

Kahuna · August 19, 2010, 8:39am

ROCLASI:
Hi Ian,

Do you have any other events triggering on this/these form(s) like onShow, onRecordSelect, etc. that might do a Sort (yes even that can influence the foundset) or touches the foundset in anyway ?

No Robert, this is a plain test form with absolutely nothing running on it at all - no events and no other code other than your function in the onLoad!

Being a test form there are no relations to or from it either, and I have tried running this code using a separate foundset and default as the table provider - with the same results.

Kahuna · August 20, 2010, 1:33pm

ROCLASI:
Hi Ian,

Do you have any other events triggering on this/these form(s) like onShow, onRecordSelect, etc. that might do a Sort (yes even that can influence the foundset) or touches the foundset in anyway ?

Any further feedback on this one Robert - or do you think I should raise it as a bug?

ROCLASI · August 20, 2010, 2:27pm

I think you need to file a bug report with that test form.

rgansevles · August 20, 2010, 3:35pm

Ian,

Did you check the result of controller.loadRecords()?

How do you actually get the number of 14821? controller.getMaxRecordIndex()?

Rob

Kahuna · August 20, 2010, 3:47pm

rgansevles:
Ian,

Did you check the result of controller.loadRecords()?

How do you actually get the number of 14821? controller.getMaxRecordIndex()?

Rob

Not sure I follow you Rob, You’ll see all of the checking I’ve done from the code Robert and I have passed back and forth. The controller.loadRecords() loads the form with all of the records and the controller.loadRecords(dataset) does the same. the only way to load the correct data is to use controller.loadRecords(sqlData) (i.e. the sql string in a variable directly.

BTW we used controller.getMaxRecordIndex() and also actually counted the records - but as you’ll note from my screen grabs its obvious when extra records are loaded as they are actually blank.

ROCLASI · August 20, 2010, 9:34pm

Hi Rob,

To summarize the tests we did.

using controller.loadRecords(SQL) will give the correct resultset.
using controller.loadRecords(DataSet) we get the following:

the dataset.getMaxRowIndex() returns 14800.
controller.loadRecords() returns true when loading the DataSet into it
databaseManager.getFoundsetCount(foundset) returns 14800 right after calling controller.loadRecords(DataSet)
still the form shows 14821 records and the records with empty FK fields are shown.
[/*:m][/list:u]
I created 4 tests that had 4 different queries (just a re-shuffle of the syntax to test if Servoy perhaps did some (incorrect) parsing) that should give the exact same result (which it did).

rgansevles · August 23, 2010, 10:15am

ROCLASI:
I think you need to file a bug report with that test form.

Please create a small sample that shows this and file a case in our support system.

Rob

ROCLASI · August 26, 2010, 9:07am

Hi Rob,

Re: Case #314843
I did some hands-on testing now myself and can reproduce the problem.
When I load the dataset in the controller in the onLoad event and also check the record count (dataset and foundset) then it all shows the correct amount.
But if I trigger a getFoundsetCount AFTER the onLoad (by way of a button) I get 10 more records in my count.

Now if I do the same thing but instead I load the controller with (the same) SQL (instead of a dataset) it all works fine.
And if I trigger the loadRecords(dataset) AFTER the onLoad then the foundset count is also fine.

So the quick fix is to use the onShow() event (at firstShow) when you need to use a DataSet but to me this is definitely a bug.

rgansevles · August 27, 2010, 8:29am

Ian and Robert,

The problem is the data access in the form onLoad method.

The controller.loadRecords() call should not be in the onLoad call but in the form onShow (check the firstShow argument).
In the onLoad method, the controller/foundset is not initialized yet, it will be initialised with the default query after the onLoad call (which is only meant for manipulating non-foundset data like elements and globals)
See
https://wiki.servoy.com:8443/display/public/DOCS/Form#Form-onLoad

If you want to suppress the first default query which is done before the onShow method is called, you can call foundset.clear() in the onLoad method.
This is an exception to the rule I just described.

I will update the docs to make this more explicit.

Rob

ROCLASI · August 27, 2010, 8:44am

rgansevles:
In the onLoad method, the controller/foundset is not initialized yet, it will be initialised with the default query after the onLoad call

And when you pass SQL then it uses this as a the default query, correct ?

rgansevles · August 27, 2010, 9:44am

Robert,

This is not the supported way.
It may work now but may break in future versions.

Note that in Servoy 6 we will introduce a special value for the namedFoundSet property of a form (‘empty’) eliminating the need to call foundset.clear() in the onLoad method.

Rob

ROCLASI · August 27, 2010, 9:53am

Okay, thanks for the clarification.

Topic		Replies	Views
Simple 'loadRecords' failing with... Classic Servoy	22	18018	April 13, 2010
Load Records Behaviour` Classic Servoy	12	5124	May 18, 2011
Performance on load records Classic Servoy	5	1989	October 4, 2011
GetDatasetByQuery and ANSI/92 Syntax not Working? Classic Servoy	7	4459	November 21, 2009
another load records by query problem Classic Servoy	5	3405	February 20, 2005

loadRecords(sql) returns different data to SQL Code??

Related topics