Connection does not give a value

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Connection does not give a value

Postby nick1461658753 » Wed Nov 16, 2016 11:59 am

Hi,

I have a tableview in which I want to show some products from a shoplist with some extra value.

I have a table shoplist_product, this is the datasource off the form.

shoplist_product is linked to user_product, since our clients have there own code for certain product, we store them here.
The relation also uses a global variable
Image

and user_product is linked to product, in another database, so we can link the clients data to our internal product codes.

Image

I think I did all this as I should. The data is present in the database. But my tableview does not give any data from either user_product or product. Only from it's foundset shoplist_product.

Code: Select all
application.output(foundset.shoplist_product_id);
application.output(foundset.shoplist_product_to_user_product.user_product_id);
application.output(foundset.shoplist_product_to_user_product.user_product_to_product.name);


Code: Select all
64BB8F1F-B6DD-470C-8A94-74F3D3966E73
<null>
TypeError: Cannot read property "name" from  (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#123)
TypeError: Cannot read property "name" from  (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#123)
   at C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js:123 (onRecordSelection)


What am I doing wrong?
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Connection does not give a value

Postby steve1376656734 » Thu Nov 17, 2016 10:45 am

Hi Nick,

I would start by adding:

Code: Select all
application.output("User id: [" + globals.svy_sec_lgn_user_id.toString() + "]");

to the debug in the onRecord selection to ensure that the user ID is what you expect it to be and that a record truly does exist for the relation the way it is defined.

Also the errors relating to being unable to read the property "name" can be ignored for now. As there is no record returned from the first relation (<null>) it cannot possibly then traverse to the second one and so you are trying to output a property called "name" of a null object.

From the code you have pasted it looks like you are using the Servoy frameworks. If this is the case then you must ensure that the records you are trying to join belong to the same organisation (if there is an organisation_id field on the table) otherwise the framework code will exclude them using the table filter that is set up as standard to ensure that tenants see only their own records. You could add a further line to the debug output to see the organisation id being used:

Code: Select all
application.output("Org id: [" + globals.svy_sec_lgn_organization_id.toString() + "]");


Also check if you have any other table filters defined that would prevent the record from being included in the results.

Hope this helps.
Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: Connection does not give a value

Postby nick1461658753 » Thu Nov 17, 2016 11:04 am

thank you for your reply, I shall check this.
We are using servoy framework, but only partially, and multiple databases, this data is stored in another database, not the framework database. Anything special I need to look out for in that case?
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Connection does not give a value

Postby steve1376656734 » Thu Nov 17, 2016 11:38 am

Main one is if you have the organisation_id column in the other databases (or whatever you have specified as the organisation filter field):

2016-11-17_09-11-11.png
2016-11-17_09-11-11.png (52.19 KiB) Viewed 7105 times


The Servoy framework adds a table filter on this column whenever a user logs in (unless the user has the Application Manager or Developer security level).

Our application uses the frameworks and we keep the frameworks data in a svy_framework database and all our application data is in a separate database. We add the organisation_id column to those tables that contain tenant specific data and Servoy takes care of the rest. The only thing to be aware of with this approach is that there was a bug in version 7 of Servoy whereby any data retrieved by using the Query Builder (getDataSetByQuery) was ignoring the table filters and you had to add the filter manually to your queries (SVY-7300). In version 8 this has been changed.

I think your best option for finding the source of your problem would be to log the actual SQL queries being sent to the DB. If you are using PostgreSQL then you can edit the postgresql.conf file and match the following settings:

Code: Select all
log_directory = 'pg_log'                   
log_filename = 'servoy-debug.log' (or whatever you choose)
log_statement = 'all'
logging_collector = on


If you change any of these parameters you will need to reload the config using pg_ctl, unless you have changed the logging_collector and this will then require a full restart of the PostgreSQL server. Once the queries are being logged then you can ensure that the query that is being issued to load the related foundset is actually what you think it is.

Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: Connection does not give a value

Postby nick1461658753 » Mon Nov 21, 2016 12:53 am

But we do not have a filter field set. Doesn't that mean this should not be an issue?
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Connection does not give a value

Postby steve1376656734 » Mon Nov 21, 2016 5:53 pm

If the field is not set then this will not be a problem. In which case the best way to diagnose the issue is to enable the DB query logging and see the exact query that is being issued to retrieve the data and ensure that it matches what you are expecting.
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: Connection does not give a value

Postby nick1461658753 » Tue Nov 22, 2016 3:09 pm

I changed the settings to the ones you provided, but I'm not getting the proper logs in the file. I get some logs when it's starting but it does not give me the queries send to the database.

EDIT: o no my mistake, we are using an extrenal DB, was configuring the wrong conf file.
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Connection does not give a value

Postby nick1461658753 » Wed Nov 23, 2016 12:12 pm

The logs don't even show the query of the error. If I view the page, I just get these logs:
Code: Select all
2016-11-23 11:08:06 CET [2972]: [54-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 LOG:  execute <unnamed>: select shoplist_id from shoplist  where user_id = $1 order by shoplist_id asc limit $2
2016-11-23 11:08:06 CET [2972]: [55-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 DETAIL:  parameters: $1 = '6B384E12-29BB-4035-9838-580D5E72F938', $2 = '201'
2016-11-23 11:08:08 CET [2972]: [56-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 LOG:  execute <unnamed>: select shoplist_product_id from shoplist_product  where shoplist_id = $1 order by shoplist_product_id asc limit $2
2016-11-23 11:08:08 CET [2972]: [57-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 DETAIL:  parameters: $1 = '69E8FD93-4FFF-41A4-9912-CA0DA17F7AD0', $2 = '201'


It doesn't seem to get to the line where the error occurs.
Code: Select all
   application.output("click");
   application.output("id "+ scopes.globals.svy_sec_lgn_user_id);
   application.output("shoplist product id " + foundset.product_id);
   application.output("user code " + foundset.shoplist_product_to_user_product.code);
   application.output("product name " + foundset.shoplist_product_to_user_product.user_product_to_product.name);


Code: Select all
click
id 6B384E12-29BB-4035-9838-580D5E72F938
shoplist product id 07DB4258-C450-42B4-8B23-FFCF08B839F0
TypeError: Cannot read property "code" from  (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#120)
TypeError: Cannot read property "code" from  (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#120)
   at C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js:120 (onRecordSelection)
   at C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js:21 (onShow)
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Connection does not give a value

Postby nick1461658753 » Wed Nov 23, 2016 12:49 pm

I found the solution.

In one of the tables I forgot to check the "UUID" flag for a column. And this prevented it from working.
How about pizza?
nick1461658753
 
Posts: 28
Joined: Tue Apr 26, 2016 10:19 am

Re: Connection does not give a value

Postby patrick » Wed Nov 23, 2016 1:33 pm

How about Pizza? :D
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Connection does not give a value

Postby steve1376656734 » Wed Nov 23, 2016 4:27 pm

Glad you found the solution. I too have forgotten to tick the UUID box on occasion but I'm reasonably sure I got a warning build marker for the relation in the problems tab of Eclipse. Something about both sides of the relation needing to be UUIDs or similar if I remember correctly.

Can't check this at the moment as I am not in the office but will do it when I get back and let you know.
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 5 guests

cron