We have an on-premises environment, and we are running SQL Server 2014 as our external database. We have a huge number of tables and stored procedures, so the 400 entity blocks aren't going to get us far with data access. We have been able to successfully run some stored procedures, but sometimes, there is no explaining why we get errors in Advanced Query tests. In one test module, I can test one stored procedure with an integer input parameter and it runs and returns results with no problem. For another, I try Test, and it tells me "Input string was not in a correct format." and I get no results, but the executed SQL shows, and it is perfect, and, since I am running a trace on SQL, I see that Outsystems did run the query with the connection as configured, and returned results, with no errors, as far as SQL Server is concerned.

This stored procedure has a VARCHAR (string) input parameter. I never know what to put in the parameter property value in the properties box on the right side. Sometimes I put an empty string. Other times a name in quotes. I can't leave it empty, although I do not know what it does.

Help?


Hi Joseph,

Thank you for your detailed explanation.  I have some ideas about what could be causing the issue; but it would help to see the settings of the advanced SQL and parameters.

The error message likely comes from a C# Parse() function call eg Int32.Parse() or Decimal.Parse().

Is the query returning a string column or a value where the Advanced query output parameter is a structure with a number attribute?  If this is the case, try changing the output structure attribute to a text type.

Otherwise, is the input parameter set to expand inline? If it is, when it has no value, it may result in invalid SQL.  Although, I expect you would see a different error message.

A few things that might help are taking screenshots of all the relevant panels, settings and SQL.  If possible creating an example module which exhibits the problem and uploading it.

I hope this helps!

Kind regards,

Stuart

Turns out this is an easily reproducible bug in the JSON to Structure wizard, which I have reported to Outsystems, along with sample JSON that produces the problem. If certain attributes contain only NULLs (or only NULLS as far as Outsystems samples the JSON - not sure how deep they go), the wizard will silently ignore and not create that attribute in the Structure. Not sure why the resulting error on running the stored procedure returns a false error about the input value format.


Thanks,

JM

Hi Joseph,

Ah well done.  You have probably noticed this already but just in case.. The JSON serialization widget will exclude default values by default.  By setting Serialize Default Values = Yes the JSON will include all attributes from the structure.

Hmm. No, I've never seen anywhere that I could customize that functionality. Where would I find that?

In actions where you serialize structures and entities to JSON, use use the JSON Serialize widget like this.

(UPDATE: Updating the image to give more context.)


Otherwise, there is also a similar attribute to serialize defaults on exposed REST endpoints.


Ah. I am using the built-in widget to create a structure to use as the output structure for an external stored procedure. Not sure there is anything to configure there, since it is just a right-click option on the Structures folder. Internally, at Outsystems, that might be the setting they need to tweak to fix this.

Another method would be to check you JSON before you submit it and make sure that every field has representative data - no NULLs.