Exception when filtering on customes with attributes

[Florian Haase]

Hi

I don't know what happened here, but since yesterday the API-call:

https://integration.visma.net/API/controller/api/v1/customer?PageNumber=1&PageSize=500&attributes=%7...

results in:

{"message":"An error has occurred.","exceptionMessage":"The 'CompressedContent' type failed to serialize the response body for content type 'application/json; charset=utf-8'.","exceptionType":"System.InvalidOperationException","stackTrace":null,"innerException":{"message":"An error has occurred.","exceptionMessage":"Incorrect syntax near ')'.\r\nInvalid usage of the option NEXT in the FETCH statement.","exceptionType":"System.Data.SqlClient.SqlException","stackTrace":" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader()\r\n at Visma.net.ERP.Api.AR.Repository.CustomerRepository.RunQueryUpfront(ICustomerQueryFilter customerQueryFilter, List`1 customerNotesIds) in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Lib\\Visma.net.ERP.Api\\AR\\Repository\\CustomerRepository.cs:line 1034\r\n at Visma.net.ERP.Api.AR.Repository.CustomerRepository.<GetCustomersPaginantionUpfornt>d__13.MoveNext() in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Lib\\Visma.net.ERP.Api\\AR\\Repository\\CustomerRepository.cs:line 442\r\n at Visma.net.ERP.Web.Api.Repositories.CustomerWebRepository.<GetAllCustomers>d__15.MoveNext() in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Lib\\Visma.net.ERP.Web.Api\\Repositories\\CustomerWebRepository.cs:line 161\r\n at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)\r\n at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)\r\n at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)\r\n at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n at System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)\r\n at PX.Api.Compression.BaseCompressor.<Compress>d__4.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)\r\n at PX.Api.Compression.CompressedContent.<SerializeToStreamAsync>d__4.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)\r\n at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__22.MoveNext()"}}

Our webshop integration is stopped therefore. What happened?

Florian

[Magnus Johnsen]

The solution to the issue has been found and is planned to be released in version 9.08 currently planned to be released on 2022-08-02

[Trygve Storrønningen1]

We are also now receiving a big stacktrace when receiving a 400 Bad request. I asume this is by a glitch. But it should be considered a security glitch as well - as it could contain information you would not want to show to an outsider. 

In our case we sent an order with a orderType not existing in Visma.net - which of course absolutely SHOULD fail - as it does, but we received this error message:

 {"message":"VismaId: 54ce7920-e8cd-43d3-90ff-a4f560976a53. Error creating salesOrder. System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> PX.Data.PXSetPropertyException: The specified inventory ID or alternate ID cannot be found in the system.\r\n  at PX.Data.PXSelectorAttribute.throwNoItem(String[] restricted, Boolean external, Object value)\r\n  at PX.Data.PXSelectorAttribute.SubstituteKeyFieldUpdating(PXCache sender, PXFieldUpdatingEventArgs e)\r\n  at PX.Data.PXDimensionSelectorAttribute.FieldUpdating(PXCache sender, PXFieldUpdatingEventArgs e)\r\n  at PX.Objects.IN.CrossItemAttribute.FieldUpdating(PXCache sender, PXFieldUpdatingEventArgs e) in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Pure\\PX.Objects\\IN\\Descriptor\\Attribute.cs:line 8157\r\n  at PX.Data.PXCache.OnFieldUpdating(String name, Object row, Object& newValue)\r\n  at PX.Data.PXCache`1.SetValueExt(Object data, String fieldName, Object value)\r\n  --- End of inner exception stack trace ---\r\n  at Visma.net.ERP.Api.Base.BaseDomain.HandleChildCommand(IBaseCommand cmd, Boolean handleChildCommands) in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Lib\\Visma.net.ERP.Api\\Base\\BaseDomain.cs:line 72\r\n  at Visma.net.ERP.Api.Base.BaseDomain.ApplyChildCommands(IEnumerable`1 childCommands, Boolean handleChildCommands) in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Lib\\Visma.net.ERP.Api\\Base\\BaseDomain.cs:line 44\r\n  at Visma.net.ERP.Api.SO.CommandModel.SoCommandHandler.Handle_V2(ICreateSalesOrderCommand[] cmdList) in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Lib\\Visma.net.ERP.Api\\SO\\CommandModel\\SoCommandHandler.cs:line 83\r\n  at Visma.net.ERP.Web.Api.Repositories.SOOrderWebRepository.CreateSaleOrderReturnSaleOrderNumber(SalesOrderUpdateDto salesOrderUpdateDto, String orderType, Int32 version) in D:\\Data\\BuildAgent\\work\\11023e52ea15d342\\Web\\Lib\\Visma.net.ERP.Web.Api\\Repositories\\SOOrderWebRepository.cs:line 178\r\nThe specified inventory ID or alternate ID cannot be found in the system."}

[Magnus Johnsen]

Hi,

It looks like your request fails because of an incorrect inventoryID, but even by sending incorrect information, we are not able to reproduce the issue of the stack trace:

[Trygve Storrønningen1]

Hi Magnus, I miss response on my answers here. 

The change in API giving now the big stack trace instead of the correct short message as earlier, is forcing us to temporary roll out a fix for this to all customers so they can get a decent feedback on what went wrong.
This should not be necessary. And a dirty fix for temporary dirty data is not of the good.

[Trygve Storrønningen1]

Yes, but I am not sure the reason of failure is effecting the problem with big stack trace.
It seems also Florian got the big stack trace with different situation/error.

I am neither able to reproduce this with simple calls from Postman, but we do get this in the error response stream when calling from code.
I think it might be 

[Trygve Storrønningen1]

I am now reproducing the big stack trace in Postman by posting an invalid inventoryId

POST https://integration.visma.net/API/controller/api/v2/salesorder

[Magnus Johnsen]

I am still not able to reproduce this via postman, neither with InventoryId nor InventoryNumber set to an incorrect value.

Could you please send an example JSON?

Thank you. 

[Magnus Johnsen]

Hi,

Are you still getting the same issue using Postman? Or only via code? We have a case looking at this for the customer endpoint, have you seen it any where else?

[Trygve Storrønningen1]

Still same both in Postman and code.

Salesorder is our biggest headache now 😉
Sorry if messing up this thread 🙂

[Trygve Storrønningen1]

When posting with a valid inventoryId but invalid orderType (eg. ROK) - I get a short reponse message as expected

[Florian Haase]

I tried also to send the http request without Escape characters - but it results in the same response. https://integration.visma.net/API/controller/api/v1/customer?PageNumber=1&PageSize=500&attributes={"GAKUNDNR":"41031"}

[Magnus Johnsen]

Hi,

We have not been able to reproduce this, is it still an issue?

[roy_muller · Accepted solution]

Yes, I reproduced it by having no attributes defined. The problem is not related to the attributes filter itself (encoding).

When list of attributes (note id) is empty, the query generated contains an empty IN clause which the DB server does not like and throws the error mentioned at the top of this thread.

The problem is fixed and tested in our test environment and is expected to be part of the next release.