You mention it is a view, is the primary key unique on the view?

Thanks Erwin. It turned out there were indeed non-unique rows of data in these views. However in one view this still didn't fix the issue even though it loads rather quick via direct database connection in the windows gui. I worked around the issue by replacing the view with a table and a procedure to fill it. The data doesn't change much making it a good workaround for us.

What helped a lot is that the 2023.1 version of the platform allows to copy references (lookups and details) when copying a table. I didn't have to change much to get the same screen back. I'm very happy with that addition to the software factory. Perhaps for a next version the developers can also look into copying context, layout and default procedures?

The assignment is already copied with the copy task, if you mean actually creating a seperate control procedure, feel free to create an idea for this. 

 

I've looked into the issue again. I checked with a query to be 100% sure the primary key of the view is unique. Indicium still replies with an invalid answer. In fact I have to kill the session of the query on sql server because it keeps eating up CPU time. I don't understand why because the same view in the Windows GUI via direct SQL server connection runs fine and with good performance.

This sounds like a performance issue. Try the following: instead of the select inside the view. Set a hardcoded row of data in the view. something like this:

select 1, 'test’, 3 

If the view opens without problems with the hardcoded data you might have to tune the query. 

Also the windows GUI and indicium have different ways to retrieve data therefor the execution plans probably differ and also the performance can differ. So even though they technically do the same from a sequel standpoint they do something completely different. 

I did exactly as you said and hard-coded a single row of data into the view. This one row pops up in the Windows GUI via SQL. Via Indicium it produces another error:

“Indicium has encountered a unknown error, see the log from indicium for more information”

I looked in the Indicium basic folder for a log file but I don't see it. Where can I find the file? Hopefully that'll clear up things a bit.

There should be a logs folder in the indicium folder:

 

Found it. The logs shows several sections. This is the first part:

2023-03-22T11:49:38.8698359+01:00 0HMPABBK3PJOT:00000003 [err] Microsoft.OData.ODataException: An ODataPrimitiveValue was instantiated with a value of type 'System.DateTime'. ODataPrimitiveValue can only wrap values which can be represented as primitive EDM types.
   bij Microsoft.OData.ODataPrimitiveValue..ctor(Object value)
   bij Microsoft.OData.ODataValueUtils.ToODataValue(Object objectToConvert)
   bij Indicium.OData.Serialization.CustomODataResource.AddProperty(String name, Object value) in C:\azp\agent\_work\1\s\src\Indicium\OData\Serialization\CustomODataResource.cs:regel 71
   bij Indicium.Controllers.TableController.<Get>d__4.MoveNext() in C:\azp\agent\_work\1\s\src\Indicium\Controllers\TableController.cs:regel 202

I've attached the full log file. Above section is at line 1115.

Unfortunately this view still hasn't been fixed in Indicium basic so we are sticking to the table equivalent of the view for the time being.

We have issues in other views also. For example one view produces the following error in Indicum basic log:

---> Microsoft.Data.SqlClient.SqlException: Derived table 't1' is not updatable because a column of the derived table is derived or constant.

This normally only happens when the view doesn’t have an instead of update trigger. However it does and this error only occurs in the Windows GUI via Indicium basic. It works fine in Windows GUI via SQL and in the Universal GUI.

What is the best route to further debug these issues?

Hello @Roland,

Which version of Indicium Basic are you using?

Hi Vincent, 

Roland is not available at the moment, but we use the last version (2023.1). We always try to use the current (last) version. Maybe we miss a couple of hotfixes, but not much more.
Hopefully you can sort our problems out, as we get a lot of messages from the business about this. Thank you!

Hello Hugo,

Regarding this:

---> Microsoft.Data.SqlClient.SqlException: Derived table 't1' is not updatable because a column of the derived table is derived or constant.

This normally only happens when the view doesn’t have an instead of update trigger. However it does and this error only occurs in the Windows GUI via Indicium basic. It works fine in Windows GUI via SQL and in the Universal GUI.

This is surprising because there shouldn't be much of a difference between Indicium Universal and Indicium Basic when it comes to update statements. My first thought would be that the test scenarios are different (e.g. different database or different view). Could you have a look at the SQL statement produced by Indicium Basic through the SQL Server Profiler or a similar tool? I think this is the only way to troubleshoot this issue.

And regarding the other error message, it looks like the view might be returning a varchar value for a column that is marked as datetime in the model. Or something similar to that.

Hello Hugo and Roland,

what Vincent mentioned about the data type not corresponding to the domain used in the model, is likely what is causing the issue. From my own experience, Indicium is a bit more strict about these things than the Windows GUI is with a direct connection.

Hopefully this post might give you some guidance in finding the cause.

• Check that the domains in the view are the same as you use on the tables you get your data from. Or at least, make sure the data type is the same, as the domain can technically be a different one.
• Something silly, but see if the definition in the template (functionality) corresponds with the order of your SELECT statement. Sometimes you might not have noticed that the order of the columns are not what you expected, if accidentally the “Sequence no.” is duplicated from manual input or dynamic model.
• CAST/CONVERT the data in the SELECT statement to the correct one of the column. Especially when you CONCAT or do some calculation work.

You can always try bullet point 3 for all columns to see if that stops the error and undo it till you get the error again of course.

A pointer to see if the view “understood” the data types as you thought it would be, check the object explorer and see what the columns of the view show you. On occasion you see that an expected DATETIME is suddenly showing VARCHAR:

(example from SSMS):
 

Good luck!