Solved

Issue with a DAC referencing a view

  • 19 December 2022
  • 4 replies
  • 194 views
J
Rank
Userlevel 4
Badge

Hi,

I have spent a fair amount of time testing how  a DAC referencing a view in the database works, specifically around a second independent BQL statement on the view not behaving as you might expect.  To demonstrate the issue as clearly as possible I have created a simple, abstract test case running on a local instance.

I have a view:

 

And, the test case attempts to run 2 BQL statements returning one or more of the following records:

 

There is a DAC that references the view:

 

And, here is the Test method that runs the 2 BQL statements:

 

So here is the issue.  When I set a breakpoint at the end of the Test Method and interrogate the contents of q1 and q2, the contents of q2 are wrong.  I was expecting 2 records being InventoryID 3236 and 3249, but we have 2 records both being InventoryID 3235 (ie. the record the first BQL statement returned)

What is going on here?  Some kind of caching around the contents of the View?

I have used SQL Profiler to check what query is being running to populate q2.  This looks correct:

I have also tested what happens if q1 is commented out:

 In this case, the contents of q2 have 1 correct record and one incorrect record:

SQL profiler suggests that the query acumatica executed should have returned the two correct records:

So, is their anyway to reliably run a BQL statement on a DAC referencing a view?  It seems that the first record returned by the view in the first BQL statement is propagated to subsequent records.  The number of records returned is correct (I have done a lot of testing around this behaviour)

Note that my first attempt to achieve my actual objective without views contained a lot of N+1 queries and was very slow to run.  This is why I went down the path of using views.  I have a work around for the issue which is to:

  1. Ensure that each view is only queried once.  The equivalent of what I have done in this abstract example is:

Where

 

  1. Ensure that each query only returns a single record

Is all a bit verbose and messy really. Would like to understand what’s going on here. 

We also have examples of DAC referencing views being used in Generic Inquiries returning not the right data.  Possibly this is a similar issue?

 

Thanks in advance for any help,

John.

icon

Best answer by aaghaei 19 December 2022, 20:29

View original

4 replies

aaghaei
Rank
Userlevel 7
Badge +8

@JWS539

can you 1) define your key for the view DAC and 2) use SelectReadonly to ignore possible cache merging and test it out?

 

if it is view for presentation purpose, why do you use Identity field? Why don’t use Int?

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
J
Rank
Userlevel 4
Badge

Hi @aaghaei,

Thanks for taking the time to review this,

Point#1 Why do you use Identity field?

Good pickup, hadn’t noticed that (code was generated automatically from the database).  I changed from DBIdentity to DBInt.  However, that alone didn’t make a difference:

 

Point #2: Use SelectReadOnly

This did make a difference and has solved the issue.  Refactored code with the ReadOnly added is below:

With this code q2 correctly contains a single record for InventoryID = 3236 (previously it was returning 3235).

Thank you for providing such a simple solution (I would never have thought to try that! Didn’t even know ReadOnly existed until now!)

Also, whilst trying to resolve, I discovered a couple of other options that would work using direct database access.  I saw an article that highly recommended not to use Direct Database access for Updates, but I’m not doing updates here, so maybe this is OK?.  

Discovery #1: Run SQL select statements directly on the database

The below gives the desired result 

 

Discovery #2: Execute a stored procedure

The below code correctly(?) calls a stored procedure, I just can’t quite work out how to retrieve the result,  (ie result array is empty)

 

On SQL server I have results being returned:

 

Will do some more research on this, but in terms of a solution to the original problem, your ReadOnly trick is the best option.

Thanks heaps for taking the time to help,

John

aaghaei
Rank
Userlevel 7
Badge +8

@JWS539 

Glad to hear you got what you need.

please note SPs are not accepted methods of development specially if you plan for ISV or code verification from Acumatica. If you do for yourself then you can do but not recommend.

PXDatabse.Select .insert .Update and .Delete ignores the UI/Application layers and directly works with DB. You might see some of these codes but again it is not suggested.

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Rank
Userlevel 7
Badge +8

@JWS539

On a side note I didn’t see the CompanyID parameter in your SP. please also note if you are going to use SPs, if I a recall correctly, you will need the CompanyID as part of your parameters unlike other suggested methods that platform itself appends the queries with the CompanyID.

The reason to avoiding direct queries most is related to uncontrolled access to Companies and records and of course bypassing the business logic controls and might cause irreversible data issues.

I personally avoid direct queries specially alterations unless I face noticeable performance advantages which has happened only once for me and was only related to reading data not data alterations. 

also as a drawback, if you use DB side coding and modify the records, UI won’t be notified and when you try to save your record you possibly will face the error “another process has changed the data” or something like this.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved