INSERT reference in not-persisted BO does not work

If you think that something doesn't work in Aware IM post your message here
Post Reply
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

INSERT reference in not-persisted BO does not work

Post by nhofkes »

I found that an attempt to INSERT an object in the multiple reference attribute of a non-persisted object results in an error (nullPointer exception). In fact, the not-persisted object might not be capable of holding multiple references.

In part inspired by this post from ACDC, I wanted to create a Query object with a peer-multiple reference to other objects. I would then add the relevant objects to the Query BO and subsequently use that as a dynamically created list for further processing. Because the Query is only required within the context of a process, I thought that I could use a not-persisted object for this purpose.

By way of example:

I created a (not-persisted) BO named NP_Query which has a peer-multiple attribute named Accounts of type Account, and ItemsCounter attribute.

The simplified process:

Code: Select all

CREATE NP_Query
FIND ALL Account
INSERT Account IN Query.Accounts
NP_Query.ItemsCounter = COUNT Account WHERE (Account IN NP_Query.Accounts)
DISPLAY MESSAGE 'Number of Accounts: <<NP_Query.ItemsCounter>>'
The INSERT action results in a java.lang.NullPointerException (as visible in the Server output log).
If I use exactly the same code as above but substitute 'NP_Query' by a regular persisted Query object, it works fine.
Note that the Account object in this example is a regular persisted object, so that's not the issue.
Also, the not-persisted object can actually hold a reference, because

Code: Select all

NP_Query.Accounts = Account
does not create an error. But if I then try to count the number of items, I get the following error message:
com.bas.basserver.persistence.PersistenceException: Error executing a query SELECT COUNT(*) FROM BASTESTDOMAINAPP_ACCOUNT AS Account LEFT JOIN BASTESTDOMAINAPP_NP_QUERY_REF AS NP_Query_Accounts_RO ON NP_Query_Accounts_RO.RID=Account.ID AND NP_Query_Accounts_RO.ID=18 AND NP_Query_Accounts_RO.FIELD_NAME='Accounts' WHERE ((NP_Query_Accounts_RO.ID=18)) message is (conn=11929) Table 'basdbtest.bastestdomainapp_np_query_ref' doesn't exist
It seems that to deal with the multiple reference attribute, Aware requires a separate reference table which is either not created for not-persisted objects or it is created in memory whereas Aware is looking at the normal database and can't find it. In either case, I can't use multiple references in a not-persisted BO. Which is unfortunate, as this means that I need to resort to a regular persisted object which puts additional load on the database server and requires to manually remove the Query object after use.

If I am overlooking something or if there is a work-around, I'd be happy to hear.
Niels
(V9.0 build 3241 - MariaDB - Windows)
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

Re: INSERT reference in not-persisted BO does not work

Post by nhofkes »

Perhaps I should add, as background to the idea of creating of the Query object, that I needed a complex filter for a particular query which was not possible to do in the normal query parameters. I.e. the list of Accounts to be included in the query should be created based on multiple criteria which couldn't be combined in a regular FIND clause. Thus, the idea was to create the separate Query object where I can compile the list of relevant Accounts by a process and then limit the FIND clause in the query for display to "FIND Account WHERE Account IN ThisQuery.Accounts"
Niels
(V9.0 build 3241 - MariaDB - Windows)
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: INSERT reference in not-persisted BO does not work

Post by tford »

Niels,
that I needed a complex filter for a particular query which was not possible to do in the normal query parameters. I.e. the list of Accounts to be included in the query should be created based on multiple criteria which couldn't be combined in a regular FIND clause.
I can't answer your non-persisted reference question, but I'd be curious to learn more about the specifics of what you are trying to accomplish if you'd like to share.
Tom - V8.8 build 3137 - MySql / PostGres
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

Re: INSERT reference in not-persisted BO does not work

Post by nhofkes »

Tom,
I don't know how much specifics you'd want to know, but to summarize, I want to enable the user to select one or more persons that are related to a company (for the purpose of creating an account for them) but to limit the list to only persons that are relevant. For example, people that already have an account should not be included. Now the issue is that to define 'relevant' in this case is not easy to do in a FIND clause - it depends on multiple criteria which are (in part) not directly related to the object (person) but only indirect, because there are various multiple references.

At first I tried to compose a "FIND Person WHERE x or (y and z)" clause, but came to the conclusion that behind the scenes this is translated to a SQL SELECT clause with various JOIN statements. If conditions x, y and z are not really comparable, because they relate to vary different objects, you can't join the results and therefore the FIND clause was rejected by AwareIM. Therefore, I resorted to writing the conditions in a process - actually two processes because they are layered - and creating the required list of persons in the QueryParameters object. After that, the FIND clause can simply refer to all the persons in the QueryParameters object.

I have got this mechanism working now, except that it is in a normal persisted object instead of a not-persisted object. At the start of the relevant process, I delete the QueryParameters object if it already exists and create a new QueryParameters which is then filled with the relevant parameters.

I think that even in cases where it would theoretically be possible to combine many different criteria in a FIND clause, I may prefer to use the approach with the QueryParameters object, because the logic is much easier to write and understand than a long WHERE clause with multiple conditions linked by OR and AND. Undoubtedly it creates a little overhead, and would thus be a little slower than an expression which can be translated to a SQL clause, but for the purposes where I am using this this is not noticeable.

And of course there is also the alternative of creating a Stored Procedure, but I don't need that additional complexity at this moment. I may do so in the future for larger datasets or more complicated queries.
Niels
(V9.0 build 3241 - MariaDB - Windows)
rbross
Posts: 441
Joined: Wed Nov 19, 2014 4:13 am
Location: Coventry, Connecticut USA

Re: INSERT reference in not-persisted BO does not work

Post by rbross »

I think I have an example of an easier way to do this. I will look for it and post it for you.
Roger Ross
AwareIM 8.7 (build 3025) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.5 (build 2828) ~ MS-SQL ~ Windows 10 ~
rbross
Posts: 441
Joined: Wed Nov 19, 2014 4:13 am
Location: Coventry, Connecticut USA

Re: INSERT reference in not-persisted BO does not work

Post by rbross »

This might be what you are looking for?
This will only display records in the picklist that have not been previously selected.
One difference is I am not using a NP BO. I don't think you need one just INSERT the reference into the BO you want.

This displays a list of contacts to add as a reference to the Relationship BO.
This is the Query, Contact_Pick_For_Relationship:
FIND Contact WHERE COUNT Relationship WHERE (Contact IN Relationship.PM_Contact AND Relationship.PM_Contact=ThisRelationship.PM_Contact)=0 ORDER BY Contact.ContactDisplayName

This is the process, PICK_Contacts_For_Relationship
PICK ONE OR MORE FROM Contact_Pick_For_Relationship
INSERT Contact IN Relationship.PM_Contact
Roger Ross
AwareIM 8.7 (build 3025) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.5 (build 2828) ~ MS-SQL ~ Windows 10 ~
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

Re: INSERT reference in not-persisted BO does not work

Post by nhofkes »

Roger
Many thanks for the suggestion, but I tried your approach first and it didn’t work. The reason for that is that, in my specific case, the criteria for the query are more complex than just ‘pick a contact who is not already included in the list’. In fact so complex that the criteria cannot be expressed in a FIND statement. Hence the alternative approach where the list for the query is created using a process.
Niels
(V9.0 build 3241 - MariaDB - Windows)
rbross
Posts: 441
Joined: Wed Nov 19, 2014 4:13 am
Location: Coventry, Connecticut USA

Re: INSERT reference in not-persisted BO does not work

Post by rbross »

I’d write a sproc, You could do this in a select statement with left outer joins to get what you want and insert it into a work table that AwareIM can see then use that as your query.
Just a thought.
SQL is very fast!
Roger Ross
AwareIM 8.7 (build 3025) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.5 (build 2828) ~ MS-SQL ~ Windows 10 ~
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

Re: INSERT reference in not-persisted BO does not work

Post by nhofkes »

I’d write a sproc
I assume that 'sproc' means a stored procedure? Yes, that's also possible. I mentioned that in my earlier post. There are clearly workarounds, but the issue here is that non-persisted objects apparently cannot hold multiple-reference attributes and that's a bug in my view - or at the very least a substantial limitation. Non-persisted objects should behave the same as persistent objects in terms of attributes and references, except that they have a limited lifespan. Of course I understand that they cannot have a matching attribute in the referenced object. I'd simply like to be able to create a one-sided, peer-multiple reference.
Niels
(V9.0 build 3241 - MariaDB - Windows)
rbross
Posts: 441
Joined: Wed Nov 19, 2014 4:13 am
Location: Coventry, Connecticut USA

Re: INSERT reference in not-persisted BO does not work

Post by rbross »

Unfortunately np BO's cannot hold multiple values in a referenced object.
it would be nice if they could.
You could try ticking the check box for "Store Session Values" under the persistence property on the np BO.
This will save all values for the session then discard them when the user logs out. Maybe this will act differently than a plan np BO when this value is selected?
As an alternate solution you could make the BO persistent and add an attribute for the LIRUID( LoggedInRegularUser.ID)
Then add the LIRUID as part of your query.
Where bo.LIRUID=LoggedInRegularUser
Roger Ross
AwareIM 8.7 (build 3025) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.5 (build 2828) ~ MS-SQL ~ Windows 10 ~
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

Re: INSERT reference in not-persisted BO does not work

Post by nhofkes »

rbross wrote: Thu Nov 05, 2020 11:28 pm Unfortunately np BO's cannot hold multiple values in a referenced object.
it would be nice if they could.
You could try ticking the check box for "Store Session Values" under the persistence property on the np BO.
This will save all values for the session then discard them when the user logs out. Maybe this will act differently than a plan np BO when this value is selected?
I tried that, but found no difference (I received the same error message).
Niels
(V9.0 build 3241 - MariaDB - Windows)
rbross
Posts: 441
Joined: Wed Nov 19, 2014 4:13 am
Location: Coventry, Connecticut USA

Re: INSERT reference in not-persisted BO does not work

Post by rbross »

I tried this using the new np SessionID check box and thats no good either.
All you can do in the new session table is store values no references.

Looks like you will have to use a persistent BO with LIRUID and add your reference to that BO.
Roger Ross
AwareIM 8.7 (build 3025) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.5 (build 2828) ~ MS-SQL ~ Windows 10 ~
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

Re: INSERT reference in not-persisted BO does not work

Post by nhofkes »

rbross wrote: Thu Nov 05, 2020 11:28 pm As an alternate solution you could make the BO persistent and add an attribute for the LIRUID( LoggedInRegularUser.ID)
Then add the LIRUID as part of your query.
WHERE bo.LIRUID=LoggedInRegularUser
Should this be:
Where bo.LIRUID=LoggedInRegularUser.ID
or is that the same thing?
Niels
(V9.0 build 3241 - MariaDB - Windows)
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: INSERT reference in not-persisted BO does not work

Post by tford »

bo.LIRUID=LoggedInRegularUser.ID
This will work.
Tom - V8.8 build 3137 - MySql / PostGres
Post Reply