Hey!
I was trying to store JSON strings using text data type. It will be helpful if I could know what is the maximum size of text data type in outsystems?
Hi Shashankit Thakur,
After doing some search for this topic I found this community discussion and also please read out the João Batista reply. Hope this discussion will be a help for your query.
Kind Regards,
Benjith Sam
Shashankit Thakur wrote:
It depends on the database which you are using. for example if you are using SQL server 12 the max length is 4000 Character.
Raj wrote:
Thanks!
Hi,
For strings bigger than 2000 outsystems uses nvarchar(max).
The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage.
NVARCHAR(MAX)
Since NVARCHAR uses 2 bytes per character, that's approx. 1 billion characters
NVARCHAR
Regards,
Marcelo
Neither this thread, nor the one linked above, answers the question!
Outsystems may USE varchar(max), but if you set it to 4000, would that still limit it to 4000 characters?
Hi Nathan,
Yes Outsystems will limit it.
So if you want the maximum space, what length value do you use... the max value for an int is 2,147,483,647, so can we just whack in 999999999 (999 million etc)
Never tried it before but in theory it should work. If you end up doing the experiment share with us the result.
In case anyone's interested, we gave it a spin so we could store the diff snapshot of a large form. It took 15 minutes to publish and there's no abort button so we're currently considering a different solution...
We do storage of large JSON, or XML strings by defining the attribute data type as Binary. Then using TextToBinary from the Binary espace to convert the text prior to storing it, and BinaryToText to convert it back to a text after reading it. Now you do not have to worry about size property of the attribute. You could decide to redesign your entity and create a 1 on 1 entity just to store the binary separate from the other attributes of your entity, with the primairy key set to the identifier of your original table.
Ideally OutSystems should support the JSON data type natively for an entity attribute. There is already an idea for that that you can vote for.
https://www.outsystems.com/ideas/5310/JSON+datatype+in+Outsystems?IsFromAdvancedSearch=True
Daniel
Hi @Daniel Kuhlmann ,
We are saving the JSON(the view JSON obtained from DataGrid) as binary in Database. But for large jsons(mostly when we have fewer filters applied in columns) we get connection timeout during the conversion from text to binary. Is there any way out for this?
Thank You,
Radhika
Hi All,
In my case as Ferreira said, I have increased text length to 2500 and OutSystems / SQL Server automatically created nvarchar(max) in database.
Sravan
For a Text input parameter in a Process I'm having the error "String or binary data would be truncated. " at the moment of launching the process.
This is the fragment of the stacktrace which reflects where is the problem launching the process:
String or binary data would be truncated.The statement has been terminated. at OutSystems.HubEdition.RuntimePlatform.Processes.ProcessBase.ThrowSpecificException(String failureMessage) at OutSystems.HubEdition.RuntimePlatform.GenericExtendedActions.ProcessLaunch(HeContext heContext, ObjectKey SSKey, ObjectKey espaceSSKey, Int32 parentActivityId, Int32 parentProcessId, List`1 inputs, List`1& nextActIds)
There is an additional constraint for the Text data type length when used as input parameter for a Process? the parameter in question had 3486 characters length in this issue.
Thanks in advance
Hi Roberto,
Yes there is a constraint . the limit is 2000. since processes are asynchronous that value is saved in the database and the field is a nvarchar(2000).
Thank you Marcelo for your quick response, I've already solved the problem by creating a record in DB and passing the identifier to the process.
Regars,
Roberto
Please refer below best practice url,
https://success.outsystems.com/Documentation/Best_Practices/Performance_and_Monitoring/Performance_Top_10_Rules
Avoid the use of 2000+ characters in a text field; data fields greater than 2000 bytes are converted into a Text data type which in turn are much slower to process and decrease performance overall. As a rule, isolate binary and/or large text fields in separate entities and only retrieve them when they are strictly necessary.
I would also look into the possibility to store these large texts or binaries outside the database. Although storing these is something a database can do you should really ask yourself the question if that is the best place to store it. Storing it in the database will cost performance, is not cost efficient and will not support a lazy-load experience. Perhaps storing it in a Azure Storage Account or an AWS S3 is a much better solution since that is a lot cheaper and more suitable for storing large amount of data.