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

Kahuna · August 17, 2010, 2:07pm

Hi Folks - once again I have thought myself to a standstill with this !!! Hope someone can throw some clarity on it? It should be really straight forward but I’ve obviously misconstrued something. :oops:

I have a form based on ‘drawings_register’, I use a loda records with getDataSetByQuery() to populate it based on the following code:

var sqlData = "select distinct dr_id "
	+"from fabric_condition fc, drawings_register dr "
	+"where fc.fc_id = dr.fc_fabric_key AND dr.fc_fabric_key IS NOT NULL"
	var dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
	controller.loadRecords(dataset);

Using that sql in a Toad returns only the dr_drawno’s that have a coinciding fc_id = fc_fabric_key

However the data returned in getDataSetByQuery() is ALL of the dr_drawno’s regardless of if the fc_fabric_key is null or not?

What am I missing here to get the correct dataset into my form?

Very much appreciate feedback on this.

mboegem · August 17, 2010, 2:36pm

which table is the dr_id based on? I see no correlation name (not really needed if it only exist in one of 2 tables, but still… could be a future show stopper)

further on I’d say that the ‘dr.fc_fabric_key IS NOT NULL’ doesn’t really do much, wouldn’t it be a better constrain on the primary table? (fc.fc_id is not null)

hope this helps

Kahuna · August 17, 2010, 3:00pm

mboegem:
which table is the dr_id based on? I see no correlation name (not really needed if it only exist in one of 2 tables, but still… could be a future show stopper)

further on I’d say that the ‘dr.fc_fabric_key IS NOT NULL’ doesn’t really do much, wouldn’t it be a better constrain on the primary table? (fc.fc_id is not null)

hope this helps

Thanks Marc - dr_id is only in one table.

Tried your suggestion for selecting fc_id as null - but same results.

It almost appears that the getDataSetByQuery is not performing the last part of the sql string??? Sounds crazy but appears to be the case even though the stats show the query running?

Total Time (mm:ss:ms)	Count	Avg Time (mm:ss:ms)	Type	Action
00:00:016	1	00:00:016	Custom	select dr_id from fabric_condition fc, drawings_register dr where fc.fc_id = dr.fc_fabric_key AND fc.fc_id IS NOT NULL

00:00:000	1	00:00:000	Load foundset	select top 11 count(1) as n from drawings_register

00:00:000	3	00:00:000	Load foundset	select top 201 dr_id from drawings_register order by dr_id asc

00:00:000	2	00:00:000	Load foundset	select top 31 dr_id, fc_fabric_key, cd_id, cc_id, ci_id, dr_drawno from drawings_register where dr_id in (?, ?, ?, ?, ?, 
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

00:00:000	2	00:00:000	Relation	select top 61 fc_id, cd_id, cc_id, fc_flag_for_inspection, fc_main1, fc_main2, fc_access, fc_action, fc_application_method, br_rule, cm_complexity, fc_cost_code, fc_ex_extent, fc_extent_num, fc_program_year_basic, fc_program_year, fi_insulation_fabric_system1, fi_insulation_fabric_system2, fi_insulation_fabric_system3, fp_paint_fabric_system1, fp_paint_fabric_system2, fp_paint_fabric_system3, bs_insulation_removal, fc_insulation_thickness, fc_insulation_type, fc_rec_maint_yr, fc_access_group, fc_name_of_surveyor, fc_scfm, fc_scfm_dec, fc_safety_factor, fc_surface_preparation_required, fc_temp_units, fc_year_by_hand, fc_year_by_hand_yn, cladding_type, fc_year_calculated, fc_date, fc_importdate, fc_date_last_maint, fc_per_complete, fc_update_y_n, fc_standard_prep_y_n, fc_date_last_yearend, fc_env_conditions, fc_paint_temp_coating, fc_base_year1, fc_last_base_reset_user, fc_last_base_reset_date, fc_last_base_reset_reason, fc_patch_repair, fc_not_prog_complete, fc_area, fc_description, fc_ccr_value, fc_diameter, cd_service_code, fc_measued_area, rc_orientation, fc_record_no_user_input, fc_linearyn, fc_elevation, fc_notes, ad_analysis_code, fc_sub_mat_type_link, cd_complex, cd_plant, fc_user_tag_1, fc_user_tag_2, fc_user_tag_3, fc_area_link, fc_haz_env, fc_corro_zone, fc_calced_area, fc_calced_area_ext, fc_downgrade_grit_scfm, fc_downgrade_grit_ext, fc_downgrade_hptc_scfm, fc_downgrade_hptc_ext, yearendselect, fc_marked_deleted, fc_marked_deleted_by, fc_marked_deleted_date, fc_scfm_changed_by_hand, fc_scfm_changed_by_hand_date, fc_spare1, fc_spare2, fc_spare3, fc_spare4, fc_year_last_reset, fc_year_by_hand_changed_by, fc_year_by_hand_changed_date, fc_hist_coating_type, fc_hist_thickness, fc_hist_expected_life, fc_hist_compatibility, fc_hist_blasted, fc_hist_date_applied, fc_qra_int, fc_qra_ext, fc_job_pack_id, fc_job_pack_description from fabric_condition where fc_id = ? order by fc_id asc

rgansevles · August 17, 2010, 3:12pm

Ian,

Getdatasetbyquery simply runs the sql as-is, without any changes.
I think the logging you show is from a different bit of code, the distinct keyword seems to have gone.

Set a breakpoint and have a look at the actual contents of the dataset, I can’t imagine Toad giving a different result from Servoy.

Also check the boolean result of controller.loadRecords.

Rob

Kahuna · August 17, 2010, 3:35pm

rgansevles:
Ian,

Getdatasetbyquery simply runs the sql as-is, without any changes.
I think the logging you show is from a different bit of code, the distinct keyword seems to have gone.

Set a breakpoint and have a look at the actual contents of the dataset, I can’t imagine Toad giving a different result from Servoy.

Also check the boolean result of controller.loadRecords.

Rob

Sorry Rob - the ‘distinct’ removal was me messing with the code between runs.

Output of the loadRecords in app.output is = true

The value of the dataset in variables view shows all of the rows regardless of the value of fc_id or fc_fabric_key???

I can’t imagine Toad giving a different result from Servoy.

I’m sure you are right Rob - but having seemingly tried every variation on this form it seems I’ve hit the wall!

Where else should I be looking?

rgansevles · August 17, 2010, 4:06pm

Ian,

So you have fabric_condition records in your db that do not have a matching drawings_register record but whose dr_id still ends up in the dataset?

Maybe dr_id is not unique and comes from another record then you expect?
You could try selecting more columns, like fc_id.

Rob

Kahuna · August 17, 2010, 4:21pm

rgansevles:
Ian,

So you have fabric_condition records in your db that do not have a matching drawings_register record but whose dr_id still ends up in the dataset?

Maybe dr_id is not unique and comes from another record then you expect?
You could try selecting more columns, like fc_id.

Rob

Rob

dr_id is the pk in the drawings_register table.
The fabric_condition table has zero, one or more related fc_fabric_key rows in drawings_register (fabric_condition.fc_id = drawings_register.fc_fabric_key).

Using this sql I should be able to get a list of dr_drawno from drawings_register only where there is a corresponding fc_fabric_key to the related fc_id.

This same sql string works in Toad and returns the correct data - just not in this particular getDatasetByQuery?

I have tried adding more columns and similar result - it simply seems that the where is not null is not considered???

EDIT: Toad Output = 14800 records (correct) with sql:

select dr_id, fc_id, fc_fabric_key
	from fabric_condition fc, drawings_register dr 
	where (fc.fc_id = dr.fc_fabric_key) AND fc.fc_id IS NOT NULL AND dr.fc_fabric_key IS NOT NULL

Servoy Output = 14821 (incorrect) with sql:

var sqlData = "select dr_id, fc_id, fc_fabric_key "
	+"from fabric_condition fc, drawings_register dr "
	+"where (fc.fc_id = dr.fc_fabric_key) AND fc.fc_id IS NOT NULL AND dr.fc_fabric_key IS NOT NULL"

There are 21 records with no fc_fabric_key in the table.

How can this be Rob???

mboegem · August 17, 2010, 9:43pm

Kahuna:
There are 21 records with no fc_fabric_key in the table.

What is the value? null or empty?

Kahuna · August 17, 2010, 10:44pm

mboegem:

Kahuna:
There are 21 records with no fc_fabric_key in the table.

What is the value? null or empty?

Its null Marc - but I have checked it for empty too, in one or more of the three thousand ways I’ve tried this! :lol:

mboegem · August 17, 2010, 11:09pm

hmmm, I’m also running out of options…

So my last attempts ;-)

did you try switching the conditions in the where clause > fc.fc_id = dr.fc_fabric_key as last condition (should be better anyway according to query rules)
did you try writing it with ‘INNER JOIN’ syntax

ptalbot · August 18, 2010, 2:24am

Kahuna:
EDIT: Toad Output = 14800 records (correct) with sql:
select dr_id, fc_id, fc_fabric_key

from fabric_condition fc, drawings_register dr 
where (fc.fc_id = dr.fc_fabric_key) AND fc.fc_id IS NOT NULL AND dr.fc_fabric_key IS NOT NULL

Servoy Output = 14821 (incorrect) with sql:
var sqlData = "select dr_id, fc_id, fc_fabric_key "
+"from fabric_condition fc, drawings_register dr "
+“where (fc.fc_id = dr.fc_fabric_key) AND fc.fc_id IS NOT NULL AND dr.fc_fabric_key IS NOT NULL”
There are 21 records with no fc_fabric_key in the table.

First I believe that you should rather use inner joins in this case, if you only want the one which have no null related records, so:

select dr_id, fc_id, fc_fabric_key
from fabric_condition fc 
inner join drawings_register dr on fc.fc_id = dr.fc_fabric_key;

shoud do the trick (no need for IS NOT NULL on the foreign key).

But this is not the issue here if this works one way in Toad and differently in Servoy, and I agree with Rob that this shouldn’t be.
So where do you get your numbers from?

In the case of the dataset is that what the dataset.getMaxRowIndex() returns, or do you then use this dataset to load a foundset and is that the foundset count with databaseManager.getFoundsetCount(foundset)?
Because that’s not quite the same thing.

We ran into something similar lately where we were doing a search and then loaded a foundset based on the pks found.
Except that the foundset was based on a view which was already filtered in the database, so databaseManager.getFoundsetCount(foundset) was returning more than what was (what could be) displayed. Couldn’t it be something like that you are experiencing?

Kahuna · August 18, 2010, 8:31am

Thanks for the feedback Patrick:

(no need for IS NOT NULL on the foreign key).

Yes I recognise that but when the obvious is not working we try everything

ut this is not the issue here if this works one way in Toad and differently in Servoy, and I agree with Rob that this shouldn’t be.
So where do you get your numbers from?

Again I agree but its not returning the correct data. I’m just using a test form now in record view with a single column (dr_drawno) and with maxRecordIndex in a tag on a footer.

There is a single function in the onload event on this test form (based on drawings_register as with the previous criteria:

function onLoad(event) {
	
	var sqlData = "select dr_id, fc_id, fc_fabric_key from fabric_condition fc inner join drawings_register dr on fc.fc_id = dr.fc_fabric_key"
	var dataset = databaseManager.getDataSetByQuery("tci_test",sqlData,null,-1)
	controller.loadRecords(dataset)
}

This produces 14821 records where 21 of them have no fc_fabric_key - and therefore are in correct - as you can see I’m using your SELECT Patrick.

The same Select in Toad (SQL Studio too) results in 14800 rows which is correct?

Baffling - I will test it with a different pair of tables and hoping I’m doing something wrong here - but this is pretty worrying from a big picture view point?

ROCLASI · August 18, 2010, 9:10am

Hi Ian,

Can you run the following query in Servoy and in Toad and see what result it gives?

select count(dr_id) from fabric_condition fc inner join drawings_register dr on fc.fc_id = dr.fc_fabric_key

Kahuna · August 18, 2010, 9:49am

ROCLASI:
Hi Ian,

Can you run the following query in Servoy and in Toad and see what result it gives?
select count(dr_id) from fabric_condition fc inner join drawings_register dr on fc.fc_id = dr.fc_fabric_key

Robert - that returns the correct count in both Toad and Application.output()

ROCLASI · August 18, 2010, 10:00am

Hi Ian,

Both show 14821 records ? If so then Toad wasn’t loading all records (or rounding it down or something).

As for the fc_fabric_key still being null. Your form is in the context of the fabric_condition table, correct?
But your query uses dr_id (the pk of drawings_register) as the first column. I think you need to place fc_id as first column.

Also why load the dataset in the controller and not just the SQL? Is it only because you want to WHOLE foundset to be loaded into the client instead of in batches of 200 ?

Kahuna · August 18, 2010, 10:17am

Both show 14821 records ? If so then Toad wasn’t loading all records (or rounding it down or something).

No Robert both are correct with 14800 records - its only in the loadRecords that we get the additional incorrect 21 records.

The form is based on the drawings_register hence the format of the SQL. Is that wrong? We’ve tried numerous combination here - ewven using just a relation (which exists already) but we either get wrong data or no data.

This sql load is simply to eliminate outside influences. So a straight sql dataset should have fewest potential hick-ups. But its still loading incorrect data (or at least not applying the limiting filter).

I’ve now tried this with a different sub table using this:```
var sqlData = “select img_id, image_fabric from fabric_condition inner join tblImages on fc_id = image_fabric”


Coul I somehow be screwing this up with the wrong table Robert???

jcompagner · August 18, 2010, 10:26am

what is the actual query servoy send to the server? Is that really that sql?
You can see that in the admin pages performance tab.

(i didnt read the whole thread so maybe this question is not valid)

ROCLASI · August 18, 2010, 10:36am

Hi Ian,

Can you you check how many records the dataset holds (before loading it in the controller).

application.output(dataset.getMaxRowIndex());

Kahuna · August 18, 2010, 10:38am

jcompagner:
what is the actual query servoy send to the server? Is that really that sql?
You can see that in the admin pages performance tab.

(i didnt read the whole thread so maybe this question is not valid)

Any help welcomed Johan - its hair pulling time here as we have discovered this may be affecting a number of our form loads.

Here is the output from the stats page on that load (the original one I discussed)

Total Time (mm:ss:ms)	Count	Avg Time (mm:ss:ms)	Type	Action
00:00:016	2	00:00:008	Load foundset	select top 31 dr_id, fc_fabric_key, cd_id, cc_id, ci_id, dr_drawno from drawings_register where dr_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
00:00:015	1	00:00:015	Custom	select dr_id, fc_fabric_key from fabric_condition fc inner join drawings_register dr on fc.fc_id = dr.fc_fabric_key
00:00:000	1	00:00:000	Load foundset	select top 201 dr_id from drawings_register order by dr_id asc
[ top ]

FYI Its returning 14821 records when it should be returning 14800.

Kahuna · August 18, 2010, 11:46am

ROCLASI:
Hi Ian,

Can you you check how many records the dataset holds (before loading it in the controller).
application.output(dataset.getMaxRowIndex());

14800 Robert - that’s is correct! Seems the loading in the controller is somehow responsible for the difference???

Topic		Replies	Views
getDataSetByQuery Classic Servoy	1	2243	August 23, 2004
controller.loadrecords(dataset) problem Classic Servoy	7	2933	July 24, 2007
getDataSetByQuery problem Classic Servoy	9	5542	October 18, 2004
Stuck in a Loop with .loadRecords? Classic Servoy	6	2379	October 19, 2009
Help with loading records following SQL select Classic Servoy	2	2488	February 9, 2004

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

Related topics